Oracle常用系统Sql

• 查看所有表空间及表空间大小：
  select tablespace_name ,sum(bytes) / 1024 / 1024 / 1024 as GB from dba_data_files group by tablespace_name;

• 查看所有表空间对应的数据文件：
  select tablespace_name,file_name from dba_data_files;

• 修改数据文件大小：
  alter database datafile ‘\oradata\test.dbf’ resize 10240M;

• 查看共有哪些用户：
  select * from dba_users;

• 查看当前用户共占用空间：
  select Sum(bytes) / 1024 / 1024 / 1024 “size(GB)”
  From User_Extents
  order by “size(GB)” desc, Segment_Name;

• 查看testUser1用户下有哪些表：
  SELECT * FROM all_tables t WHERE t.OWNER = ‘testUser1’;

• 查看分区表的分区名和所属表空间：
  select table_name,partition_name,tablespace_name
  from user_tab_partitions;

• 查看testUser1表空间被哪些用户使用：
  select owner from dba_tables where tablespace_name=‘testUser1’ group by owner;

• 查看testUser1用户所有的权限和角色：
  select privilege from dba_sys_privs where grantee=‘testUser1’
  union
  select privilege from dba_sys_privs where grantee in
  (select granted_role from dba_role_privs where grantee=‘testUser1’ );

• 查看testUser1用户使用了哪些表空间：
  select distinct tablespace_name from dba_segments where owner=‘testUser1’;

• 查找非系统用户(不全)：
  select username from dba_users where username not in
  (‘SYS’
  ,‘SYSTEM’
  ,‘OUTLN’
  ,‘FLOWS_FILES’
  ,‘MDSYS’
  ,‘ORDSYS’
  ,‘EXFSYS’
  ,‘DBSNMP’
  ,‘WMSYS’
  ,‘APPQOSSYS’
  ,‘APEX_030200’
  ,‘ORDDATA’
  ,‘CTXSYS’
  ,‘ANONYMOUS’
  ,‘XDB’
  ,‘ORDPLUGINS’
  ,‘SI_INFORMTN_SCHEMA’
  ,‘OLAPSYS’
  ,‘ORACLE_OCM’
  ,‘XS$NULL’
  ,‘MDDATA’
  ,‘DIP’
  ,‘APEX_PUBLIC_USER’
  ,‘SPATIAL_CSW_ADMIN_USR’
  ,'SPATIAL_WFS_ADMIN_USR‘）；

• 查看t1表的建表语句：
  select dbms_metadata.get_ddl( ‘TABLE’, ‘t1’ ) from dual;

• 查看t1表的表字段，并用逗号隔开：
  select wm_concat(column_name) from user_tab_cols where table_name=‘t1’;

• 查看有哪些表：
  select object_name from USER_OBJECTS where object_type in (‘TABLE’);

• 查看不为空的表：
  select table_name from user_tables where num_rows !=0 and num_rows is NOT NULL;

• 查看表空间大小
  select a.tablespace_name, total, free, total-free as used from
  (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
  (select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
  where a.tablespace_name = b.tablespace_name;

• 查看表大小：
  select Segment_Name, Sum(bytes) / 1024 / 1024 / 1024 “size(GB)”
  From User_Extents
  Group By Segment_Name
  order by “size(GB)” desc, Segment_Name;

• 查看表空间使用大小
  select file_name,bytes/1024/1024/1024 from dba_data_files
  where tablespace_name like ‘testUser1’;

• 数据文件大小
  select a.file#,
  a.name,
  a.bytes / 1024 / 1024 CurrentMB,
  ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
  (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
  ‘alter database datafile ‘’’ || a.name || ‘’’ resize ’ ||
  ceil(HWM * a.block_size) / 1024 / 1024 || ‘M;’ ResizeCmd
  from v$datafile a,
  (SELECT file_id, MAX(block_id + blocks - 1) HWM
  FROM DBA_EXTENTS
  GROUP BY file_id) b
  where a.file# = b.file_id(+)
  And (a.bytes - HWM * a.block_size) >0
  ORDER BY NAME;