本文深入探讨了 Oracle 数据库管理系统中表空间与数据文件的管理细节，包括如何显示表空间详细信息、编号与名称、包含的数据文件、动态信息以及临时表空间与文件的管理。
1.显示表空间详细信息
（DBA_TABLESPACES,USER_TABLESPACES,DBA_TABLESPACES）
exp：
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- -----------
-------
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
UNDOTBS1 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS2 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
DATA01 8192 131072 131072 1 2147483645 0 131072 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
BIG_TBS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY YES
TEMP02 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
OMF 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
11 rows selected
2.显示表空间的编号和名称
（V$TABLESPACE）
exp:
SQL> select * from v$tablespace;
TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON
---------- ------------------------------ --------------------------- ------- ------------
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP YES NO YES
6 EXAMPLE YES NO YES
7 DATA01 YES NO YES
8 BIG_TBS YES YES YES
5 UNDOTBS2 YES NO YES
9 TEMP02 YES NO YES
10 OMF YES NO YES
11 rows selected
3.显示表空间所包含的数据文件
（dba_data_files)
exp:
SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF 4 USERS 5242880 640 AVAILABLE 4 YES 3435972198 4194302 160 5177344 632
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF 3 SYSAUX 335544320 40960 AVAILABLE 3 YES 3435972198 4194302 1280 335478784 40952
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF 2 UNDOTBS1 31457280 3840 AVAILABLE 2 YES 3435972198 4194302 640 31391744 3832
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM 471859200 57600 AVAILABLE 1 YES 3435972198 4194302 1280 471793664 57592
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE 157286400 19200 AVAILABLE 5 YES 3435972198 4194302 80 157220864 19192
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\DATA01.DBF 6 DATA01 10485760 1280 AVAILABLE 6 NO 0 0 0 10354688 1264
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\BIG.DBF 7 BIG_TBS 1073741824 131072 AVAILABLE 1024 NO 0 0 0 1073610752 131056
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS2.DBF 8 UNDOTBS2 10485760 1280 AVAILABLE 8 NO 0 0 0 10420224 1272
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\ORCL\DATAFILE\O1_MF_OMF_98R474DP_.DBF 9 OMF 2097152 256 AVAILABLE 9 NO 0 0 0 2031616 248
9 rows selected
4.显示数据文件动态信息
（V$DATAFILE）
当打开数据库时，Oracle会按照控制文件所记载的数据文件位置及名称打开所有数据文件。当发出检查点时，CKPT进程会将检查点
时间、SCN写入到数据文件中。
exp:
SQL> select *
2 from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES
BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------
---------- -------------------------------------------------------------------------------- ---------- ------------- --------------------------------------------------------------------------------
1 10 2004/3/9 23:5 0 1 SYSTEM READ WRITE 2171317 2013/11/20 10:1 0 318841 318842 2013/10/31 471859200 57600
0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE
2 317561 2004/3/10 1:0 1 2 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 318841 318842 2013/10/31 31457280 3840
0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE
3 5833 2004/3/9 23:5 2 3 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 318841 318842 2013/10/31 335544320 40960
0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE
4 8840 2004/3/9 23:5 4 4 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 318841 318842 2013/10/31 5242880 640
0 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF 0 8192 NONE
5 341980 2013/10/31 14 6 5 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 0 0 157286400 19200 157286400
8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF 0 8192 NONE
6 2079035 2013/11/19 11 7 6 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 0 0 10485760 1280 10485760
8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\DATA01.DBF 0 8192 NONE
7 2079578 2013/11/19 11 8 1024 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 0 0 1073741824 131072 1073741824
8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\BIG.DBF 0 8192 NONE
8 2079647 2013/11/19 11 5 8 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 0 0 10485760 1280 10485760
8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS2.DBF 0 8192 NONE
9 2168605 2013/11/20 9: 10 9 ONLINE READ WRITE 2171317 2013/11/20 10:1 0 0 0 2097152 256 2097152
8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\ORCL\DATAFILE\O1_MF_OMF_98R474DP_.DBF 0 8192 NONE
9 rows selected
5.显示临时表空间所包含的临时文件
（dba_temp_files)
exp:
SQL> select *
2 from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF 1 TEMP 20971520 2560 AVAILABLE 1 YES 3435972198 4194302 80 19922944 2432
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP02.DBF 2 TEMP02 5242880 640 AVAILABLE 1 NO 0 0 0 4194304 512
6.显示临时文件动态信息
（v$tempfile)
exp:
SQL> select *
2 from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
1 0 3 1 ONLINE READ WRITE 20971520 2560 20971520 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF
2 0 9 1 ONLINE READ WRITE 5242880 640 5242880 8192 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP02.DBF
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
