表解释:
dba_tables : 系统里所有的表的信息,需要DBA权限才能查询
all_tables : 当前用户有权限的表的信息(只要对某个表有任何权限,即可在此视图中看到表的相关信息),会查出数据字典。
user_tables: 当前用户名下的表的信息,不会查出数据字典。
1.1查询所有表空间的该表
select * from all_tables where table_name = 'DRAFT_INFO';
1.2查询当前登录用户的该表所属表空间
SELECT
TABLE_NAME,
TABLESPACE_NAME
FROM
USER_TABLES
where table_name='DRAFT_INFO'
2、查看表空间使用情况:
SELECT tablespace_name,SUM(bytes) / (1024 * 1024) AS free_space
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY FREE_SPACE;
SELECT a.tablespace_name,
a.bytes/(1024 * 1024) as total,
b.bytes/(1024 * 1024) as used,
c.bytes/(1024 * 1024) as free,
round((b.bytes * 100) / a.bytes,2) as "% USED ",
round((c.bytes * 100) / a.bytes,2) as "% 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 查询表空间是否自增
select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name = 'SYSTEM'