∵1、Oracle 查看失效的存储或无效对象JOB等
1-1、查看失效的存储
select * from user_objects where status <> 'VALID';
1-2、查找到无效对象
select 'Alter '||object_type||' '||object_name||' compile;' from user_objects where status = 'INVALID';
1-3、查看失效的job
select * from user_jobs where failures <>0;
1-4、查询RMI语句信息
set linesize 220
set heading off
set feedback off
set pagesize 0
set verify off
COL GAME_CODE FOR A20
COL ADDR FOR A20
COL REGISTRY_PORT FOR A20
COL SERVICE_PORT FOR A20
SELECT A.GAME_CODE,A.ADDR,A.REGISTRY_PORT,A.SERVICE_PORT,A.jar_id FROM FS_S10_GAME_JAR A WHERE game_code in ('AAA0001','AAA0004');
∵2、Oracle数据库用户锁定原因以及处理方式（ORA-28000）
2-1、查看具体的被锁时间
SQL> select username,lock_date from dba_users where username=’ CGSIT2019’;
USERNAME LOCK_DATE
TEST 2020-11-11 11:11:11
--上方为查询结果
2-2、在DB的监听日志查看该时间段符合连接的用户信息，确认是用户锁定还是程序锁定，请注意文件路径有变
[oracle@CGSITDB alert]$ less /data/oracle/diag/tnslsnr/CGSITDB/listener/alert/log.xml
2-3、解锁
SQL> alter user CGSIT2019 account unlock;
User altered.
2-4、查看是那个ip造成的test用户被锁
一般数据库默认是10次尝试失败后锁住用户
2-4-1、查看FAILED_LOGIN_ATTEMPTS的值
select * from dba_profiles
2-4-2、修改为30次
alter profile default limit FAILED_LOGIN_ATTEMPTS 30;
2-4-3、修改为无限次(为安全起见，不建议使用)
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
∵3、Oracle数据库表空间不足，扩容信息（ORA-01653 unable to extend table）
3.1、查看表空间大小，扩容表空间
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
T.TOT_TABLESPACE_SIZE_MB "表空间大小(M)",
T.TOT_TABLESPACE_SIZE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round((T.TOT_TABLESPACE_SIZE_MB - F.TOTAL_BYTES) / T.TOT_TABLESPACE_SIZE_MB * 100, 2), '990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BLOCK "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BLOCK
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT D.TABLESPACE_NAME,
Round(Sum(D.BYTES) / (1024 * 1024), 2) TOT_TABLESPACE_SIZE_MB
FROM SYS.DBA_DATA_FILES D
GROUP BY D.TABLESPACE_NAME) T
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
查看一下用户表空间文件的路径：
select name from v$datafile;
3-2.查询所有用户表空间所在位置
select * from dba_data_files
3-3.扩容表空间
alter tablespace CGSIT2019 add datafile '/data/oracle/sit/datafile/CGSIT2019DB2.dbf' size 2G;
示例：CGSIT2019=表空间名字 /data/oracle/sit/datafile/=路径 CGSIT2019DB2.dbf=数据文件名字（不能重复） 2G=扩容的大小
∵4、Oracle导入导出功能
--此部分为全量导入导出与备份
在需要导出数据的那一台数据库做 记得切换oracle 用户做
4-1、导出数据，
expdp DIRECTORY=EXPDP_DIR logfile=kf20200521.log dumpfile=kf20200521.dmp schemas=im_devService userid=im_devService/qweJsdq4GW
--示例解读 DIRECTORY=路径可在下面5-9步骤查询 logfile=定义输出日志 dumpfile=备份数据文件 schemas=一般都以owner名字命名 userid=用户/密码
--在需要导入数据的那一台数据库做以下步骤
4-2、创建表空间
CREATE TABLESPACE KFXT datafile '/data/oracle/sit/datafile/KFXT/KFXT.dbf' SIZE 3G EXTENT MANAGEMENT LOCAL;
--创建表空间，如该DB已有创建好表空间省略此步骤
4-3、创建用户并赋予用户拥有KFXT表空间的权限
create user kf_devService identified by "qweJsdq4GW" default tablespace KFXT temporary tablespace TEMP2 profile DEFAULT;
--创建用户，如该DB已有创建导入的用户省略此步骤
4-4、给用户授权
GRANT CONNECT, RESOURCE TO KF_DEVSERVICE; --一般owner用户使用这个权限
GRANT CREATE，SESSION，CONNECT TO KF_DEVSERVICE; --普通用户请使用这个权限
4-5、创建DIRECTORY的目录
create or replace directory kfdata_exp as '/data/oracle/kfback';
--请根据5-9查看是否需要重新定义路径，此步骤关联4-5 至4-8操作
4-6、给创建的目录授权，让kf_devService有读写的权限
grant read,write on directory kfdata_exp to kf_devService;
4-7、创建目录的真实路径
mkdir -p /data/oracle/kfback
4-8、给目录授权
chown -R oracle:oinstall /data/oracle/kfback
4-9、查看目录是否存在数据库里
select * from dba_directories; (sql里做)
4-10、导入dmp文件的时候，需要授导入的权限；
grant imp_full_database to kf_devService;
--给用户授予导入的权限
4-11、数据导入（需切换oracle用户操作）
impdp DIRECTORY=kfdata_exp logfile=kf20200521.log dumpfile=kf20200521.dmp SCHEMAS=im_devService remap_SCHEMA=im_devService:kf_devService remap_tablespace=devdb1:KFXT userid=kf_devService/qweJsdq4GW
--DIRECTORY=存放的数据路径，需确保改路径下存在 logfile=定义与收集导入日志信息 dumpfile=dump文件，要导入的数据，需要确认文件名字是否正确 remap_SCHEMA=更改属主用户，如用户不变省却此参数（源文件使用：新用户schema） remap_tablespace=修改用户数据表空间存放，如表空间不变省却此步骤(源用户表空间：新用户表空间名字) userid=当前用户密码
∵5、Oracle导入导出进阶功能，单表导入，排除表等
5-1、oracle 使用imoprt 导入单张表
impdp DIRECTORY=kfdata_exp logfile=20211222.log dumpfile=kf20200521.dmp tables=FS_S10_GAME_JAR userid=kf_devService/qweJsdq4GW
--kfdata_exp=存放的数据路径，需确保改路径下存在 logfile=定义与收集导入日志信息 dumpfile=dump文件，要导入的数据，需要确认文件名字是否正确 tables=导入表的名字 userid=当前的用户密码
5-2、导入数据并排除指定的表不导入
impdp DIRECTORY=kfdata_exp logfile=kf20200521.log dumpfile=kf20200521.dmp SCHEMAS=im_devService remap_SCHEMA=im_devService:kf_devService remap_tablespace=devdb1:KFXT userid=kf_devService/qweJsdq4GW exclude=table:"in('FS_S10_GAME_JAR','FS_S12_LOG')"
--此导入信息根据5-11查看信息， 新增参数exclude 排除表，如上示例提供两张表排除，如需要排除多个请填写实际表名字，需吧表名字转换成大写
--如导入不成功，提示语法有误，请吧exclude 字段所有特殊字数添加转移符号（单引号前面添加转移符号，俗称反斜杠）
5-3、导出备份并排除指定表不导出备份
expdp DIRECTORY=EXPDP_DIR logfile=kf20200521.log dumpfile=kf20200521.dmp schemas=im_devService userid=im_devService/qweJsdq4GW EXCLUDE=TABLE:\"IN\'FS_S10_GAME_JAR\',\'FS_S12_LOG\'\"
--导出并排除表，使用方式与6-2相同
5-4、按表空间导出数据（只导出该表空间的数据）
expdp DIRECTORY=EXPDP_DIR tablespace=devdb1,KFXT logfile=kf20200521.log dumpfile=kf20200521.dmp userid=kf_devService/qweJsdq4GW
--只保留表空间参数，按需要导出的表空间添加，示例只导出devdb1与KFXT 两个表空间数据
5-5、导出数据并追加覆盖目标表空间数据
impdp DIRECTORY=EXPDP_DIR logfile=kf20200521.log dumpfile=kf20200521.dmp SCHEMAS=im_devService userid=kf_devService/qweJsdq4GW table_exists_action=REPLACE
--table_exists_action:导入对象已存在时执行的操作。有效关键字:SKIP,APPEND,REPLACE和TRUNCATE
∵6、Oracle用户密码过期或忘记密码
6-1、查询所有用户的账号的状态（此步骤查询DBA表，请用DBA用户查询）
select username,account_status from dba_users WHERE username='CGSIT2019';
--根据显示的状态查看，open是启用状态，EXPIRED & LOCKED 是过期或者冻结
6-2、修改用户密码
ALTER USER 用户名 IDENTIFIED BY 密码;
--请把语句的中文替换成需要修改的信息
6-3、查询用户密码过期所剩时间策略
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
--最后一个参数显示的是UNLIMITED 则是无限期，显示数字则是剩余日期
6-4、修改DEFAULT 的默认过期时间，将180天改成密码永不过期
SELECT username,PROFILE FROM dba_users WHERE username='CGSIT2019';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
--查询用户在哪个PROFILE ，在第二条修改的位置执行修改密码策略
6-5、解锁用户状态为EXPIRED (请使用sqlplu 进入DBA用户查询)
select user#,name,password,astatus from user$ where name = 'CGSIT2019';
alter user CGSIT2019 identified by values '2D594E86F93B17A1';
--请根据第一条查询显示的PASSWORD 值复制到第二条语句的values 中，两条语句的用户记得核对，切勿修改错用户
∵7、Oracle授权表或存储给其他用户
7-1、批量查询生成授权只查询语句，在使用OWNER用户执行授权动作（linux终端的SQLPLUS 操作）
!touch /home/oracle/1111.txt
spool /home/oracle/1111.txt
select 'GRANT SELECT ON NAME_A.'||table_name||' to NAME_B;' from user_tables;
spool off
@/home/oracle/1111.txt;
commit
!rm -f /home/oracle/1111.txt
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
7-2、批量查询生成授权只查询语句，在使用OWNER用户执行授权动作（plsql工具演示）
select 'GRANT SELECT ON NAME_A.'||table_name||' to NAME_B;' from user_tables;
--根据显示的结果上边有一个绿色按钮点击获取到最后一页，全选并新建一个sql窗口 全部执行
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
7-3、批量生成存储授只查看权语句，在使用OWNER用户执行授权动作（linux终端的SQLPLUS 操作）
!touch /home/oracle/2222.txt
spool /home/oracle/2222.txt
select 'grant debug on name_A.' || object_name || ' to ' || 'name_B' || ';' from all_objects where object_type in ('PROCEDURE', 'FUNCTION','PACKAGE') and owner = 'name_A';
spool off
@/home/oracle/2222.txt;
commit
!rm -f /home/oracle/2222.txt
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
7-4、批量生成存储授只查看权语句，在使用OWNER用户执行授权动作 （plsql工具演示）
select 'grant debug on name_A.' || object_name || ' to ' || 'name_B' || ';' from all_objects where object_type in ('PROCEDURE', 'FUNCTION','PACKAGE') and owner = 'name_A';
--根据显示的结果上边有一个绿色按钮点击获取到最后一页，全选并新建一个sql窗口 全部执行
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
∵8、Oracle创建owner 表同义词（synonym）给其他用户使用
8-1、批量查询生成授权只查询语句，在使用OWNER用户执行授权动作（linux终端的SQLPLUS 操作）
!touch /home/oracle/1111.txt
spool /home/oracle/1111.txt
select 'create or replace synonym NAME_B.'||table_name||' for NAME_A.'||table_name||';' from all_tables WHERE owner='NAME_A';
spool off
@/home/oracle/1111.txt;
commit
!rm -f /home/oracle/1111.txt
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
8-2、批量查询生成授权只查询语句，在使用OWNER用户执行授权动作（plsql工具演示）
select 'create or replace synonym NAME_B.'||table_name||' for NAME_A.'||table_name||';' from all_tables WHERE owner='NAME_A';
--根据显示的结果上边有一个绿色按钮点击获取到最后一页，全选并新建一个sql窗口 全部执行
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
8-3、批量生成存储授只查看权语句，在使用OWNER用户执行授权动作（linux终端的SQLPLUS 操作）
!touch /home/oracle/2222.txt
spool /home/oracle/2222.txt
select 'create or replace synonym NAME_B.' || object_name || ' for NAME_A.' || object_name || ';' from all_objects where object_type in ('PROCEDURE', 'FUNCTION','PACKAGE','PACKAGE BODY') and owner = 'NAME_A';
spool off
@/home/oracle/2222.txt;
commit
!rm -f /home/oracle/2222.txt
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
8-4、批量生成存储授只查看权语句，在使用OWNER用户执行授权动作 （plsql工具演示）
select 'create or replace synonym NAME_B.' || object_name || ' for NAME_A.' || object_name || ';' from all_objects where object_type in ('PROCEDURE', 'FUNCTION','PACKAGE','PACKAGE BODY') and owner = 'NAME_A';
--根据显示的结果上边有一个绿色按钮点击获取到最后一页，全选并新建一个sql窗口 全部执行
--请根据上面的步骤依次执行，中途演示的 NAME_A 是OWNER用户 NAME_B 则是需要权限的用户，请根据需求修改信息，用户名称需要大写
∵9、Oracle 授权与权限回收
9-1、查询用户获得的权限或过滤单张表
select tt.*,
(select object_type
from user_objects
where object_name = tt.table_name
and object_type in ('TABLE',
'FUNCTION',
'TYPE',
'SEQUENCE',
'PACKAGE',
'PROCEDURE')) object_type
from (select grantee,
table_name,
listagg(privilege, ',') WITHIN GROUP(order by privilege) privilege_s
from user_tab_privs
where grantee = 'DDMQA' --此处为查询用户获得的权限
AND TABLE_NAME = 'FS_S10_GAME_JAR' --此处为单张表，如需要查询所有则删除此段落
group by grantee, table_name) tt
order by object_type;
--请把上面整个SQL语句复制，并根据语句中的注释修改
9-2、手动授权单张表给用户
GRANT SELECT,INSERT,UPDATE,DELETE ON NAME_A.FS_S10_GAME_JAR TO NAME_B;
--此处列出 增删改查权限，请根据授权需求，自行删减 NAME_A 代表owner用户，NAME_B 表示需要获得权限的用户，请根据实际进行修改
9-3、手动回收用户单张表权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON NAME_A .FS_S12_PUBLIC_ARCADE_POOL FROM NAME_B ;
--此处列出 增删改查权限，请根据授权需求，自行删减 NAME_A 代表owner用户，NAME_B 表示需要获得权限的用户，请根据实际进行修改
9-4、用户缺少登入权限（ORA-0131）请用DBA用户授权，下面请手动更改中文用户替换相关用户
GRANT debug connect session to 用户;
∵10、Oracle 锁表与死锁
10-1、自动查询并生成杀死锁住的进程语句 （请吧查询的结果用DBA用户杀死）
select 'alter system kill session ''' || sess.sid || '' || ',' ||
sess.serial# || ''';',
sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
10-2、查询目前有锁住表的信息
SELECT s.sid,
s.username,
l.OBJECT_ID,
l.SESSION_ID,
s.SERIAL#,
l.ORACLE_USERNAME,
l.OS_USER_NAME,
l.PROCESS
FROM V$LOCKED_OBJECT l, V$SESSION S
WHERE l.SESSION_ID = S.SID;
--如果出现结果请捕捉 第一个sid 参数与第五个SERIAL参数带入下面的语句
alter system kill session '284,38112';
10-3、查询有死锁的信息
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));
10-4、查询用户或者机器造成锁表信息并生成杀死语句
select s.sid,
s.machine,
o.object_name,
l.oracle_username,
l.locked_mode,
'ALTER SYSTEM KILL SESSION ''' || s.sid || ', ' || s.serial# ||
''';' Command
from v$locked_object l, v$session s, all_objects o
where l.session_id = s.sid
and l.object_id = o.object_id
∵11、Oracle 关闭服务或监听与启动
11-1、关闭监听进程
lsnrctl stop --此关闭命令在ORACLE 用户使用
!lsnrctl stop --此关闭命令在SQLPLUS 终端使用
11-2、启动监听进程
lsnrctl start --此启动命令在ORACLE 用户使用
!lsnrctl start --此启动命令在SQLPLUS 终端使用
11-3、进入sqlplus 的DBA管理员模式
su oracle
source /home/oracle/.profileORA
sqlplus / as sysdba
11-4、关闭oracle DB服务
shutdown immediate --日常关闭使用
shutdown abort --此处慎用，尽量不要使用到，强制关闭DB，遇到无法关闭无法解决时使用
11-5、启动oracle DB服务
startup --偷懒模式
startup open --标准启动方式
--如上的所有操作需使用oracle用户执行，切勿使用root用户
11-5、解锁存储过程锁
--查询存储过程被锁
--查V$DB_OBJECT_CACHE
SELECT * FROM V$DB_OBJECT_CACHE WHERE name='存储名' AND LOCKS!='0';
--按对象查出sid的值
select /*+ rule*/ SID from V$ACCESS WHERE object='存储';
--查sid,serial#
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID in ('1160');
--杀掉对应进程即解锁
alter system kill session '1160,41565';
∵12、oracle 表空间sysaux 自增长用满 导致ORA-1688 问题处理
12-1、查询所有表空间信息并查看sysaux 表空间大小
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
T.TOT_TABLESPACE_SIZE_MB "表空间大小(M)",
T.TOT_TABLESPACE_SIZE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round((T.TOT_TABLESPACE_SIZE_MB - F.TOTAL_BYTES) /
T.TOT_TABLESPACE_SIZE_MB * 100,
2),
'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BLOCK "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BLOCK
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT D.TABLESPACE_NAME,
Round(Sum(D.BYTES) / (1024 * 1024), 2) TOT_TABLESPACE_SIZE_MB
FROM SYS.DBA_DATA_FILES D
GROUP BY D.TABLESPACE_NAME) T
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
12-2、查询各个sysaux表空间的使用情况
select *
from (select segment_name, segment_type, bytes / 1024 / 1024
from dba_segments
where tablespace_name = 'SYSAUX'
and bytes / 1024 / 1024 > 1000
order by bytes desc);
12-3、如果有数据的话则使用如何语句生成truncate ，使用dba用户清理
select distinct 'truncate table ' || segment_name || ';',
s.bytes / 1024 / 1024
from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes / 1024 / 1024 > 100
order by s.bytes / 1024 / 1024 / 1024 desc;
∵13、oracle 性能指标信息查询
13-1、查看到指定时间段内数据库的redo信息、逻辑读、物理读、执行次数、解析次数、软硬解析次数、事务数等
select s.snap_date,
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
to_char(round(s.seconds / 60, 2)) "elapse(min)",
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
s.redosize redo,
round(s.redosize / s.seconds, 2) "redo/s",
s.logicalreads logical,
round(s.logicalreads / s.seconds, 2) "logical/s",
physicalreads physical,
round(s.physicalreads / s.seconds, 2) "phy/s",
s.executes execs,
round(s.executes / s.seconds, 2) "execs/s",
s.parse,
round(s.parse / s.seconds, 2) "parse/s",
s.hardparse,
round(s.hardparse / s.seconds, 2) "hardparse/s",
s.transactions trans,
round(s.transactions / s.seconds, 2) "trans/s"
from (select curr_redo - last_redo redosize,
curr_logicalreads - last_logicalreads logicalreads,
curr_physicalreads - last_physicalreads physicalreads,
curr_executes - last_executes executes,
curr_parse - last_parse parse,
curr_hardparse - last_hardparse hardparse,
curr_transactions - last_transactions transactions,
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
to_char(currtime, 'yy/mm/dd') snap_date,
to_char(currtime, 'hh24:mi') currtime,
currsnap_id endsnap_id,
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
from (select a.redo last_redo,
a.logicalreads last_logicalreads,
a.physicalreads last_physicalreads,
a.executes last_executes,
a.parse last_parse,
a.hardparse last_hardparse,
a.transactions last_transactions,
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
b.end_interval_time lasttime,
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
b.startup_time
from (select snap_id,
dbid,
instance_number,
sum(decode(stat_name, 'redo size', value, 0)) redo,
sum(decode(stat_name,
'session logical reads',
value,
0)) logicalreads,
sum(decode(stat_name,
'physical reads',
value,
0)) physicalreads,
sum(decode(stat_name, 'execute count', value, 0)) executes,
sum(decode(stat_name,
'parse count (total)',
value,
0)) parse,
sum(decode(stat_name,
'parse count (hard)',
value,
0)) hardparse,
sum(decode(stat_name,
'user rollbacks',
value,
'user commits',
value,
0)) transactions
from dba_hist_sysstat
where stat_name in
('redo size',
'session logical reads',
'physical reads',
'execute count',
'user rollbacks',
'user commits',
'parse count (hard)',
'parse count (total)')
group by snap_id, dbid, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
order by end_interval_time)) s,
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time') t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc, time desc;
13-2、查询前10个运行时间过久的语句
select a.*, elapsed_seconds / executions elapsed_per
from (select sql_text,
---sql_fulltext,
sql_id,
round(elapsed_time / 1000000, 2) elapsed_seconds,
executions,
buffer_gets,
disk_reads
from (select * from v$sql order by elapsed_time desc)
where rownum <= 100) a
where executions > 0
order by elapsed_per desc;
13-3、查询所有表空间的大小
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
T.TOT_TABLESPACE_SIZE_MB "表空间大小(M)",
T.TOT_TABLESPACE_SIZE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round((T.TOT_TABLESPACE_SIZE_MB - F.TOTAL_BYTES) /
T.TOT_TABLESPACE_SIZE_MB * 100,
2),
'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BLOCK "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BLOCK
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT D.TABLESPACE_NAME,
Round(Sum(D.BYTES) / (1024 * 1024), 2) TOT_TABLESPACE_SIZE_MB
FROM SYS.DBA_DATA_FILES D
GROUP BY D.TABLESPACE_NAME) T
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
oracle查询表空间占用前十的表大小
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = upper('表空间名称') GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;
Resize方式收缩表空间大小
主动收缩表空间占磁盘的大小只能用resize收缩，如果收缩报错ORA-03297，说明Resize的尺寸过小，需要适当调大reisze的值
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;