oracle常用SQL语句

获取表

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