獲取表
select table_name from user_tables; //當(dāng)前用戶的表
select table_name from all_tables; //所有用戶的表
select table_name from dba_tables; //包括系統(tǒng)表
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;
計(jì)算表占用空間的大小
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;
查看數(shù)據(jù)庫是否為CDB
select name,
decode(cdb,
'YES',
'Multitenant Option enabled',
'Regular 12c Database: ') "Multitenant Option",
open_mode,
con_id
from v$database;
查看某個(gè)表空間下的表數(shù)量
select *
from dba_tables
where tablespace_name = 'tablespace_name'
and owner = 'owner'
查找工作空間的路徑
select * from dba_data_files
