Oralce学习笔记(六)

Oralce学习笔记(六)

逻辑体系结构与Data file

逻辑体系结构关系图

逻辑体系结构最小单位BLOCK的研究

block能装多少行数据：

drop table test_block_num purge;create table test_block_num (id varchar2(1));begin for i in 1..8000 loop insert into test_block_num values('a'); end loop; commit;end;/--测试发现，不行。select f, b, count(*) from (select dbms_rowid.rowid_relative_fno(rowid) f, dbms_rowid.rowid_block_number(rowid) b from test_block_num) group by f, b;根本原因：每行的其他开销导致每行的最小长度在11个字节左右，所以一个8K的块的行理论上最多可用存储8096/11=736行-------------------------------------------------------------------------------------------------------------SQL> select f, b, count(*) 2 from (select dbms_rowid.rowid_relative_fno(rowid) f, 3 dbms_rowid.rowid_block_number(rowid) b 4 from test_block_num) 5 group by f, b; F B COUNT(*)---------- ---------- ---------- 11 197 660 11 198 660 6 1957 660 6 1955 660 6 1956 660 11 194 660 11 196 660 11 193 660 11 192 660 11 195 80 6 1958 660 11 199 660 6 1959 660

行迁移的成因和优化：

成因：当行被Update时，如果Update更新的行大于数据块得PCTFREE值，就需要申请第2个块，从而形成行迁移。后果：导致应用需要访问更多的数据块，性能下降。预防：1. 将数据块的PCTFREE调大；2. 针对表空间扩大数据块大小检查：analyze table 表名 validate structure cascade into chained_rows;

