oracle常用sql

查看show parameter 参数时 经常会出现换行显示VALUE换行显示,可通过value_col_plus_show_param调整列大小
 COL value_col_plus_show_param FORMAT A40;

#查表空间的表按照大小排序--表大小排序
 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;

启用会话中的跟踪：
sql> alter session set sql_trace=true;

刷新共享池，从库高速缓存中删除所有sql语句：
sql> alter system flush shared_pool;

USR_FIRST 用户可以只读访问 USR_SECOND 所有表包含新建的表-只读赋权。
set pagesize 0
set head off;
set feedback off;
set newpage none;
spool /tmp/grant.sql
select 'grant select on '||'"'||owner||'"."'||table_name||'"'||' to USR_FIRST;' from dba_tables where OWNER='USR_SECOND' union select 'grant select on '||'"'||owner||'"."'||VIEW_NAME||'"'||' to USR_FIRST;' from dba_views where OWNER='USR_SECOND';
spool off;
! sed -ri '/SYS@/d' /tmp/grant.sql
@/tmp/grant.sql

expdp或impdp从 -导入导出 备份失败-直接得出可以kill的命令
 set pagesize 199 linesize 199;
 set head off
 SELECT 'drop table ' || owner_name || '.' || job_name || ' purge;' FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

select sessiontimezone from dual;（客户端连接时区）
select dbtimezone from dual;   （数据库时区）
修改数据库的时区：alter database set time_zone='+08:00';
  

部署后确认时区-正确显示是 +00:00
SYS > select dbtimezone from dual;  

DBTIME
------
+00:00

启动supplemental log：
SQL>alter database add supplemental log data;

关闭supplemental log：
SQL>alter database drop supplemental log data;

查看 supplemental log：
SQL>select supplemental_log_data_min from v$database;

LogMiner分析

使用脚本创建相关的包
@$ORACLE_HOME/rdbms/admin/dbmslm.sql
@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;
exec sys.dbms_logmnr.add_logfile(logfilename => '/backup/orcl/1_32371_1142084139.dbf',options => dbms_logmnr.new);
使用本地的在线数据字典分析归档日志
exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

查询分析出来的归档日志内容，例如统计最大修改量的Schema
set pagesize 199 linesize 199;
col seg_owner for a30;
col SEG_OWNER for a20;
select seg_owner,count(*) from v$logmnr_contents group by seg_owner;
select count(1),substr(sql_redo,1,30) from v$logmnr_contents group by substr(sql_redo,1,30) order by count(1) desc ;
添加新的redo日志组：
exec sys.dbms_logmnr.add_logfile(logfilename=>'/backup/orcl/1_32372_1142084139.dbf');

查看某时间段删除进回收站的表
select OWNER,OBJECT_NAME,ORIGINAL_NAME,CREATETIME from dba_recyclebin where CREATETIME > '2023-08-27:15:00:00' and CREATETIME < '2023-08-27:20:00:00' order by CREATETIME desc ;

生成awr使用自带脚本  @?/rdbms/admin/awrrpt
RAC生成AWR报告 @?/rdbms/admin/awrgrpt.sql
生成ash使用自带脚本：@?/rdbms/admin/ashrpt