草稿

select tablespace_name,
max_gb,
used_gb,
round(100 * used_gb / max_gb) pct_used,
status,LOGGING,FORCE_LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE
from (
select t.status status,t.LOGGING LOGGING,t.FORCE_LOGGING FORCE_LOGGING,t.EXTENT_MANAGEMENT EXTENT_MANAGEMENT,t.ALLOCATION_TYPE ALLOCATION_TYPE,a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),
2) used_gb,
round(a.maxbytes / power(2, 30), 2) max_gb
from dba_tablespaces t,(select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = t.tablespace_name
union all
select t.status status,t.LOGGING LOGGING,t.FORCE_LOGGING FORCE_LOGGING,t.EXTENT_MANAGEMENT EXTENT_MANAGEMENT,t.ALLOCATION_TYPE ALLOCATION_TYPE,h.tablespace_name tablespace_name,
round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,
round(sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) / power(2, 30),
2) max_gb
from dba_tablespaces t,v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
and t.tablespace_name = h.tablespace_name
group by h.tablespace_name,t.status,t.LOGGING,t.FORCE_LOGGING,t.EXTENT_MANAGEMENT,t.ALLOCATION_TYPE
)
order by 4;

用户用了多大表空间
select * from
(select owner,tablespace_name, sum(b) g from
(select owner,
t.segment_name,
t.partition_name,
round(bytes / 1024 / 1024 / 1024, 2) b,
tablespace_name
from dba_segments t)
where owner not in
('SYS', 'OUTLN', 'SYSTEM', 'TSMSYS', 'DBSNMP', 'WMSYS')
group by owner,tablespace_name)
order by g desc;

--查看表空间是否具有自动扩展的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;


QUERY=MOD_MR_STATDATA_KQI:"WHERE msg_time >= TO_DATE(\'20110609 10:00:00\',\'yyyymmdd hh24:mi:ss\') AND msg_time <= TO_DATE(\'20110609 13:00:00\',\'yyyymmdd hh24:mi:ss\')"

1.下面的语句用来查询哪些对象被锁：


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 object_name,machine,s.sid,s.serial#,s.inst_id
from gv$locked_object l,dba_objects o ,gv$session s
where l.object_id=o.object_id and l.session_id=s.sid
and o.object_name='PROCESSED_VOU_IN_REGION_TMP_PT';

查看远程归档状态
select dest_name,status,error from v$archive_dest;

