1、檢視錶空間的物理路徑以及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
2、檢視錶空間的使用情況
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
3、檢視錶空間是否為自動擴充套件，autoextensible為YES表示自動擴充套件
select file_name,autoextensible,increment_by from dba_data_files;
4、檢視錶空間建立語句(感謝OO的幫助。。。)
SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 30 12:54:14 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> conn sys/oracle as sysdba
Connected.
SQL> create tablespace yourtablespace datafile 'ytb.dbf' size 10M;
Tablespace created.
SQL> select dbms_metadata.get_ddl('TABLESPACE','YOURTABLESPACE') from dual;
DBMS_METADATA.GET_DDL('TABLESPACE','YOURTABLESPACE')
--------------------------------------------------------------------------------
CREATE TABLESPACE "YOURTABLESPACE" DATAFILE
  'C:\ORACLEXE\APP\ORACLE\PRODUC
SQL>  set long 2000
SQL> /
DBMS_METADATA.GET_DDL('TABLESPACE','YOURTABLESPACE')
--------------------------------------------------------------------------------
CREATE TABLESPACE "YOURTABLESPACE" DATAFILE
  'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\YTB.DBF' SIZE 10485760
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEM
ENT AUTO

表空間相關查詢

相關文章