数据字典DBA_TABLESPACES存放表空间的信息，从该视图可以知道数据库中有哪些表空间。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> desc dba_tablespaces; Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NUMBER MAX_SIZE NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) FORCE_LOGGING VARCHAR2(3) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) DEF_TAB_COMPRESSION VARCHAR2(8) RETENTION VARCHAR2(11) BIGFILE VARCHAR2(3) PREDICATE_EVALUATION VARCHAR2(7) ENCRYPTED VARCHAR2(3) COMPRESS_FOR VARCHAR2(12) |
抽取几个常用的字段
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> set linesize 200 SQL> select tablespace_name ,max_size,status,logging,contents,force_logging,extent_management,segment_space_management from dba_tablespaces; TABLESPACE_NAME MAX_SIZE STATUS LOGGING CONTENTS FOR EXTENT_MAN SEGMEN ------------------------------ ---------- --------- --------- --------- --- ---------- ------ SYSTEM 2147483645 ONLINE LOGGING PERMANENT NO LOCAL MANUAL SYSAUX 2147483645 ONLINE LOGGING PERMANENT NO LOCAL AUTO UNDOTBS1 2147483645 ONLINE LOGGING UNDO NO LOCAL MANUAL TEMP 2147483645 ONLINE NOLOGGING TEMPORARY NO LOCAL MANUAL USERS 2147483645 ONLINE LOGGING PERMANENT NO LOCAL AUTO HBK_TEMP 2147483645 ONLINE NOLOGGING TEMPORARY NO LOCAL MANUAL HBK_DATA 2147483645 ONLINE LOGGING PERMANENT NO LOCAL AUTO CMS69_DATA 2147483645 ONLINE LOGGING PERMANENT NO LOCAL AUTO 8 rows selected. |
有表空间，我们可以进一步知道某一表空间的数据文件，数据字典DBA_DATA_FILES存放数据文件的信息，从该视图中，可以知道一个表空间由哪些数据文件组成。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SQL> desc dba_data_files; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL> col FILE_NAME format a50 SQL> select file_name,file_id,tablespace_name,status,bytes,online_status from dba_data_files; FILE_NAME FILE_ID TABLESPACE_NAME STATUS BYTES ONLINE_ -------------------------------------------------- ---------- ------------------------------ --------- ---------- ------- /u01/app/oraData/orcl/users01.dbf 4 USERS AVAILABLE 5242880 ONLINE /u01/app/oraData/orcl/undotbs01.dbf 3 UNDOTBS1 AVAILABLE 78643200 ONLINE /u01/app/oraData/orcl/sysaux01.dbf 2 SYSAUX AVAILABLE 555745280 ONLINE /u01/app/oraData/orcl/system01.dbf 1 SYSTEM AVAILABLE 713031680 SYSTEM /u01/app/oraData/orcl/hbk_data.dbf 5 HBK_DATA AVAILABLE 52428800 ONLINE /u01/app/oraData/orcl/CMS69_DATA.dbf 6 CMS69_DATA AVAILABLE 1310720000 ONLINE 6 rows selected. |
也可以使用如下查询，v$datafile视图的信息更加详情，desc v$datafile进行查看
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> col name format a50 SQL> select name , file#,ts#,status,bytes from v$datafile; NAME FILE# TS# STATUS BYTES -------------------------------------------------- ---------- ---------- ------- ---------- /u01/app/oraData/orcl/system01.dbf 1 0 SYSTEM 713031680 /u01/app/oraData/orcl/sysaux01.dbf 2 1 ONLINE 555745280 /u01/app/oraData/orcl/undotbs01.dbf 3 2 ONLINE 78643200 /u01/app/oraData/orcl/users01.dbf 4 4 ONLINE 5242880 /u01/app/oraData/orcl/hbk_data.dbf 5 7 ONLINE 52428800 /u01/app/oraData/orcl/CMS69_DATA.dbf 6 8 ONLINE 1310720000 6 rows selected. |
结合视图DBA_FREE_SPACE和DBA_DATA_FILES，可以查询出一个表空间的使用情况。
|
1 2 3 4 5 6 7 8 9 10 |
SQL> set linesize 100 SQL> desc dba_free_space Name Null? Type ----------------------------------------------------- -------- ------------- TABLESPACE_NAME VARCHAR2(30) FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
select tbs 表空间名, sum(totalM) 总共大小M, sum(usedM) 已使用空间M, sum(remainedM) 剩余空间M, sum(usedM)/sum(totalM)*100 已使用百分比, sum(remainedM)/sum(totalM)*100 剩余百分比 from ( select b.file_id ID, b.tablespace_name tbs, b.file_name name, b.bytes/1024/1024 totalM, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM, sum(nvl(a.bytes,0))/1024/1024 remainedM, (100-(sum(nvl(a.bytes,0))/(b.bytes)*100)) 已使用百分比 from dba_free_space a,dba_data_files b where a.file_id = b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name ) group by tbs |
|
1 2 3 4 5 6 7 8 9 10 |
表空间名 总共大小M 已使用空间M 剩余空间M 已使用百分比 剩余百分比 ------------------------------ ------------- ---------------- ------------- ------------------ --------------- UNDOTBS1 75 39.375 35.625 52.5 47.5 CMS69_DATA 1250 1185.3125 64.6875 94.825 5.175 SYSAUX 530 493.25 36.75 93.0660377 6.93396226 USERS 5 1.3125 3.6875 26.25 73.75 SYSTEM 680 676.5 3.5 99.4852941 .514705882 HBK_DATA 50 1.1875 48.8125 2.375 97.625 6 rows selected. |
获取创建表空间的SQL语句，时间久了，表空间容量不够，或者建立的表空间参数不合理，你的项目经理很可能会问你建表空间的SQL。
通过包DBMS_METADATA的过程GET_DDL可以得到创建表空间的SQL
|
1 2 3 4 5 6 7 |
SQL> select dbms_metadata.get_ddl('TABLESPACE','HBK_DATA') from dual; DBMS_METADATA.GET_DDL('TABLESPACE','HBK_DATA') -------------------------------------------------------------------------------- CREATE TABLESPACE "HBK_DATA" DATAFILE '/u01/app/oraData/orcl/hbk_data.dbf' |
转自：https://blog.csdn.net/huangbaokang/article/details/89840181