逻辑体系结构与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 1000
select /*+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 1000
select /*+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 1000
select /*+index(EMPLOYEES,idx_emp_id)*/ * from EMPLOYEES where employee_id>0
---启动大小为32K的块新建表空间(WINDOWS下只能使用2K,4K，8K和16K)
--行链接只有通过加大BLOCK块的方式才可以避免，如下：
create tablespace TBS_LJB_16k
blocksize 16K
datafile 'D:ORACLEORADATATEST11GTBS_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 a15
col segment_type format a10
col tablespace_name format a20
col blocks format 9999
col 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 .0625
SQL>---建一个索引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 .0625
SQL> ---插入数据后继续观察
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 9
SQL> ---随着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_LJB
datafile 'D:ORACLEORADATATEST11GTBS_LJB01.DBF' size 100M
extent management local
segment space management auto;
col file_name format a50
col BYTES format 999999999999
set linesize 366
SELECT 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:ORACLEORADATATEST11GTEMP_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:ORACLEORADATATEST11GUNDOTBS02.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 0
SYSAUX .71 .04 .67 5.63
UNDOTBS1 4.99 .56 4.43 11.22
USERS .36 .23 .13 63.88
TBS_LJB 13 12.67 .33 97.46
TBS_LJB_2K .09 .09 0 100
TBS_LJB_16K .09 .09 0 100
SQL>---创建用户表空间
SQL>create tablespace TBS_LJB datafile 'D:ORACLEORADATATEST11GTBS_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:ORACLEORADATATEST11GTBS_LJB01.DBF TBS_LJB NO 1073741824
SQL> ---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）
SQL> CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:ORACLEORADATATEST11GTEMP_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:ORACLEORADATATEST11GTEMP_LJB.DBF TEMP_LJB NO 104857600
SQL> ---创建回滚表空间
SQL> create undo tablespace undotbs2 datafile 'D:ORACLEORADATATEST11GUNDOTBS02.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:ORACLEORADATATEST11GUNDOTBS02.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 33583
set autotrace off
delete 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 statistics
select count(*) from t;
select count(*) from t;
set autotrace off
delete from t where rownum<=2000000;
commit;
select bytes/1024/1024 from user_segments where segment_name='T';
set autotrace on statistics
select 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 statistics
select 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
---------------
264
SQL> 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
---------------
264
SQL> 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_LJB
datafile 'D:ORACLEORADATATEST11GTBS_LJB01.DBF' size 100M
extent management local
segment space management auto;
col file_name format a50
col BYTES format 999999999999
set linesize 366
SELECT 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:ORACLEORADATATEST11GTEMP_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:ORACLEORADATATEST11GUNDOTBS02.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 0
SYSAUX .71 .04 .67 5.63
UNDOTBS1 4.99 .56 4.43 11.22
USERS .36 .23 .13 63.88
TBS_LJB 13 12.67 .33 97.46
TBS_LJB_2K .09 .09 0 100
TBS_LJB_16K .09 .09 0 100
SQL>---创建用户表空间
SQL>create tablespace TBS_LJB datafile 'D:ORACLEORADATATEST11GTBS_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:ORACLEORADATATEST11GTBS_LJB01.DBF TBS_LJB NO 1073741824
SQL> ---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）
SQL> CREATE TEMPORARY TABLESPACE temp_ljb TEMPFILE 'D:ORACLEORADATATEST11GTEMP_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:ORACLEORADATATEST11GTEMP_LJB.DBF TEMP_LJB NO 104857600
SQL> ---创建回滚表空间
SQL> create undo tablespace undotbs2 datafile 'D:ORACLEORADATATEST11GUNDOTBS02.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:ORACLEORADATATEST11GUNDOTBS02.DBF UNDOTBS2 NO 104857600
表空间之临时表空间组调优：
---临时表空间（语法有些特别，有TEMPORARY及TEMPFILE的关键字）
drop tempoary tablespae temp_ljb including contents and datafiles;
CREATE TEMPORARY TABLESPACE temp_ljb
TEMPFILE 'D:ORACLEORADATATEST11GTEMP01.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 1000
column file_name format a60
select file_name,tablespace_name from dba_temp_files;
select * from dba_tablespace_groups;
create temporary tablespace temp1_1 tempfile 'D:ORACLEORADATATEST11GTMP1_1.DBF' size 100M tablespace group mytmp_grp1;
create temporary tablespace temp1_2 tempfile 'D:ORACLEORADATATEST11GTMP1_2.DBF' size 100M tablespace group mytmp_grp1;
create temporary tablespace temp1_3 tempfile 'D:ORACLEORADATATEST11GTMP1_3.DBF' size 100M tablespace group mytmp_grp1;
alter tablespace temp tablespace group mytmp_grp1;
create temporary tablespace temp2_1 tempfile 'D:ORACLEORADATATEST11GTMP2_1.DBF' size 100M tablespace group mytmp_grp2;
create temporary tablespace temp2_2 tempfile 'D:ORACLEORADATATEST11GTMP2_2.DBF' size 100M tablespace group mytmp_grp2;
create temporary tablespace temp2_3 tempfile 'D:ORACLEORADATATEST11GTMP2_3.DBF' size 100M tablespace group mytmp_grp2;
alter user LJB temporary tablespace mytmp_grp1;
alter user YXL temporary tablespace mytmp_grp2;
set linesize 1000
column file_name format a60
select 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 1000
column file_name format a60
select 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 TEMP
LJB 35 TEMP
LJB 38 TEMP
LJB 40 TEMP
LJB 44 TEMP
LJB 57 TEMP
LJB 66 TEMP
--建临时表空间组后，各个用到临时表空间的SESSION的临时表空间的使用情况：
SQL> SELECT USERNAME,SESSION_NUM,TABLESPACE FROM V$SORT_USAGE;
USERNAME SESSION_NUM TABLESPACE
------------------------------ ----------- -------------------------------
LJB 28 TEMP
LJB 35 TEMP1_1
LJB 38 TEMP1_2
LJB 40 TEMP1_3
LJB 44 TEMP2_1
LJB 57 TEMP2_2
LJB 66 TEMP2_3