DG备库查看日志应用情况
set line 150
set pagesize 100
select * from (select dest_id,thread#,sequence#,applied,to_char(first_time,'yyyymmdd hh24:mi:ss') as first_time,to_char(next_time,'yyyymmdd hh24:mi:ss') as next_time
from gv$archived_log
order by sequence# desc)
where 1=1 and rownum <=400;

select PROCESS,PID,STATUS,THREAD#,SEQUENCE# from gv$managed_standby;
select process,client_process,sequence#,status,BLOCK#,BLOCKS from gv$managed_standby where PROCESS like '%M%';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database recover managed standby database disconnect from session parallel 16;

--开启bct之后需要设置的参数
alter system set "_log_read_buffers"=256 scope=spfile;
alter system set "_bct_public_dba_buffer_size"=200000000 scope=both sid='*';
alter system set "_bct_public_dba_buffer_maxsize"=200000000 scope=both sid='*';
alter system set "_bct_buffer_allocation_max"=1073741824 scope=both sid='*';

1.日常维护

1.停止Standby
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;

2.切换到只读模式
-----由shutdown模式切换到只读模式-------
startup nomount;
alter database mount standby database;
alter database open read only;
-----由应用日志模式切换到只读模式-------
alter database recover managed standby database cancel;-- 取消日志应用
alter database open read only;

3.切换回管理恢复模式
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;-- 启动日志应用
alter database recover managed standby database using current logfile disconnect from session;

4.
-查看应用日志延迟时间：

select value from v$dataguard_stats where name='apply lag';



-查看接收日志延迟时间：

select value from v$dataguard_stats where name='transport lag';



-查看主库归档

Primary:
select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;



-查看备库已接收归档

PhyStdby:
select thread#, max(sequence#) "Last Standby Seq Received" from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;



-查看备库已应用归档

PhyStdby:
select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;



-查看归档应用详细情况

select first_time,sequence#,applied from v$archived_log;



-查看主备库GAP

select * from v$archive_gap;




11g 备库上面 停止MRP ,启动MRP


SQL> recover managed standby database cancel;
Media recovery complete.
startup mount;



SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;




4.主库和备库之间角色切换
4.1 主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

4.2 从库切换为主库
alter database commit to switchover to primary;
shutdown immediate;
startup
alter system switch logfile;

5.备库自动使用主库传过来的日志进行恢复
alter database recover automatic standby database;

6.更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;

7.取消自动恢复模式
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;


mysql重启
/paic/mysql/5.7.26/bin/mysqladmin --login-path=root --socket=/paic/my3461/var/mysql.sock shutdown
停库：/paic/mysql/5.7.26/bin/mysqladmin --login-path=root --socket=/paic/my3461/var/mysql.sock shutdown
启库：/bin/sh /paic/mysql/5.7.26/bin/mysqld_safe --defaults-file=/paic/my3461/my.cnf &
/bin/sh /paic/mysql/5.7.26/bin/mysqld_safe --defaults-file=/paic/my3461/my.cnf &
OGG按照如下sql去判断长事务，只要start_time是30分钟前的，都任务是长事务。

select s.SID, s.SERIAL#, s.sql_id, s.OSUSER,s.USERNAME,s.PREV_HASH_VALUE,s.SQL_HASH_VALUE,to_date(t.START_TIME,'MM/dd/yy hh24:mi:ss'),t.USED_UBLK
from v$transaction t, v$session s
where t.SES_ADDR = s.SADDR ;


cd $GGS_HOME
./ggsci
send E_GBS showtrans
send E1_GBS showtrans

select s.SID, s.SERIAL#, s.sql_id, s.OSUSER,s.USERNAME,to_date(t.START_TIME,'MM/dd/yy hh24:mi:ss'),t.STATUS trans_status,s.STATUS sess_status
from v$transaction t, v$session s
where t.SES_ADDR = s.SADDR
and t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN in('263.17.3443557','163.18.10671521');

--单实例
select 'alter system kill session '''||a.sid||',' ||a.serial#||''';'
from v$transaction b, v$session a
where b.addr=a.taddr and (sysdate-START_DATE)*24*60 > 5 order by b.start_time;
--rac
select 'alter system kill session '''||a.sid||',' ||a.serial#||',@'||a.inst_id||''';'
from gv$transaction b, gv$session a
where b.addr=a.taddr and (sysdate-START_DATE)*24*60 > 5 order by b.start_time;

查询临时表空间的使用率：
select c.tablespace_name,
to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,
to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
from (select tablespace_name,sum(bytes) bytes
from dba_temp_files GROUP by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool GROUP by tablespace_name) d
where c.tablespace_name = d.tablespace_name;

--rac
select d.INST_ID,
c.tablespace_name,
to_char(c.bytes / 1024 / 1024 / 1024, '99,999.999') total_gb,
to_char((c.bytes - d.bytes_used) / 1024 / 1024 / 1024, '99,999.999') free_gb,
to_char(d.bytes_used / 1024 / 1024 / 1024, '99,999.999') use_gb,
to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
GROUP by tablespace_name) c,
(select INST_ID, tablespace_name, sum(bytes_cached) bytes_used
from gv$temp_extent_pool
GROUP by inst_id,tablespace_name) d
where c.tablespace_name = d.tablespace_name;

查询那些用户在使用
select a.username,
a.sql_id,
a.SEGTYPE,
b.BYTES_USED/1024/1024/1024||'G',
b.BYTES_FREE/1024/1024/1024 from V$TEMPSEG_USAGE a join V$TEMP_SPACE_HEADER b on a.TABLESPACE=b.tablespace_name;

查询temp表空间使用率：
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+);

查询实时使用temp表空间的sql_id和sid：
set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
sid,
serial#,
se.sql_id
machine,
program,
tablespace
segtype,
(su.BLOCKS*8/1024/1024) GB
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr
order by su.BLOCKS desc;

查询历史的temp表空间的使用的SQL_ID：
select a.SQL_ID,
a.SAMPLE_TIME,
a.program,
sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
from v$active_session_history a
where TEMP_SPACE_ALLOCATED is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
order by 2 asc,4 desc;