操作:（以EMPLOYEES表为例，如果涉及到该表有主键，并且有别的表的外键REFERENCE关联到本表，必须要执行步骤2和步骤7，否则不必执行）：1． 执行脚本创建chained_rows表。2. 禁用所有其它表上关联到此表上的所有限制(假想EMPLOYEES表有主键PK_EMPLOYEES_ID，假想test表有外键f_employees_id关联reference到employees表）。 select index_name,index_type,table_name from user_indexes where table_name='EMPLOYEES'; select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME='PK_EMPLOYEES_ID'; alter table test disable constraint f_employees_id;3． 将存在有行迁移的表（用table_name代替）中的产生行迁移的行的rowid放入到chained_rows表中。 4． 将表中的行迁移的row id放入临时表中保存。5． 删除原来表中存在的行迁移的记录行。6． 从临时表中取出并重新插入那些被删除了的数据到原来的表中，并删除临时表。7． 启用所有其它表上关联到此表上的所有限制。 alter table test enable constraint f_employees_id;此外还可以采用move和exp/imp的方式（特别注意move会导致索引失效，需要重建索引）。

--- PCTFREE试验准备之建表DROP TABLE EMPLOYEES PURGE;CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;desc EMPLOYEES;create index idx_emp_id on employees(employee_id);--- PCTFREE试验准备之扩大字段alter table EMPLOYEES modify FIRST_NAME VARCHAR2(1000);alter table EMPLOYEES modify LAST_NAME VARCHAR2(1000);alter table EMPLOYEES modify EMAIL VARCHAR2(1000);alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(1000);--- PCTFREE试验准备之更新表UPDATE EMPLOYEES SET FIRST_NAME = LPAD('1', 1000, '*'), LAST_NAME = LPAD('1', 1000, '*'), EMAIL = LPAD('1', 1000, '*'), PHONE_NUMBER = LPAD('1', 1000, '*');COMMIT;---行迁移优化前，先看看该语句逻辑读情况(执行计划及代价都一样）SET AUTOTRACE traceonly set linesize 1000select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0;/set autotrace off ----- 发现存在行迁移的方法--首先建chaind_rows相关表，这是必需的步骤drop table chained_rows purge;@?/rdbms/admin/utlchain.sql----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中analyze table EMPLOYEES list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES';---以下方法可以去除行迁移drop table EMPLOYEES_TMP;create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);Insert into EMPLOYEES select * from EMPLOYEES_TMP;delete from chained_rows ;commit;analyze table EMPLOYEES list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES';--这时的取值一定为0。

行连接：

产生原因：当一行数据大于一个数据块，ORACLE会同时分配两个数据块，并在第一个块上登记第二个块的地址，从而形成行链接。

预防方法：针对表空间扩大数据块大小。

检查行迁移的语句：

analyze table 表名 validate structure cascade into chained_rows;

测试语句：

--- PCTFREE试验准备之建表DROP TABLE EMPLOYEES PURGE;CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;desc EMPLOYEES;create index idx_emp_id on employees(employee_id);--- PCTFREE试验准备之扩大字段alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000);alter table EMPLOYEES modify EMAIL VARCHAR2(2000);alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000);--- PCTFREE试验准备之更新表UPDATE EMPLOYEES SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'), PHONE_NUMBER = LPAD('1', 2000, '*');COMMIT;---行链接移优化前，先看看该语句逻辑读情况SET AUTOTRACE traceonly set linesize 1000select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0/set autotrace off ----- 发现存在行链接的方法--首先建chaind_rows相关表，这是必需的步骤drop table chained_rows purge;----以下命令针对EMPLOYEES表和EMPLOYEES_BK做分析，将产生行迁移的记录插入到chained_rows表中analyze table EMPLOYEES list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES';---用消除行迁移的方法根本无法消除行链接!!!drop table EMPLOYEES_TMP;create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);Insert into EMPLOYEES select * from EMPLOYEES_TMP;delete from chained_rows ;commit;--发现用消除行迁移的方法根本无法消除行链接!analyze table EMPLOYEES list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES';SET AUTOTRACE traceonly set linesize 1000select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0---启动大小为32K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)--行链接只有通过加大BLOCK块的方式才可以避免，如下：create tablespace TBS_LJB_16k blocksize 16Kdatafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_32K_01.DBF' size 100M autoextend on extent management local segment space management auto;DROP TABLE EMPLOYEES_BK PURGE;CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_LJB_16K AS SELECT * FROM EMPLOYEES;delete from chained_rows ;commit;analyze table EMPLOYEES_BK list chained rows into chained_rows;select count(*) from chained_rows where table_name='EMPLOYEES_BK';SQL> --发现用消除行迁移的方法根本无法消除行链接!SQL> analyze table EMPLOYEES list chained rows into chained_rows;表已分析。SQL> select count(*) from chained_rows where table_name='EMPLOYEES'; COUNT(*)---------- 107 SQL> --行链接只有通过加大BLOCK块的方式才可以避免，如下： SQL> DROP TABLE EMPLOYEES_BK PURGE;表已删除。SQL> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_LJB_16K AS SELECT * FROM EMPLOYEES;表已创建。SQL> delete from chained_rows ;已删除107行。SQL> commit;提交完成。SQL> analyze table EMPLOYEES_BK list chained rows into chained_rows;表已分析。SQL> select count(*) from chained_rows where table_name='EMPLOYEES_BK'; COUNT(*)

Oralce中segment：

