oracle常用sql

持续更新中

#查表空间的表按照大小排序--表大小排序
 set pagesize 199 linesize 199;
 col SEGMENT_NAME for a30;
 select * from (Select Segment_Name,Sum(bytes)/1024/1024/1024 From dba_Extents where TABLESPACE_NAME='TS_EMP' Group By Segment_Name order by 2 desc ) WHERE ROWNUM <= 10 ;

#归档量--每天的归档量
set linesize 199  pagesize 199;
col TRUNC(completion_time) for a25;
alter session set NLS_date_FORMAT='yyyy-mm-dd hh24:mi:ss';
SELECT SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024 AS "Size(G)",
TRUNC(completion_time) FROM v$archived_log
GROUP BY TRUNC(completion_time) order by 2 desc;  

#收集整个用户下的所有对象统计信息
exec dbms_stats.gather_schema_stats('USR_EMP',options=>'gather stale',estimate_percent =>10);

#数据库的字符集-字符集
prompt  ///2.DB nls_database_parameters
set pagesize 199 linesize 199;
col  PARAMETER for a30;
col VALUE for a40;
select * from nls_database_parameters;

#查看数据库的用户和默认用户表空间、临时表空间的位置
set pagesize 199 linesize 199;
col USERNAME for a26;
col DEFAULT_TABLESPACE for a30;
col CREATED for a20;
col ACCOUNT_STATUS for a20;
col TEMPORARY_TABLESPACE for a30;
select username,to_char(created,'YYYY-MM-DD HH24:MI:SS') created,ACCOUNT_STATUS,default_tablespace,temporary_tablespace from dba_users  order by CREATED;

#undo使用率高的问题
select tablespace_name,status,sum(bytes)/1024/1024 MB from dba_undo_extents
where tablespace_name like 'UNDOTBS%'
group by tablespace_name,status
order by 1;