查询用户有那些角色
select * from dba_role_privs t where t.grantee='DONGRONGRONG448';
select 'grant '||GRANTED_ROLE||' to OPESUP;' from dba_role_privs t where t.grantee='DBMONOPR';
查询用户/角色有那些系统权限
select * from dba_sys_privs t where t.grantee='DBMONOPR';
select 'grant '||PRIVILEGE||' to OPESUP;' from dba_sys_privs t where t.grantee='DBMONOPR';
查询用户/角色有那些对象权限
select * from dba_tab_privs t where t.grantee='DBMONOPR';
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to OPESUP;' from dba_tab_privs t where t.grantee='DBMONOPR';


SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10





查看异常等待事件
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Tot"
from gv$session_Wait
where event not like '%SQL*Net%'
and event not like '%timer%'
and event not like 'rdbms ipc message'
and wait_class# != 6
and event not like '%idle wait%'
group by event
order by 4 desc;

找到对应的sid或则sql_id

select l.hash_value, l.sql_id, s.event, count(*)
from v$session s,
v$sql l
where s.wait_class# != 6
and s.sql_id = l.sql_id
group by l.hash_value, s.event, l.sql_id
order by 4 desc, 3 asc;


查询绑定变量的带入值
SELECT NAME, P.POSITION, P.DATATYPE_STRING, P.VALUE_STRING
FROM V$SQL_BIND_CAPTURE P
WHERE P.SQL_ID = '461prg85g2j23';


/opt/cgtools/cginfo -t perf -s mem/cpu
cat /opt/cgtools/etc/cg.cfg |grep -i t0ecif


SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE
signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id='2y3f4nnuz1tgz');

ALTER USER gbscmcc GRANT CONNECT THROUGH deployop;

D:\Users\LICHENGWEI285\AppData\Roaming\VanDyke\Config


ps -fu oraprod |awk '{print $2}'|xargs kill -9

$ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -phBaseFile ./patch_list_gi_home.txt


exec dbms_ijob.next_date(4002,to_date('2018-06-27 11:15:00','yyyy-mm-dd hh24:mi:ss'));



fgrep "24-AUG-2022 04:0" listener_scan1.log | fgrep "establish" | awk '{ print $1 " "$2 }' | awk -F: '{ print $1 ":" $2 }' | sort | uniq -c


cat listener_scan1.log | grep "24-AUG-2022 04:0" |grep establish | sed 's/\*.*HOST=/ /g;s/).*HOST=/ /g;s/).*$//g' | awk '{print $3}' | sort -rn | uniq -c | sort -k 1

(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=5)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=gbs.db.paic.com.cn)(PORT=1526)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvp_gbs_egis_hsp.world)))(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=5)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=gbs.dbldr.paic.com.cn)(PORT=1526)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvp_gbs_egis_hsp.world))))


(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=4)(RETRY_COUNT=5)(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=gbs.dbldr.paic.com.cn)(PORT=1528)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvp_gbs_egis_hsp.world))))


exec dbms_service.start_service('srvp_t3gbs');

--停crontab
cd $HOME
cp .cron_file cron_file_20220727
cat /dev/null > .cron_file
crontab .cron_file
crontab -l --确认是否已经清空

cd $HOME
cp cron_file_20220727 .cron_file
crontab .cron_file
crontab -l


ssh等效性
cd /paic/app/oracle/rdbms/19c/19.14.0/oui/prov/resources/scripts
./sshUserSetup.sh -user poracle -hosts "cnsz083680 cnsz083681 cnsz083682 cnsz083683 cnsz083680-priv cnsz083681-priv cnsz083682-priv cnsz083683-priv" -advanced -confirm -noPromptPassphrase

---抓top sql
select to_char(sysdate, 'yyyy-mm-dd') SDATE, res.*
from (select distinct se.sql_id,
EXECUTIONS,
round(sum(ELAPSED_TIME) / 1000000, 2) AS TOTAL_ELAPSED_TIME,
round(sum(ELAPSED_TIME)/ EXECUTIONS / 1000000, 2) AS ELAPSED_TIME,
round(sum(CPU_TIME) / EXECUTIONS/ 1000000, 2) AS CPU_TIME,
round(sum(APPLICATION_WAIT_TIME)/ EXECUTIONS / 1000000, 2) AS clwait_time,
round(sum(BUFFER_GETS) / EXECUTIONS, 2) AS buffer_gets,
round(sum(DISK_READS) / EXECUTIONS, 2) AS disk_read,
to_char(substr(SQL_TEXT,1,2000)) as sql_text
from v$sql wr,v$session se
where wr.sql_id=se.sql_id
and EXECUTIONS>0
group by se.sql_id,
EXECUTIONS,
ELAPSED_TIME,
sql_text
order by TOTAL_ELAPSED_TIME desc
) res
where rownum < 500;