---构造t表drop table t purge;create table t tablespace tbs_ljb as select * from dba_objects where rownum=1 ;col segment_name format a15col segment_type format a10col tablespace_name format a20col blocks format 9999col extents format 9999---查询数据字典获取segment相关信息---建一个T表就产生了表段，T段(SEGMENT)，请观察区(EXTENT)及BLOCKS的个数。如下：select segment_name, segment_type, tablespace_name, blocks,extents, bytes/1024/1024 from user_segments where segment_name = 'T';select count(*) from user_extents WHERE segment_name='T';---建一个索引IDX_OBJ_ID就产生了索引段，IDX_OBJ_ID段(SEGMENT),和表的情况类似，如下：create index idx_obj_id on t(object_id);select segment_name, segment_type, tablespace_name, blocks, extents, bytes/1024/1024 from user_segments where segment_name = 'IDX_OBJ_ID';select count(*) from user_extents WHERE segment_name='IDX_OBJ_ID';---插入数据后继续观察insert into t select * from dba_objects ;commit;---随着T表数据不断增加，区(EXTENT)也不断增多。如下：select segment_name, segment_type, tablespace_name, blocks, extents,bytes/1024/1024 from user_segments where segment_name = 'T';select count(*) from user_extents WHERE segment_name='T';---随着IDX_OBJ_ID不断增大，区(EXTENT)也不断增多。如下：select segment_name, segment_type, tablespace_name, blocks, extents, bytes/1024/1024 from user_segments where segment_name = 'IDX_OBJ_ID';select count(*) from user_extents WHERE segment_name='IDX_OBJ_ID';----------------------------------------------------------------------------------------------------------------------------------------------SQL> ---建一个T表就产生了表段，T段(SEGMENT)，观察区(EXTENT)及BLOCKS的个数。如下：SQL> select segment_name, 2 segment_type, 3 tablespace_name, 4 blocks,extents, 5 bytes/1024/1024 6 from user_segments where segment_name = 'T';SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME BLOCKS EXTENTS BYTES/1024/1024--------------- ---------- -------------------- ------ ------- ---------------T TABLE TBS_LJB 8 1 .0625SQL>---建一个索引IDX_OBJ_ID就产生了索引段，和表情况类似，如下：SQL> create index idx_obj_id on t(object_id);索引已创建。SQL> select segment_name, 2 segment_type, 3 tablespace_name, 4 blocks, 5 extents, 6 bytes/1024/1024 7 from user_segments 8 where segment_name = 'IDX_OBJ_ID';SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME BLOCKS EXTENTS BYTES/1024/1024--------------- ---------- -------------------- ------ ------- ---------------IDX_OBJ_ID INDEX TBS_LJB 8 1 .0625SQL> ---插入数据后继续观察SQL> insert into t select * from dba_objects ;已创建72882行。SQL> commit;提交完成。SQL> ---随着T表数据不断增加，区(EXTENT)及BLOCKS的个数也不断增多。如下：SQL> select segment_name, 2 segment_type, 3 tablespace_name, 4 blocks, 5 extents,bytes/1024/1024 6 from user_segments 7 where segment_name = 'T';SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME BLOCKS EXTENTS BYTES/1024/1024--------------- ---------- -------------------- ------ ------- ---------------T TABLE TBS_LJB 1152 24 9SQL> ---随着IDX_OBJ_ID不断增大，区(EXTENT)及BLOCKS的个数也不断增多。如下：SQL> select segment_name, 2 segment_type, 3 tablespace_name, 4 blocks, 5 extents, 6 bytes/1024/1024 7 from user_segments 8 where segment_name = 'IDX_OBJ_ID';SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME BLOCKS EXTENTS BYTES/1024/1024--------------- ---------- -------------------- ------ ------- ---------------IDX_OBJ_ID INDEX TBS_LJB 384 18 3

