---查看引用的外键
Select B.Table_Name, B.Column_Name
From Dba_Constraints a,
Dba_Cons_Columns b
Where A.Constraint_Name = B.Constraint_Name
And A.R_Constraint_Name = :constraint_name;
https://www.cndba.cn/asker/article/320https://www.cndba.cn/asker/article/320
-- 查看锁住的表并杀死session
Select S.Sid,
S.Serial#,
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 ;
--根据pid查看sql
select sql_text,spid,v$session.program,process,v$sqlarea.ADDRESS,v$session.sid,v$session.SERIAL#,v$sqlarea.HASH_VALUE,v$process.* from
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (23345);
--查看占用temp情况
SELECT S.sid || ',' || S.serial# sid_serial,
S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
T.sqladdr address,
Q.hash_value,
Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address(+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
--查看数据库中对象的大小
select segment_name,
segment_type,
tablespace_name,
sum(bytes) / 1024 / 1024
FROM dba_segments
/*where tablespace_name=''
*/
group by segment_name, segment_type, tablespace_name
order by 4 desc;
--查看表空间使用情况
Select D.Tablespace_Name,
Space "SUM_SPACE(M)",
Space - Nvl(Free_Space, 0) "USED_SPACE(M)",
Round((1 - Nvl(Free_Space, 0) / Space) * 100, 2) "USED_RATE(%)",
Free_Space "FREE_SPACE(M)",
Blocks Used_Blocks
From (Select Tablespace_Name,
Round(Sum(Bytes) / (1024 * 1024), 2) Space,
Sum(Blocks) Blocks
From Dba_Data_Files
Group By Tablespace_Name) d,
(Select Tablespace_Name,
Round(Sum(Bytes) / (1024 * 1024), 2) Free_Space
From Dba_Free_Space
Group By Tablespace_Name) f
Where D.Tablespace_Name = F.Tablespace_Name(+)
Order By D.Tablespace_Name;
--修改表空间的数据文件的大小
alter Database datafile '全路径的数据文件名称' resize 2048M
Alter Tablespace 表空间名称 Add Datafile '全路径的数据文件名称' Size 2048M
--停止一个定时任务
SQL> exec dbms_job.broken(job_id,true)
--启用一个停止的job
SQL> exec dbms_job.broken(job_id,false)
--数据库密码文件
DBA连接数据库的方式有以下几种:
SQLPLUS / AS SYSDBA :这是典型的操作系统认证,不需要listener进程;
SQLPLUS SYS/PASSWORD AS SYSDBA :此种方法只能连接本地数据库,这里我认为也属于操作系统认证,当password不对时,用户也能连接数据库,不需要listener进程
SQLPLUS [email protected]_SID AS SYSDBA :此种方法可访问本地数据库,同时亦可远程访问数据库,属于典型的密码认证
--连入sga
sqlplus -prelim / as sysdba
从Oracle10g开始,sqlplus提供了一个参数选项-prelim,用这个参数,在系统已经hang的时候。我们可以连接到SGA而不是数据库,也就是说没有session被创建。
rman>crosscheck archivelog all;
rman>delete expired archivelog;
--删除过期备份
https://www.cndba.cn/asker/article/320
RMAN> report obsolete;
RMAN> delete obsolete;
--杀死外部链接进程
ps -ef |grep oracle |grep LOCAL=NO |grep -v grep |awk '{print $2}' |xargs kill -9
rac维护管理指令
--查看asm磁盘使用情况
select name,total_mb,free_mb from v$asm_diskgroup;
1、查看状态的命令
$ crs_stat -t /crsctl stat res -t --查看crs及所有的service的状态
$ crsctl check crs --用crsctl命令,检查crs相关服务的状态
$ srvctl status nodeapps -n db01 (db02) --使用srvctl查看所有节点上的服务是否正常
$ srvctl status database -d db --使用srvctl查看数据库状态
https://www.cndba.cn/asker/article/320
https://www.cndba.cn/asker/article/320
2、启动和关闭相关服务的命令
$ crs_start–all --启动所有的crs服务
$ crs_stop–all --停止所有的crs服务
$ crsctl start crs --启动crs服务
$ crsctl stop crs --停止crs服务
$ srvctl start nodeapps -n db01(db02) --启动某节点上所有的应用
$ srvctl stop nodeapps -n db01(db02) --停止某节点上所有的应用
$ srvctl start listener -n db01(db02) --启动某个节点的listener
$ srvctl stop listener -n db01(db02) --停止某个节点的listener
$ srvctl start instance–d db–i db01(db02) --启动某个instance
$ srvctl stop instance–d db–i db01(db02) --停止某个instance
$ srvctl start database–d db --启动数据库
$ srvctl stop database–d db --停止数据库
https://www.cndba.cn/asker/article/320
dg部分
启动复制
Alter database recover managed standby database disconnect from session;
关闭复制
https://www.cndba.cn/asker/article/320
Alter database recover managed standby database cancel;
查看一个大事物的完成度
set linesize 400;
set pagesize 400;
col sql_text format a100;
col opname format a15;
SELECT se.sid,
opname,
TRUNC (sofar / totalwork * 100, 2) pct_work,
elapsed_seconds elapsed,
ROUND (elapsed_seconds * (totalwork - sofar) / sofar) remain_time,
sql_text
FROM v$session_longops sl, v$sqlarea sa, v$session se
WHERE sl.sql_hash_value = sa.hash_value
AND sl.sid = se.sid
AND sofar != totalwork
ORDER BY start_time;
#v$session_longops可用于查看历史执行缓慢sql
查询当前占用cpu和时间最长的sql
SELECT A.USERNAME,
A.COMMAND,
A.STATUS,
A.PROGRAM,
A.MACHINE,
A.CLIENT_IDENTIFIER,
B.SQL_TEXT,
B.CPU_TIME,
B.SHARABLE_MEM,
ROUND((B.DISK_READS + B.BUFFER_GETS) / B.EXECUTIONS) AS RESOURCE_COST
FROM gV$SESSION A, gV$SQLSTATS B
WHERE A.SQL_ID = B.SQL_ID
AND B.EXECUTIONS > 0
AND A.WAIT_CLASS <> 'Idle'
ORDER BY RESOURCE_COST DESC;
版权声明:本文为博主原创文章,未经博主允许不得转载。
dba oracle指令 rac指令