oracle dba 应该熟悉的命令（5）

91 删除日志组成员
ALTER DATABASE DROP LOGFILE MEMBER 'D:ORADATAMYDBredo14.log' ;

92 显示系统表空间
select * from dba_tablespaces;
93 显示系统表空间所拥有的文件
select * from dba_data_files;
94 显示系统临时表空间
select * from dba_temp_files;
95 显示表空间的已被使用多少
select tablespace_name,sum(bytes)/1024/1024 m
from dba_data_files group by tablespace_name;
96 显示表空间还有多少没有使用
select tablespace_name,
sum(bytes)/1024/1024 m
from dba_free_space group by tablespace_name;
97 创建表空间
10G CREATE BIGFILE TABLESPACE "MYTBS1" DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
9I CREATE TABLESPACE "MYTBS1" DATAFILE 'D:oracleORADATAthwerpmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "MYTBS1" DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M , 'D:ORADATAMYDBmytbs02.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
98 在指字表空间里建表
create table t1 (a int) tablespace mytbs1;
99 在指字表空间里建索引
create index t1_ind on t1(a) tablespace mytbs1;
100 在指定表空间里增加数据文件
ALTER TABLESPACE "MYTBS1" ADD DATAFILE 'D:ORADATAMYDBmytbs03.dbf' SIZE 100M
100 修改表空间的大小
ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS_01.DBF' RESIZE 200M
101 表空间改文件的自动扩展
ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
102 删除表空间和表空间所属文件
drop tablespace mytbs1 including contents and datafiles;
103 建立临时表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
CREATE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
104 更改用户的临时表空间
ALTER USER "GARY" TEMPORARY TABLESPACE "MYTEMP"
105 设置成默认的表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "MYTEMP"
106 创建临时表空间组
ALTER TABLESPACE MYTEMP TABLESPACE GROUP MYTEMP_GROUP
ALTER TABLESPACE TEMP TABLESPACE GROUP MYTEMP_GROUP
107 创建重做表空间
CREATE UNDO TABLESPACE "MYUNDO" DATAFILE 'D:ORADATAMYDBmyundo01.dbf' SIZE 200M
108 显示重做参数
show parameter undo
109 回滚段的使用情况
select * from dba_rollback_segs;
110 更改重做表空间
alter system set undo_tablespace=myundo;
111 建立一个表
create table a tablespace users as select * from dba_objects;
112 插入一些记录
insert into a select * from a;
113 有条件查询表名放在哪个表空间
select * from dba_tables where table_name='A' and owner='SYS';
114 有条件查询段名放在哪个表空间
select * from dba_segments where segment_name='A' and owner='SYS';
115 查询有哪些段类型
select distinct segment_type from dba_segments;
116 查询段和表空间的对应关系
select * from dba_extents where segment_name='A' and owner='SYS';
117 建立一个16K表空间
CREATE TABLESPACE "MYTBS3" DATAFILE 'D:oracleORADATAthwerpmytbs3.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384;
118 指字参数建立表
create table b (a int,b varchar2(10)) tablespace users INITRANS 2 PCTFREE 5;
119 显示当前所使用的块大小
show parameter block_size;
120 释放表的高水位的空间
alter table a deallocate unused;