--- 查看Oracle 数据、临时、回滚、系统表空间情况--查看表空间的总体情况 SELECT A.TABLESPACE_NAME "表空间名", A.TOTAL_SPACE "总空间(G)", NVL(B.FREE_SPACE, 0) "剩余空间(G)", A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)", CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" --避免分母为0 FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) ORDER BY 5; ---创建用户表空间create tablespace TBS_LJBdatafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB01.DBF' size 100Mextent management localsegment space management auto;col file_name format a50col BYTES format 999999999999 set linesize 366SELECT file_name, tablespace_name, autoextensible,bytes FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TBS_LJB' order by substr(file_name, -12); ---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:\ORACLE\ORADATA\TEST11G\TEMP_LJB.DBF' SIZE 100M;SELECT FILE_NAME,BYTES,AUTOEXTENSIBLE FROM DBA_TEMP_FILES where tablespace_name='TEMP_LJB';---回滚段表空间（语法有些特别，有UNDO的关键字）create undo tablespace undotbs2 datafile 'D:\ORACLE\ORADATA\TEST11G\UNDOTBS02.DBF' size 100M;SELECT file_name, tablespace_name, autoextensible,bytes/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS2' order by substr(file_name, -12); ---系统表空间（Oracle 10g的系统表空间还增加了SYSAUX作为辅助系统表空间使用）SELECT file_name, tablespace_name,autoextensible,bytes/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'SYS%' order by substr(file_name, -12);---系统表空间和用户表空间都属于永久保留内容的表空间select tablespace_name,contents from dba_tablespaces where tablespace_name in('TBS_LJB', 'TEMP_LJB', 'UNDOTBS2', 'SYSTEM', 'SYSAUX'); -------------------------------------------------------------------------------------------------------------------------------------------SQL> SELECT A.TABLESPACE_NAME "表空间名", 2 A.TOTAL_SPACE "总空间(G)", 3 NVL(B.FREE_SPACE, 0) "剩余空间(G)", 4 A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)", 5 CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" 6 FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE 7 FROM DBA_DATA_FILES 8 GROUP BY TABLESPACE_NAME) A, 9 (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE 10 FROM DBA_FREE_SPACE 11 GROUP BY TABLESPACE_NAME) B 12 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) 13 ORDER BY 5;表空间名 总空间(G) 剩余空间(G) 使用空间(G) 剩余百分比%------------------------------ ---------- ----------- ----------- -----------SYSTEM .73 0 .73 0SYSAUX .71 .04 .67 5.63UNDOTBS1 4.99 .56 4.43 11.22USERS .36 .23 .13 63.88TBS_LJB 13 12.67 .33 97.46TBS_LJB_2K .09 .09 0 100TBS_LJB_16K .09 .09 0 100SQL>---创建用户表空间SQL>create tablespace TBS_LJB datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB01.DBF' size 1G extent management local; 表空间已创建。SQL> SELECT file_name, tablespace_name, autoextensible,bytes FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TBS_LJB';FILE_NAME TABLESPACE_NAME AUT BYTES-------------------------------------------------- ------------------------------ --- -------------D:\ORACLE\ORADATA\TEST11G\TBS_LJB01.DBF TBS_LJB NO 1073741824SQL> ---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）SQL> CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:\ORACLE\ORADATA\TEST11G\TEMP_LJB.DBF' SIZE 100M;表空间已创建。SQL> SELECT FILE_NAME,tablespace_name,AUTOEXTENSIBLE,BYTES FROM DBA_TEMP_FILES where tablespace_name='TEMP_LJB';FILE_NAME TABLESPACE_NAME AUT BYTES-------------------------------------------------- -------------------------------D:\ORACLE\ORADATA\TEST11G\TEMP_LJB.DBF TEMP_LJB NO 104857600SQL> ---创建回滚表空间SQL> create undo tablespace undotbs2 datafile 'D:\ORACLE\ORADATA\TEST11G\UNDOTBS02.DBF' size 100M;表空间已创建。SQL> SELECT file_name, tablespace_name, autoextensible,bytes FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS2';FILE_NAME TABLESPACE_NAME AUT BYTES -------------------------------------------------- -----------------------------------------D:\ORACLE\ORADATA\TEST11G\UNDOTBS02.DBF UNDOTBS2 NO 104857600

--构造表drop table t purge;create table t as select * from dba_objects;insert into t select * from t;insert into t select * from t;insert into t select * from t;insert into t select * from t;insert into t select * from t;commit;exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; select num_rows,blocks from user_tab_statistics where table_name='T'; NUM_ROWS BLOCKS--------- ---------- 2320250 33583set autotrace offdelete from t where rownum<=2300000;commit;exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; select num_rows,blocks from user_tab_statistics where table_name='T'; NUM_ROWS BLOCKS---------- ---------- 32480 33583

--构造表drop table t purge;create table t as select * from dba_objects;insert into t select * from t;insert into t select * from t;insert into t select * from t;insert into t select * from t;insert into t select * from t;commit;--测试表的大小及语句的效率select bytes/1024/1024 from user_segments where segment_name='T';set autotrace on statisticsselect count(*) from t;select count(*) from t;set autotrace offdelete from t where rownum<=2000000;commit;select bytes/1024/1024 from user_segments where segment_name='T';set autotrace on statisticsselect count(*) from t;select count(*) from t;--用move重组数据后，高水平位释放（注意move操作会导致索引失效）alter table t move;select bytes/1024/1024 from user_segments where segment_name='T';set autotrace on statisticsselect count(*) from t;select count(*) from t;---延伸扩展，如何定位出存在高水平位的表exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; select * from user_tab---------------------------------------------------------------------------------------------------------------------SQL> --测试表的大小及语句的效率SQL> select bytes/1024/1024 from user_segments where segment_name='T';BYTES/1024/1024--------------- 264SQL> select count(*) from t; COUNT(*)---------- 2332096统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 33350 consistent gets 0 physical reads---删除大量数据，再做试验如下,发现SEGMENT未见减少，依然是：SQL> delete from t where rownum<=2000000;已删除2000000行。SQL> commit;提交完成。SQL> select bytes/1024/1024 from user_segments where segment_name='T';BYTES/1024/1024--------------- 264SQL> select count(*) from t; COUNT(*)---------- 332096统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 33350 consistent gets 0 physical reads SQL> --用move重组数据后，高水平位释放（注意move操作会导致索引失效）SQL> alter table t move;表已更改。SQL> select bytes/1024/1024 from user_segments where segment_name='T';BYTES/1024/1024--------------- 38 SQL> select count(*) from t; COUNT(*)---------- 332096统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 4742 consistent gets 0 physical reads

