表空间相关查询

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28686045/viewspace-1469886/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28686045/viewspace-1469886/