常用命令
删除备份集（归档日志的备份文件）
delete noprompt backup of archivelog until time='sysdate-8' device type disk;
删除原始归档日志文件
delete noprompt archivelog all completed before 'sysdate-8';
注册归档日志：
把日志拷贝到备库上,4、在备库上手工注册上一步中从主库拷贝来的日志
alter database register logfile '/u01/archivelog/1_99_626106231.arc';
SQL> ALTER SYSTEM SET log_archive_dest_2='LOCATION=/u02/archives' SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/archives' SCOPE=BOTH;
可以设置多个路径，产生多个副本的归档日志
查看字符集：
SELECT NAME, VALUE$ FROM SYS.PROPS$
WHERE NAME LIKE '%CHARACTERSET';
先备份归档，后备份数据+控制文件，极端情况会导致数据不一致。
建议先备份数据+控制文件，再备份归档日志，极端情况需要将归档手动注册到控制文件即可。
通过sql_id查找sql_text
alter session set cursor_sharing=force;
set line 180 pages 9999 long 9999 longc 9999
select /* SHSNC */ sql_fulltext from v$sqlarea where sql_id=to_char('3cu3qm9a2xxaj');
查询PID对应的session
select p.spid as "OS_PID",
se.sid,se.serial#,se.status,se.username,se.machine,se.logon_time from v$process p,v$session se
where p.addr=se.paddr and se.sid='&sid';
total process 数量高：
select schemaname,status,count() from v$session group by status,schemaname order by count() desc;
select count() from v$session;
select count() from v$process;
select inst_id,program,count(*) from gv$process where program like '%P0%' group by inst_id,program;
select inst_id,program,count() from gv$process where program like '%P0%' group by inst_id,program having count() > 1 order by 1,2;
select event,count(*) from v$session where schemaname='SYS' group by evnet;
查看数量最多的等待时间是否异常等待事件，目前除了sql*net message from client 外都需要注意一下
session 状态检查
select status,count(*) from v$session group by status;
CPU使用高排查方法：
col username for a15
col machine for a15
col sql_id for a15
set pagesize 999
select se.sid,se.username,se.machine,sq.cpu_time,sq.sql_text,se.sql_id from v$process p,v$session se,v$sqlarea sq where p.addr=se.paddr and se.sql_hash_value=sq.hash_value and p.spid='&pid';
查询目前正在执行的sql及执行该语句的用户
SELECT b.sid ,
b.username,
b.serial#,
spid ,
paddr,
sql_text ,
b.machine
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
数据库闪回区状态检查与清理
select sum(percent_space_used) from v$flash_recovery_area_usage;
select file_type,percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
紧急情况下需要按照下面命令执行
su - oracle
rman target /
delete force noprompt archivelog like '+FRA/DB_NAME/%' completed before 'sysdate-(30/24/60)';
ora-01578:ORACLE 数据块损坏（文件号12，块号1120751）
ORA-01110：数据文件12：‘D:\APP\ADMINISTRATOR\ORADATA\ORCL\NNC_DATA01.DBF'
select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=12 and
1120751 between block_id AND block_id + blocks - 1;
找到之前的备份
impdp LSTH/lsth dumpfile=BACK20250305.DMP logfile=back20250504impdp.log directory=expdp_dir
table_exists_action=truncate tables=(SM_MSG_USER)
select /*+use_hash(a,b) */
SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);
实例如果动态注册不到动态监听里面，可以修改local_listener
alter system set local_listener='
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=openlab)(PORT=1521)))';
已知列名，查出所有有这个列名的表：
select owner,table_name,column_name from dba_tab_columns where column_name='列名;
批量kill掉inactive的会话：
SELECT SID,
SERIAL#,
INST_ID,
MODULE,
STATUS,
S.PROGRAM,
machine,
terminal,
S.LAST_CALL_ET/60/60,
'alter system disconnect session '''||sid||','||serial#||''' immediate; '
FROM v$session S WHERE S.USERNAME IS NOT NULL
-- AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE','PLSQLDEV.EXE')
AND S.LAST_CALL_ET >= 2 * 60*60
AND S.STATUS = 'INACTIVE'
ORDER BY INST_ID DESC
;
select count(1)，count(distinct 列1||列2) from 表名
在没有开启闪回数据库，undo也过了保留期限（和undo没啥关系，truncate不记录redo和undo）。因为当truncate命令发起之后，oracle实际上兵没有在删除底层数据块上的数据，而是要等到重用的时候才会把这部分回收，于是这给了我们一个能够恢复数据库的机会
1.安装一个包，这个包的下载地址http://www.hellodba.com/reader.php?ID=216&lang=CN
sql>@E:\1ys\FY_Recover_Data.pck
2.创建实验表并truncate
create table test1 as select * from user_objects;
select count() from test1;
truncate table test1;
3.通过脚本恢复数据，恢复的表是test1$$
exec fy_recover_data.recover_truncated_table('SYS','TEST1')
select count() from test1; (发现没有数据）
select count() from test1$$;(有数据）
4.把数据插回原表
alter table test1 nologging;
insert /+ append/ into test1 select * from test1$$;
commit;
alter table test1 logging;
select count() from test1;
注意：紧急救急，只要块没复写，都能恢复。生产环境最好不要使用
禁用笛卡尔积
alter system set '_optimizer_mjc_enabled'=false
查询之前执行过的语句的执行计划（并非上一条语句，而是相隔了一段时间，但没有被shared_pool age out,准确度高）
1.先查询这条语句的sql_id和child_number,后面会用到
set line 200 pagesize
col sql_text for a80
select sql_text,sql_id,child_number from v$sql where sql_text like 'select * from ft.t1 where id=100';
2.使用上一步得到的结果查询执行计划
select * from table(dbms_xplan.display_cursor('afksjfksjk13',0,'Advanced'));
RAC环境数据文件建到本地磁盘问题
select name from v$datafile where file#=41;
alter database datafile 41 offline;
rman target /
copy datafile 41 to '+DATA';
rename(告诉控制文件，数据文件的路径已经改了）
alter database rename file '/u01/app/oracle/product/11.2.0/db1/dbs/nccca01.dbf' to '+DATA/orcl/datafile/nnc_data01.2276.1020362581' （改成rman中copy的时候提示output的名字）
select name,status from v$datafile where file=41;
（状态发现是recover，意思是这个文件的检查点停了，其他文件的检查点还在变，所以不一致了，需要恢复）
recover datafile 41;
alter database datafile 41 online;
select name,status from v$datafile where file#=41;
（状态已经变成online）
临时表空间扩容了还是满
笛卡尔积：要么统计信息有问题，要么两个表关联没有条件
禁用笛卡尔积，执行计划再看需要hash的尺寸明显减少：
alter system set '_optimizer_mjc_enabled'=false;
logmnr恢复dml语句：
注意：通过logmnr恢复dml语句，需要之前项目上提前将额外日志打开才能恢复，否则恢复数据不全
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
SELECT SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK FROM V$DATABASE;
1.查看最近的40个归档日志文件：
select * from (select 'archived-log',COMPLETION_TIME,THREAD#,ARCHIVED,SEQUENCE#, APPLIED,DELETED from v$archived_log order by first_time desc) where rownum < 40;
2.添加归档日志文件：
exec dbms_logmnr.add_logfile(logfilename=>'/oracle/app/bak/ORCL/archivelog/2023_05_19/o1_mf_1_21_l6fzxzjz_.arc',options=>dbms_logmnr.new);
exec dbms_logmnr.add_logfile(logfilename=>'/oracle/app/bak/ORCL/archivelog/2023_05_19/o1_mf_1_22_l6g6ylho_.arc',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oracle/app/bak/ORCL/archivelog/2023_05_19/o1_mf_1_23_l6g80fdg_.arc',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11953_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11954_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11955_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11956_1002165838.dbf',options=>dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile(logfilename=>'/oradata/arch/1_11957_1002165838.dbf',options=>dbms_logmnr.addfile);
3.开始解析：
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
CREATE TABLE huifu9 AS select * from v$logmnr_contents where table_name='TEST11';
4.结束解析：
exec dbms_logmnr.end_logmnr;
SELECT TIMESTAMP,SESSION_INFO,SQL_REDO FROM huifu9 where table_name='TEST11';
SELECT TIMESTAMP,SESSION_INFO,SQL_REDO FROM huifu9 where upper(sql_redo) like '%TEST%';
5.通过sql_undo恢复数据：
通过sql_undo可以恢复之前delete的操作：
select timestamp,session_info,sql_redo,sql_undo from huifu9 where timestamp > to_date('2023-05-19 14:40:00','yyyy-mm-dd hh24:mi:ss');
select timestamp,session_info,sql_redo from jpt where timestamp > to_date('2023-05-19 14:40:00','yyyy-mm-dd hh24:mi:ss');
6.将sql_undo的数据单独导入一张表
create table huifu10 as select sql_undo from huifu9 where timestamp > to_date('2023-05-19 14:40:00','yyyy-mm-dd hh24:mi:ss');
select * from huifu10;
将huifu10表中的insert数据复制粘贴插入到test11被误删除数据的表中即可。
select sql_undo from huifu where table_name='EMP';
create table huifu1 as select sql_undo from huifu where table_name='EMP';
归档模式：
SELECT log_mode FROM v$database;
alter system set log_archive_dest_1=’location=f:\archive_log’ scope=both;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
将新环境所有数据文件删掉，将旧环境的数据文件拷贝过来
pfile中指向控制文件位置即可启动
如何启动报错
https://blog.csdn.net/qq_42382354/article/details/136585418
centos8上安装11g必须要打p33711103这个补丁
trc文件暴增
head -2000 20:00 ora91c_ora_6398.trc|more
ownerid=105 name=YTS_TRANS_20240331
select * from dba_users where user_id=105 --IUAP_APDDC_COREDOC
select a.object_name,a.object_id,a.data_object_id,a.created,a.namespace from dba_objects a where object_name='YTS_TRANS_20240331'; --USTOCK
select * from dba_objects where object_name='YTS_TRANS_20240331' --USTOCK
select * from USTOCK.YTS_TRANS_20240331; 空表
IUAP_APDDC_COREDOC用户访问了USTOCK的表YTS_TRANS_20240331
grep YTS_TRANS_20240331 ora19c_ora_25572.trc|wc -l
select * from dba_users a where a.default_tablespace not in ('SYSTEM','SYSAUX','USERS')
查看这段时间的sql_id，通过sql_id查找具体的sql信息
select distinct(a.sql_id) from v$active_session_history a where session_id=11285 and to_char(a.sample_time,'yyyymmdd hh24:mi:ss')> '20240416 19:30:00' and to_char(a.sample_time,'yyyymmdd hh24:mi:ss')< '20240416 21:30:00'
通过awr报告
dictionary caches stats中：dc_segments 发现pct miss 47.67
Top 10 Foreground Events by Total Wait Time： row cache lock: db time 87%
mos文档号：Resolving Issues Where 'Row Cache Lock' Waits are Occurring (Doc ID 1476670.1)
修改参数：
alter table USTOCK.YTS_TRANS_20240331 move online storage(initial 10m next 10m); --19c 新特性，online可以使索引不失效
select t.owner,t.table_name,t.pct_free,t.ini_trans,t.initial_extent,t.next_extent from dba_tables t where t.table_name='YTS_TRANS_20240331';
alter system set shared_pool_cache=5g;
alter system set open_cursors=2000;
alter system set sessjion_cached_cursor=500 scope=spfile; 需要重启生效
open_cursors --每个session（会话）最多能同时打开多少个cursor
session_cached_cursor --每个session（会话）最多可以缓存多少个关闭的cursor
解决方法：1.对已创建的表修改存储参数：alter table USTOCK.YTS_TRANS_20240331 move online storage(initial 10m next 10m); --19c 新特性，online可以使索引不失效
2.对未来创建的表 关闭数据库延迟段参数
alter system set deferred_segment_creation=false;
windows自动备份脚本
rem ---------------------------------------------------------------------------
rem windows环境下Oracle数据库的自动备份脚本。
rem 可以将本批处理设成windows任务计划下自动执行。
@echo off
@echo ================================================
@echo windows环境下Oracle数据库的自动备份脚本
@echo 说明：启动备份时，需要配置以下变量
@echo 1、BACKUP_DIR 指定要备份到哪个目录
@echo 2、ORACLE_USERNAME 指定备份所用的Oracle用户名
@echo 3、ORACLE_PASSWORD 指定备份所用的Oracle密码
@echo 4、ORACLE_DB 指定备份所用的Oracle实例名
@echo 5、BACK_OPTION 备份选项，可以为空，可以为full=y，可以为owner=a用户,b用户 等等....
@echo 6、RAR_CMD 指定RAR命令行压缩工具所在目录
@echo ================================================
rem 以下变量需要根据实际情况配置------> 注意：此脚本必须放在D:\NCback目录下
set BACKUP_DIR=E:\dump
set ORACLE_USERNAME=SYHBNCC
set ORACLE_PASSWORD=syhb2020
set ORACLE_DB=orcl
set BACK_OPTION= directory=datadump
set RAR_CMD="C:\oral\winrar\Rar.exe"
set Today=%DATE%
set Today=%Today:0,4%%Today:5,2%%Today:~8,2%
set BACK_NAME=%ORACLE_USERNAME%_%Today%
set BACK_FULL_NAME=%BACK_NAME%
rem 开始备份
rem "exp %ORACLE_USERNAME%/%ORACLE_PASSWORD%@%ORACLE_DB% %BACK_OPTION% file=%BACK_FULL_NAME%.dmp log=%BACK_FULL_NAME%exp.log"
expdp %ORACLE_USERNAME%/%ORACLE_PASSWORD%@%ORACLE_DB% %BACK_OPTION% schemas=%ORACLE_USERNAME% dumpfile=sy%BACK_NAME%.dmp logfile=sy%BACK_NAME%.log
rem 压缩并删除原有文件
e:
cd E:\dump
%RAR_CMD% a -df "%BACK_FULL_NAME%.rar" "SY%BACK_NAME%.DMP" "SY%BACK_NAME%exp.log"
rem ---------------------------------------------------------------------------
rem 指定待删除文件的存放路径
set SrcDir=E:\dump
rem 指定天数
set DaysAgo=15
forfiles /p %SrcDir% /s /m *.rar /d -%DaysAgo% /c "cmd /c del /f /q /a @path"
pause
ECHO OFF
set y=%date:0,4%%date:5,2%%date:~8,2%
expdp ygz633jg/ygz633jg directory=nc63file dumpfile=jg%y%.dmp logfile=jg%y%.log schemas=ygz633jg network_link=mylink
exp ygz633jg/ygz633jg@orcl41 file=D:\ncdatabak\jg%y%.dmp log=D:\ncdatabak\jg%y%.log owner=ygz633jg
cd /d D:\ncdatabak
rar a %y%.rar jg%y%.dmp jg%y%.log
del jg%y%.dmp
del jg%y%.log
forfiles /p %backuppath% /m *.rar -d -7 /c "cmd /c del /f @path
exits
rman清理脚本
！/bin/bash
ORACLE_BASE=/data/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/19.3/db_home; export ORACLE_HOME
ORACLE_SID=ora19c; export ORACLE_SID
source /home/oracle/.bash_profile
BACK_DIR=/home/oracle/scripts
DATE=date +%F%R
RMAN_LOG=$BACK_DIR'/log/'$DATE'rman_del.log'
rman log=$RMAN_LOG target / <<EOF
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-7';
delete noprompt expired archivelog all;
delete noprompt obsolete;
exit;
EOF
systemctl start crond
vi /var/log/cron
vi /var/log/message
时间同步脚本
!/bin/bash
if [ $# -eq 1 ]; then
echo "使用的时间服务器为:$1"
date_string=$(ssh $1 "date '+%Y-%m-%d %H:%M:%S'")
echo "同步的时间为${date_string}"
sudo date -s "$date_string"
sudo hwclock --systohc
echo "时间同步完成"
else
echo "请输入需要同步时间的服务器ip"
fi
./1.sh 192.168.1.2 需要同步哪一台服务器的时间就输入哪个ip地址即可
date -s 21:21:55
ORA-04031
SELECT value/1024/1024 AS size_mb
FROM v$parameter
WHERE name = 'sga_target';
select sum(bytes)/1024/1024 mb from v$sgastat where pool='shared pool';
alter system set sga_max_size=12G scope=spfile;
alter system set sga_target=12G scope=spfile;
查看10天内数据增长在1G以上的对象:
SET LINES 200 PAGES 200
COL OWNER FOR A10
WITH T1 AS
(SELECT TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD') SNAPDATE,
A.TS#,
A.OBJ#,
TRUNC(SUM(A.SPACE_ALLOCATED_DELTA) / 1024 / 1024) DELTA_MB
FROM DBA_HIST_SEG_STAT A, DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND B.SNAP_ID >
(SELECT MIN(SNAP_ID)
FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > SYSDATE - &DAYS)
--AND A.SPACE_ALLOCATED_DELTA > 1024 * 1024 * 10 -- LIMIT SIZE MB
GROUP BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'), A.TS#, A.OBJ#
ORDER BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'))
SELECT A.SNAPDATE,
A.TS#,
--A.OBJ#,
B.OBJECT_NAME,
B.OWNER,
SUM(A.DELTA_MB) DELTA_MB
FROM T1 A, DBA_HIST_SEG_STAT_OBJ B
WHERE A.OBJ# = B.OBJ#
AND A.TS# =
(SELECT TS# FROM V$TABLESPACE WHERE NAME = UPPER('&TABLESPACE_NAME'))
GROUP BY A.SNAPDATE,
A.TS#,
--A.OBJ#,
B.OBJECT_NAME,
B.OWNER
HAVING SUM (A.DELTA_MB) >= &SUMDELTASIZE
ORDER BY A.SNAPDATE;
lob字段查看
select * from
(select segment_name,segment_type,round(sum(bytes)/1024/1025) object_size
from dba_segments where owner='NC65'
group by segment_name,segment_type
order by object_size desc)
where segment_type in ('TABLE','INDEX') and rownum <10;
select owner,segment_name,segment_type,bytes/1024/1024/1024 from dba_segments order by 4 desc;
查看大字段segment_name对应的表名及对应的列名：
select * from dba_lobs where segment_name='SYS_LOB0000117378C00006$$';
表太多了，查看表的情况:
select substr(object_name, 0,4), count() from user_objects where object_type = 'TABLE' group by substr(object_name, 0,4) order by count() desc;
select count(0) from user_tables where table_name like 'FYDW%';
select count(0) from user_tables where table_name like 'DWBM%'
select count(0) from user_tables where table_name like 'CODE%';
FYDW临时表
--创建存储过程删除7天前创建的FYDW临时表
（正常表3000多个，
先切换到用户下面再查
select count() from user_tables;
select count() from user_objects a,user_tables b where a.OBJECT_NAME=b.TABLE_NAME and b.TEMPORARY='Y' and created < sysdate - 7;
select count(*) from user_objects where object_type = 'TABLE' and temporary ='Y' and created <sysdate - 7'😉
select * from user_objects where object_type = 'TABLE' and temporary ='Y' and created <sysdate - 7'😉
create or replace procedure p_drop_fydw as
v_tab_name varchar(30);
cursor cur_drop_temq is
select object_name
from user_objects
where object_type = 'TABLE'
and object_name like 'FYDW%' and temporary = 'Y'
and created < sysdate - 7;
begin
open cur_drop_temq;
loop
fetch cur_drop_temq
into v_tab_name;
exit when cur_drop_temq%notfound;
execute immediate 'drop table ' || v_tab_name || ' purge';
end loop;
close cur_drop_temq;
end;
/
create or replace procedure p_drop_fydw as
v_tab_name varchar(30);
cursor cur_drop_temq is
select object_name
from user_objects
where object_type = 'TABLE'
and object_name like 'TEMQ_%' and temporary = 'Y'
and created < sysdate - 7;
begin
open cur_drop_temq;
loop
fetch cur_drop_temq
into v_tab_name;
exit when cur_drop_temq%notfound;
execute immediate 'drop table ' || v_tab_name || ' purge';
end loop;
close cur_drop_temq;
end;
/
exec STJT.p_drop_fydw
iostat -d -x -m 1 10000 看rMB/s 数据库一般达到1G/s
--每月21号凌晨2点执行存储过程
VARIABLE JOBNO NUMBER;
VARIABLE INSTNO NUMBER;
BEGIN
SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
DBMS_JOB.SUBMIT(:JOBNO,'p_drop_fydw;
',TRUNC(LAST_DAY(SYSDATE))+21+2/24,'TRUNC(LAST_DAY(SYSDATE))+21+2/24',TRUE,:INSTNO);
COMMIT;
END;
/
pdb需要进入容器里面创建目录：
show pdbs
alter session set container=orclpdb;
expdp NCC10/NCC10@172.20.17.29:1521/orclpdb schemas=NCC10 DIRECTORY=ncdb DUMPFILE=NCC1020240318.dmp LOGFILE=NCC1020240318.log
D:>expdp nc633/nc633 schemas=nc633 directory=CTSW dumpfile=nc6330408.dmp logfil
e=nc6330408.log exclude=table:"in('SM_FILESTOREVIEW')"
impdp导入：
create directory qyldir as '/oracle/expdata1';
grant read on directory qyldir to system;
grant write on directory qyldir to system;
impdp nnc/nnc directory=mydir dumpfile=biphnst2022_20231109021001.dmp logfile=20231116_impdp.log remap_schema=biphnst2022:nnc
create tablespace nnc_data01 datafile 'E:\data\nnc_data01.dbf' size 30G autoextend on next 500m extent management local uniform size 1m;
alter tablespace nnc_data01 add datafile 'E:\data\nnc_data02.dbf' size 10g autoextend on next 500m maxsize unlimited;
alter tablespace nnc_data01 add datafile 'E:\data\nnc_data03.dbf' size 10g autoextend on next 500m maxsize unlimited;
create tablespace nnc_index01 datafile 'E:\data\nnc_index01.dbf' size 5g autoextend on next 500m extent management local uniform size 128k;
create user nnc identified by nnc default tablespace nnc_data01 temporary tablespace temp;
grant dba,connect,resource to nnc;
alter tablespace SYSTEM add datafile '+DATA/ORCL/DATAFILE/system001.dbf' size 1G AUTOEXTEND ON;
oracle怎么删除directory 语法如下：drop directory dirname;
创建或者修改directory目录：create or replace directory dum_date_dir as '/home/oracle/datatmp';
ora-01940删除当前连接的用户
select saddr,sid,serial#,paddr,username,status from v$session where username = '用户名';
alter system kill session 'sid,serial#';
drop user 用户名 cascade;
查看所有的schema所占空间大小：
select owner,sum(bytes)/1024/1024/1024 schema_size_gig from sys.dba_segments group by owner;
查看是否有中断的expdp job:
select * from dba_datapump_jobs where state = 'NOT RUNNING' and owner_name='NC65';
select * from dba_datapump_jobs where state = 'RUNNING' and owner_name='NC65';
select a.sid,a.event,a.blocking_session,a.wait_class from v$session a where a.module like 'Data Pump%';
查看10天内数据增长在1G以上的对象:
SET LINES 200 PAGES 200
COL OWNER FOR A10
WITH T1 AS
(SELECT TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD') SNAPDATE,
A.TS#,
A.OBJ#,
TRUNC(SUM(A.SPACE_ALLOCATED_DELTA) / 1024 / 1024) DELTA_MB
FROM DBA_HIST_SEG_STAT A, DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
AND B.SNAP_ID >
(SELECT MIN(SNAP_ID)
FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > SYSDATE - &DAYS)
--AND A.SPACE_ALLOCATED_DELTA > 1024 * 1024 * 10 -- LIMIT SIZE MB
GROUP BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'), A.TS#, A.OBJ#
ORDER BY TO_CHAR(B.BEGIN_INTERVAL_TIME, 'YYYYMMDD'))
SELECT A.SNAPDATE,
A.TS#,
--A.OBJ#,
B.OBJECT_NAME,
B.OWNER,
SUM(A.DELTA_MB) DELTA_MB
FROM T1 A, DBA_HIST_SEG_STAT_OBJ B
WHERE A.OBJ# = B.OBJ#
AND A.TS# =
(SELECT TS# FROM V$TABLESPACE WHERE NAME = UPPER('&TABLESPACE_NAME'))
GROUP BY A.SNAPDATE,
A.TS#,
--A.OBJ#,
B.OBJECT_NAME,
B.OWNER
HAVING SUM (A.DELTA_MB) >= &SUMDELTASIZE
ORDER BY A.SNAPDATE;
select * from dba_lobs where SEGMENT_NAME=’SYS_LOB0000963443C00003$$’;
oracle根据pid找到对应的sid和serial#
select sid,serial#,program,machine,client_identifier from v$session where paddr=(select addr from v$process where spid in ('16140','16004'));
select tablespace_name,file_name,
bytes/1024/1024 已使用,
maxbytes/1024/1024 最大,
to_char(bytes/maxbytes100,'990.99')||'%' 使用率
from dba_data_files
where tablespace_name like 'NNC%'
order by tablespace_name,file_name,to_char(bytes/maxbytes100,'990.99')||'%' desc;
查看表空间
col used_pct for a20
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct
from (
select tablespace_name, sum(bytes) /1024/1024 as MB
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name
order by used_pct desc;
临时表空间：
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1;
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb
FROM dba_temp_files;
ALTER TABLESPACE temp
ADD TEMPFILE '/path/to/new/tempfile.dbf' SIZE 1024M
AUTOEXTEND ON
NEXT 1024M
MAXSIZE 8192M;
临时表空间老是满，查看是什么会话和sql占用了：
SELECT vt.inst_id,
vs.sid,
vs.serial#,
vs.username,
vs.osuser,
vs.machine,
-- vs.saddr,
vs.client_info,
vs.program,
-- vs.module,
vs.logon_time,
vt.tempseg_usage,
vt.segtype
FROM gv$session vs,
(SELECT inst_id,
username,
session_addr,
segtype,
ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage
FROM gv$tempseg_usage
GROUP BY inst_id, username, session_addr, segtype
ORDER BY 4 DESC) vt
WHERE vs.inst_id = vt.inst_id
AND vs.saddr = vt.session_addr
order by tempseg_usage desc;
收集统计信息：
BEGIN
dbms_stats.gather_table_stats(ownname => 'nc65',
tabname => 'fip_relation',
partname => '',
block_sample => FALSE,
granularity => 'ALL',
cascade => TRUE);
END;
检查锁问题
oracle 查到锁的源头 blocking_session为源头,
select last_call_et,v.event,
s.sql_id,
--- s.SQL_FULLTEXT,
s.SQL_TEXT,
v.inst_id,
V.SID,
V.CLIENT_IDENTIFIER,
v.blocking_session,
v.blocking_session_status,
'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;',
v.USERNAME,
s.CPU_TIME,
s.ELAPSED_TIME,
v.PROGRAM,
'kill -9 ' || p.spid,
v.CLIENT_INFO,
v.SQL_HASH_VALUE,
v.SQL_ADDRESS,
v.MACHINE,
v.TERMINAL, s.DISK_READS,s.BUFFER_GETS,s.SORTS,s.SHARABLE_MEM,s.PERSISTENT_MEM,s.RUNTIME_MEM,s.ROWS_PROCESSED
from gv$session v, gv$process p, gv$sql s
where v.last_call_et > 0
and v.status = 'ACTIVE'
and v.username != 'SYS'
and p.addr = v.paddr
and s.ADDRESS = v.SQL_ADDRESS
and s.HASH_VALUE = v.SQL_HASH_VALUE
order by last_call_et desc;
杀掉源头blocking_session
select CLIENT_IDENTIFIER,v.inst_id,v.status,'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;', v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL from gv$session v where sid='XXX';
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE B.OBJECT_ID = A.OBJECT_ID;
nc用的是AL32UTF8：SIMPLIFIED CHINESE_CHINA.AL32UTF8
select * from dba_users where username=upper('用户名‘）；
select * from all_tab_columns c where c.OWNER=upper('数据库用户名’）；
select * from all_tables where owner = 'pfcs';
日志切换频率：
##################################
查询每小时的归档日志生成量
##################################
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-2)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
性能优化：回表查询
1.什么样的SQL必须要回表？
select * from table where ...
这样的SQL就必须回表，所以我们必须严禁使用select * 。那什么样的SQL不需要回表？
select count(*) from table
这样的SQL就不需要回表。
当要查询的列也包含在索引中，这个时候就不需要回表中了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。
当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID 前面有“*”） 也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。
186 consistent gets 逻辑读
2.如果一个表在数据库里面执行时间特别快，在前端执行很慢，应该是有临时表导致的：代码执行过程中，临时表应该会有数据，你直接到数据库执行，临时表数据和实际程序执行不一样
泓博老师之前说过，把日志级别调到debug，执行一遍操作，从日志里把插到临时表的sql整理出来，手动插到一张临时表里，然后再看真实的执行速度执行计划、
远程连接语句
sqlplus sys/oracle@192.168.16.1:1521/orcl as sysdba
sqlplus scott/oracle@192.168.16.1:1521/orcl
查看执行计划：
explain plan for
select JZ,pk_poundbill,companyname,vvehicle,dgrosstime,materialname,nnet,vbillcode from v_itf_zcinfo_ajj where companyname='山东金岭化工股份有限公司'；
select * from table(dbms_xplan.display)
select * from user_ind_columns where table_name = upper('gl_voucher');
create index I_LEVM_POUNDBILL_001 ON LEVM_POUNDBILL(NNET,TS,VDEF18,NBILLSTATUS) tablespace NNC_INDEX01 NOLOGGING;
dba_tables里的num_rows, last_analyzed列
然后再select count(*)一下实际行数，看和num_rows相差大不
select num_rows,last_analyzed from dba_tables where table_name='FA_CARDHISOTYR';
show parameter optimizer_index_cost_adj
alter session set optimizer_index_cost_adj=
例子： 获取表的ddl语句，表名为EXAMPLE
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EXAMPLE') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'NCDATA01') FROM DUAL;
查看临时表空间使用率：
select
a.tablespace_name tablespace,
d.mb_total,
sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_used,
d.mb_total - sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_free
from
v$sort_segment a,
(
select
b.name,
c.block_size,
sum (c.bytes) / 1024 / 1024 mb_total
from
v$tablespace b,
v$tempfile c
where
b.ts#= c.ts#
group by
b.name,
c.block_size
) d
where
a.tablespace_name = d.name
group by
a.tablespace_name, d.mb_total;
SELECT a.tablespace_name "表空间名称",
total / (1024 * 1024) "表空间大小(M)",
free / (1024 * 1024) "表空间剩余大小(M)",
(total - free) / (1024 * 1024 ) "表空间使用大小(M)",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
select
blocksession.sid as block_session_sid,
blocksession.serial# as block_session_serial#,
blocksession.username as block_session_username,
blocksession.osuser as block_session_osuser,
blocksession.machine as block_session_machine,
blocksession.status as block_session_status,
blockobject.object_name as blocked_table,
waitsession.sid as wait_session_sid,
waitsession.serial# as wait_session_serial#,
waitsession.username as wait_session_username,
waitsession.osuser as wait_session_osuser,
waitsession.machine as wait_session_machine,
waitsession.status as wait_session_status
from
v$lock blocklock,
v$lock waitlock,
v$session blocksession,
v$session waitsession,
v$locked_object lockedobject,
dba_objects blockobject
where
blocklock.block = 1
and blocklock.sid != waitlock.sid
and blocklock.id1 = waitlock.id1
and blocklock.id2 = waitlock.id2
and blocklock.sid = blocksession.sid
and waitlock.sid = waitsession.sid
and lockedobject.session_id = blocksession.sid
and lockedobject.object_id = blockobject.object_id;
查看集群各资源状态
crsctl stat res -t
停止现有scan监听
srvctl stop scan_listener
移除集群中scan监听
srvctl remove scan_listener
添加新的scan监听
srvctl add scan_listener -l LISTENER
开启scan监听
srvctl start scan_listener
数据库实例注册到scan监听
alter system set remote_listener=ht-scan:1521;
查看scan监听及状态
[+ASM1]@ht01[/home/grid]$srvctl status scan_listener
查看scan
[+ASM1]@ht01[/home/grid]$srvctl config scan
关闭节点集群
/u01/app/grid/bin/crsctl stop crs -f
ps -ef|grep css
ps -ef|grep crs
ps -ef|grep evm
ocssd进程：是clusterware最关键的进程，如果出现异常会导致系统重启，这个进程提供CSS（Cluster Synchroniztion Service）服务，它通过多种心跳机制实时监控集群的健康状态，提供集群的基础服务功能。
crsd进程：是实现高可用的主要进程，它提供了CRS（Cluster Ready Service）服务。这些服务包括Clusterware
上集群资源的关闭、重启、转移、监控等。集群资源分为两类：一类是Nodeapps型的，就是说每个节点只需要一个就行，这类有GSD(Global Service Daemon)、ONS（Oracle Notification Service Daemon)、VIP、Listener;另一类是Database-Related,就是和数据库相关，不受节点限制，这类有Database、Instance、Service等。
evmd进程：该进程负责发布CRS产生的各种事件，同事也是CRSD和CSSD两个进程直接的桥梁。
create tablespace oa_uat datafile '/data/oradata/DVOADB/oa_uat01.dbf' size 5g autoextend on;
grant connect,resource,unlimited tablespace to ekp;
alter profile default limit password_life_time unlimited;
alter system set processes=3000 scope=spfile;
alter system set open_cursors=3000 scope=both;
查看sql语句的历史执行计划
查看SQL语句对应的SQL ID:
SELECT sql_id, sql_text
FROM v$sql
WHERE LOWER(sql_text) LIKE '%select * from employees%';
查看SQL的具体执行计划变更版本历史:
select distinct SQL_ID,
PLAN_HASH_VALUE,
to_char(TIMESTAMP, 'yyyy-mm-dd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID = '68wnxdjxwwn2h'
order by TIMESTAMP;
查看SQL执行计划变化的具体详情:
col options for a15
col operation for a20
col object_name for a20
select plan_hash_value,
id,
operation,
options,
object_name,
depth,
cost,
to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss')
from DBA_HIST_SQL_PLAN
where sql_id = '68wnxdjxwwn2h'
and plan_hash_value in (1542630049, 2754593971, 2620382595)
order by TIMESTAMP,ID;
备份控制文件：
alter database backup controlfile to trace as '/home/oracle/1.ctl';
表空间管理：
select tablespace_name,file_name,
bytes/1024/1024 已使用,
maxbytes/1024/1024 最大,
to_char(bytes/maxbytes100,'990.99')||'%' 使用率
from dba_data_files
where tablespace_name like 'NNC%'
order by tablespace_name,file_name,to_char(bytes/maxbytes100,'990.99')||'%' desc;
查看表空间
col used_pct for a20
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct
from (
select tablespace_name, sum(bytes) /1024/1024 as MB
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name
order by used_pct desc;
临时表空间：
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1;
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb
FROM dba_temp_files;
ALTER TABLESPACE temp
ADD TEMPFILE '/path/to/new/tempfile.dbf' SIZE 1024M
AUTOEXTEND ON
NEXT 1024M
MAXSIZE 8192M;
临时表空间老是满，查看是什么会话和sql占用了：
SELECT vt.inst_id,
vs.sid,
vs.serial#,
vs.username,
vs.osuser,
vs.machine,
-- vs.saddr,
vs.client_info,
vs.program,
-- vs.module,
vs.logon_time,
vt.tempseg_usage,
vt.segtype
FROM gv$session vs,
(SELECT inst_id,
username,
session_addr,
segtype,
ROUND(SUM(blocks) * 8192 / 1024 / 1024 / 1024, 2) tempseg_usage
FROM gv$tempseg_usage
GROUP BY inst_id, username, session_addr, segtype
ORDER BY 4 DESC) vt
WHERE vs.inst_id = vt.inst_id
AND vs.saddr = vt.session_addr
order by tempseg_usage desc;
asm磁盘如果往磁盘组里面扩容会对系统有一定影响：需要rebalance
ALTER DISKGROUP DATA ADD DISK '/path/to/new_disk' REBALANCE POWER 8;
POWER_LIMIT = 1：低速运行，对系统影响最小。
soft nproc达到上限后会影响数据库创建新的会话
查看表的ddl创建语句：
select dbms_metadata.get_ddl('TABLE','PUB_OID','JSZYHR') from dual;
查看坏块：
select * from v$database_block_corruption;
查看表的rowid：
select rowid from jszyhr.pub_oid where rownum<5;
rename table:
alter table pub_oid rename to pub_oid_broken;
drop table:
drop table pub_oid_broken purge;
查看数据库运行时间：
SELECT
startup_time,
ROUND((SYSDATE - startup_time) * 24, 2) AS hours_running,
ROUND((SYSDATE - startup_time), 2) AS days_running
FROM v$instance;
浙公网安备 33010602011771号