查看Oralce数据、临时、回滚系统表空间情况。

--- 查看Oracle 数据、临时、回滚、系统表空间情况sqlplus "/ as sysdba"--查看表空间的总体情况 SELECT A.TABLESPACE_NAME "表空间名", A.TOTAL_SPACE "总空间(G)", NVL(B.FREE_SPACE, 0) "剩余空间(G)", A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)", CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" --避免分母为0 FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) ORDER BY 5; ---创建用户表空间create tablespace TBS_LJBdatafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB01.DBF' size 100Mextent management localsegment space management auto;col file_name format a50col BYTES format 999999999999 set linesize 366SELECT file_name, tablespace_name, autoextensible,bytes FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TBS_LJB' order by substr(file_name, -12); ---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:\ORACLE\ORADATA\TEST11G\TEMP_LJB.DBF' SIZE 100M;SELECT FILE_NAME,BYTES,AUTOEXTENSIBLE FROM DBA_TEMP_FILES where tablespace_name='TEMP_LJB';---回滚段表空间（语法有些特别，有UNDO的关键字）create undo tablespace undotbs2 datafile 'D:\ORACLE\ORADATA\TEST11G\UNDOTBS02.DBF' size 100M;SELECT file_name, tablespace_name, autoextensible,bytes/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS2' order by substr(file_name, -12); ---系统表空间（Oracle 10g的系统表空间还增加了SYSAUX作为辅助系统表空间使用）SELECT file_name, tablespace_name,autoextensible,bytes/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'SYS%' order by substr(file_name, -12);---系统表空间和用户表空间都属于永久保留内容的表空间select tablespace_name,contents from dba_tablespaces where tablespace_name in('TBS_LJB', 'TEMP_LJB', 'UNDOTBS2', 'SYSTEM', 'SYSAUX'); -------------------------------------------------------------------------------------------------------------------------------------------SQL> SELECT A.TABLESPACE_NAME "表空间名", 2 A.TOTAL_SPACE "总空间(G)", 3 NVL(B.FREE_SPACE, 0) "剩余空间(G)", 4 A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)", 5 CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) END "剩余百分比%" 6 FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE 7 FROM DBA_DATA_FILES 8 GROUP BY TABLESPACE_NAME) A, 9 (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE 10 FROM DBA_FREE_SPACE 11 GROUP BY TABLESPACE_NAME) B 12 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) 13 ORDER BY 5;表空间名 总空间(G) 剩余空间(G) 使用空间(G) 剩余百分比%------------------------------ ---------- ----------- ----------- -----------SYSTEM .73 0 .73 0SYSAUX .71 .04 .67 5.63UNDOTBS1 4.99 .56 4.43 11.22USERS .36 .23 .13 63.88TBS_LJB 13 12.67 .33 97.46TBS_LJB_2K .09 .09 0 100TBS_LJB_16K .09 .09 0 100SQL>---创建用户表空间SQL>create tablespace TBS_LJB datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB01.DBF' size 1G extent management local; 表空间已创建。SQL> SELECT file_name, tablespace_name, autoextensible,bytes FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TBS_LJB';FILE_NAME TABLESPACE_NAME AUT BYTES-------------------------------------------------- ------------------------------ --- -------------D:\ORACLE\ORADATA\TEST11G\TBS_LJB01.DBF TBS_LJB NO 1073741824SQL> ---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）SQL> CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:\ORACLE\ORADATA\TEST11G\TEMP_LJB.DBF' SIZE 100M;表空间已创建。SQL> SELECT FILE_NAME,tablespace_name,AUTOEXTENSIBLE,BYTES FROM DBA_TEMP_FILES where tablespace_name='TEMP_LJB';FILE_NAME TABLESPACE_NAME AUT BYTES-------------------------------------------------- -------------------------------D:\ORACLE\ORADATA\TEST11G\TEMP_LJB.DBF TEMP_LJB NO 104857600SQL> ---创建回滚表空间SQL> create undo tablespace undotbs2 datafile 'D:\ORACLE\ORADATA\TEST11G\UNDOTBS02.DBF' size 100M;表空间已创建。SQL> SELECT file_name, tablespace_name, autoextensible,bytes FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'UNDOTBS2';FILE_NAME TABLESPACE_NAME AUT BYTES -------------------------------------------------- -----------------------------------------D:\ORACLE\ORADATA\TEST11G\UNDOTBS02.DBF UNDOTBS2 NO 104857600