---执行计划变换的sql信息

with base_info as
(
select parsing_schema_name as exec_user,
sql_id, plan_hash_value as phv,
executions as execs,
elapsed_time as ela
from gv$sql where plan_hash_value>0 and elapsed_time>0
union all
select parsing_schema_name,
sql_id, plan_hash_value,
executions_delta as execs,
elapsed_time_delta
from DBA_HIST_SQLSTAT
where plan_hash_value>0 and elapsed_time_delta>0
and snap_id >= (select min(snap_id) from dba_hist_snapshot where begin_interval_time>=sysdate-8
and dbid=(select dbid from v$database)
)
and dbid=(select dbid from v$database)
),tmp_gby as
(
select exec_user,sql_id,phv,sum(ela) as sum_ela,greatest(sum(execs),1) as sum_execs
,round(sum(ela)/greatest(sum(execs),1)) as avg_etime
from base_info
group by exec_user,sql_id,phv
),tmp_ana as
(select sql_id, exec_user,phv as best_phv, sum_execs as b_sum_execs, avg_etime as b_avg_etime
,round(stddev(avg_etime) over (partition by exec_user,sql_id)) as stddev_etime
,count(phv) over(partition by exec_user,sql_id) as phvs
,first_value(phv) over (partition by exec_user,sql_id order by avg_etime desc)as worst_phv
,first_value(sum_execs) over (partition by exec_user,sql_id order by avg_etime desc)as w_sum_execs
,first_value(avg_etime) over (partition by exec_user,sql_id order by avg_etime desc)as w_avg_etime
,row_number() over(partition by exec_user,sql_id order by avg_etime) as rn
from tmp_gby
)
select * from
( select sql_id,exec_user,phvs--,stddev_etime
,best_phv,b_sum_execs
,round(b_avg_etime/1e3) as b_avg_etime_ms
,worst_phv,w_sum_execs
,round(w_avg_etime/1e3) as w_avg_etime_ms
,round(stddev_etime/b_avg_etime) as norm_stddev
,round(w_avg_etime/b_avg_etime,1) as ratio
from tmp_ana a
where phvs>1 and rn=1 and stddev_etime/b_avg_etime>1
order by stddev_etime/b_avg_etime desc
) where rownum<=50 order by norm_stddev;


node1

[oracle@node1 ~]$ ssh-keygen -t rsa

[oracle@node1 ~]$ ssh-keygen -t dsa

[oracle@node1 ~]$ cd .ssh

[oracle@node1 ssh]$ cat *.pub >> authorized_keys

node2

[oracle@rac2 ~]$ ssh-keygen -t rsa

[oracle@rac2 ~]$ ssh-keygen -t dsa

node1

[oracle@rac1 ssh]$ scp authorized_keys node2:/home/oracle/.ssh/

node2

[oracle@rac2 ~]$ cd .ssh

[oracle@rac2 ssh]$ cat *.pub >> authorized_keys

[oracle@rac2 ssh]$ scp authorized_keys node1:/home/oracle/.ssh/

验证等效性

查询表列名乱码
select distinct owner,table_name from dba_tab_columns where length(column_name)<>lengthb(column_name)

sequence达到容量的80%
select sequence_owner, sequence_name, max_value, last_number
from dba_sequences
where last_number >= max_value * 0.8
and sequence_owner not in ('GBSREPT', 'DKVTEST', 'DMLBAK')
and CYCLE_FLAG <> 'Y';


pg查看建库的时间
SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, datname FROM pg_database;


select 'select pg_terminate_backend('||PID||');' from pg_stat_activity where query like '%select ss.id as stationId%';

