获取表
select table_name from user_tables; //当前用户的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner='username'
获取表字段
select *
from user_tab_columns
where Table_Name = 'table_name'
order by column_name
剩余表空间百分比
select df.tablespace_name "表空间名",
totalspace "总空间M",
freespace "剩余空间M",
round((1 - freespace / totalspace) * 100, 2) "使用率%"
from (select tablespace_name,
round(sum(bytes) / 1024 / 1024) totalspace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1024 / 1024) freespace
from dba_free_space
group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name;
检查依赖
Select b.table_name 主键表名,
b.column_name 主键列名,
a.table_name 外键表名,
a.column_name 外键列名
From (Select a.constraint_name,
b.table_name,
b.column_name,
a.r_constraint_name
From user_constraints a, user_cons_columns b
Where a.constraint_type = 'R'
And a.constraint_name = b.constraint_name) a,
(Select Distinct a.r_constraint_name, b.table_name, b.column_name
From user_constraints a, user_cons_columns b
Where a.constraint_type = 'R'
And a.r_constraint_name = b.constraint_name) b
Where a.r_constraint_name = b.r_constraint_name
检查被锁定的表
select object_name, machine, s.sid, s.serial#
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid;
计算表占用空间的大小
select segment_name table_name,
sum(blocks) blocks,
sum(bytes) / (1024 * 1024) "table_size[mb]"
from user_segments
where segment_type = 'table'
and segment_name = &table_name
group by segment_name;
查看数据库是否为CDB
select name,
decode(cdb,
'YES',
'Multitenant Option enabled',
'Regular 12c Database: ') "Multitenant Option",
open_mode,
con_id
from v$database;
查看某个表空间下的表数量
select *
from dba_tables
where tablespace_name = 'tablespace_name'
and owner = 'owner'
查找工作空间的路径
select * from dba_data_files