表空间之临时表空间组调优：

---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）drop tempoary tablespae temp_ljb including contents and datafiles;CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:\ORACLE\ORADATA\TEST11G\TEMP01.DBF' SIZE 100M;SELECT FILE_NAME,BYTES,AUTOEXTENSIBLE FROM DBA_TEMP_FILES where tablespace_name='TEMP_LJB';---查看临时表空间大小select tablespace_name, sum(bytes) / 1024 / 1024 from dba_temp_files group by tablespace_name;--临时表空间组set linesize 1000column file_name format a60select file_name,tablespace_name from dba_temp_files;select * from dba_tablespace_groups;create temporary tablespace temp1_1 tempfile 'D:\ORACLE\ORADATA\TEST11G\TMP1_1.DBF' size 100M tablespace group mytmp_grp1;create temporary tablespace temp1_2 tempfile 'D:\ORACLE\ORADATA\TEST11G\TMP1_2.DBF' size 100M tablespace group mytmp_grp1;create temporary tablespace temp1_3 tempfile 'D:\ORACLE\ORADATA\TEST11G\TMP1_3.DBF' size 100M tablespace group mytmp_grp1;alter tablespace temp tablespace group mytmp_grp1;create temporary tablespace temp2_1 tempfile 'D:\ORACLE\ORADATA\TEST11G\TMP2_1.DBF' size 100M tablespace group mytmp_grp2;create temporary tablespace temp2_2 tempfile 'D:\ORACLE\ORADATA\TEST11G\TMP2_2.DBF' size 100M tablespace group mytmp_grp2;create temporary tablespace temp2_3 tempfile 'D:\ORACLE\ORADATA\TEST11G\TMP2_3.DBF' size 100M tablespace group mytmp_grp2;alter user LJB temporary tablespace mytmp_grp1;alter user YXL temporary tablespace mytmp_grp2;set linesize 1000column file_name format a60select file_name,tablespace_name from dba_temp_files;select * from dba_tablespace_groups;drop tablespace temp1_1 including contents and datafiles;drop tablespace temp1_2 including contents and datafiles;drop tablespace temp1_3 including contents and datafiles;drop tablespace temp2_1 including contents and datafiles;drop tablespace temp2_2 including contents and datafiles;drop tablespace temp2_3 including contents and datafiles;set linesize 1000column file_name format a60select file_name,tablespace_name from dba_temp_files;select * from dba_tablespace_groups;增加临时表空间的好处在于：在多SESSION登录用到临时表空间时，ORACLE可为这些SESSION分配不同临时表空间，有利于分散负载，缓解IO竞争。--未建临时表空间组之前各个用到临时表空间的SESSION的临时表空间的使用情况： SQL> SELECT USERNAME,SESSION_NUM,TABLESPACE FROM V$SORT_USAGE;USERNAME SESSION_NUM TABLESPACE------------------------------ ----------- -------------------------------LJB 28 TEMPLJB 35 TEMPLJB 38 TEMPLJB 40 TEMPLJB 44 TEMPLJB 57 TEMPLJB 66 TEMP--建临时表空间组后，各个用到临时表空间的SESSION的临时表空间的使用情况：SQL> SELECT USERNAME,SESSION_NUM,TABLESPACE FROM V$SORT_USAGE;USERNAME SESSION_NUM TABLESPACE------------------------------ ----------- -------------------------------LJB 28 TEMPLJB 35 TEMP1_1LJB 38 TEMP1_2LJB 40 TEMP1_3LJB 44 TEMP2_1LJB 57 TEMP2_2LJB 66 TEMP2_3

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。