nohup pg_dump -Fd -d tag -U postgres -j 10 -Z 9 $(cat 1.txt | xargs -I % echo "-t %") -v -f /pacloud/pgbackup/licw/fltag20230530/risk_tag_his.dump > risk_tag_his.log &

pg_dump -d tag -s $(cat 1.txt | xargs -I % echo "-t %") > 1.sql


set password for 'dtcoll'@'%' = password('Gxik_3Rho');

redis-cli -p 16355 -a '6Yg78E!Rn' -c <<EOF
flushall
EOF

create role r_bdpbispdata_dev_qry;
grant r_bdpbispdata_dev_qry to devsup01 ;
select 'grant select on '|| schemaname ||'.'|| tablename ||' to r_&r_bdpbispdata_dev_qry;' from pg_tables where tableowner='bdpbispdata';


查询事务id和状态
SELECT LOCAL_TRAN_ID,STATE FROM DBA_2PC_PENDING;
一般是处理prepared状态的事务，prepared状态需要rollback force之后，再通过dbms包purge掉。其他状态可以直接通过dbms包purge掉。
处理流程：
rollback force 'local_tran_id';
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
commit;
如果遇到在rollback的过程中hang死，可以参考mos上面的文档进行处理
How To Resolve Stranded DBA_2PC_PENDING Entries [ID 401302.1]


---查询表中数据存放的数据块情况
set linesize 200
col name format a40
select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b where a.file_id=b.file# and a.owner='&Schema' and a.segment_name='&TableName’;
----根据数据文件与块号查找对象
Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=&file_id and &block_id between block_id and block_id+blocks-1;


计算Oracle实例中不同用户的cpu消耗占比
select SYS_CONTEXT ('USERENV', 'DB_NAME') DB_NAME , owner, rs_type, round( val/1000,2) schema_cpu , round(val / sum(val) over(), 4) pct
from (
select PARSING_SCHEMA_NAME owner , 'CPU-AWR' rs_type , CPU_TIME val , round( CPU_TIME /sum(CPU_TIME) over() ,4) pct
from (
select t.PARSING_SCHEMA_NAME , sum( t.CPU_TIME_DELTA/1000) CPU_TIME from dba_hist_sqlstat t ,dba_hist_snapshot sn
where t.PARSING_SCHEMA_NAME not in('<EMPTY>','FOGLIGHT','<DONT_CARE>','SYS','FGLPA','APPMGR','DBMGR','DBMON','FACTUSERMGR','OVSEE','DBQUA','DBQINTF','DEVDBA','FACTUSERMGR','PUBLIC','TOAD',
'DEPLOYOP','DBMONOPR','DBMONOPR02','RSMSMONOPR','GGMGR', 'EDSOP','DMLBAK','ORA_RECO_070361','SYSTEM')
and PARSING_SCHEMA_NAME not in
('SYS', 'SYSTEM', 'DBMON', 'DBQUA', 'DMLBAK', 'OVSEE', 'DBMGR','PERF_STAT','DBADATA','SCOTT',
'SH','HR','LIANGHAIAN001','LIULI044','LIBING3','ZHANGZHONGJING001','LIUSHUAN001','GONGXUNFENG001',
'APPMGR', 'OUTLN', 'DBSNMP', 'DEPLOYOP','I3_ORCL','PERFSTAT','XDB','ZHANGZH1','TIANXY','LUHH',
'YAOYI','EXPFULL','DENGLP','ZOULEI','YUNCHENGYUE','DBMONOPR02','FOGLIGHT','DBAQRY',
'I3_ORCL1', 'I3_ORCL2','I3_ORCL_R1','I3_ORCL_R2','GGMGR','FGLPA','TOAD','SPOTLIGHT')
and PARSING_SCHEMA_NAME not like '%SYS%'
-- and not exists (select 1 from factuser_list a where a.oa_name = PARSING_SCHEMA_NAME)
and t.SNAP_ID = sn.SNAP_ID
and t.INSTANCE_NUMBER = sn.INSTANCE_NUMBER
and sn.BEGIN_INTERVAL_TIME between trunc(sysdate-30) and trunc(sysdate)
group by t.PARSING_SCHEMA_NAME
order by 2 desc)
);

--查询可以释放空间的数据文件
set linesize 200
col name for a40
col resizecmd for a80
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 * block_size)> 0
order by 5;

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
and rownum < 10;