oracle备份数据核对,oracle 之数据库核查

clear columns breaks

set feed off wrap off verify off head off trim off

set pagesize 20

set linesize 100

--set newpage 0

set term off

set heading off

set TTITLE  ON

column dat1 new_value filename;

select to_char(sysdate,'yyyy-mm-dd hh24') dat1 from dual;

spool c:&&filename..txt

prompt *************************************************

prompt **************数据库相关主要核查信息*********************

prompt *************************************************

select  '实例名称:' || name from v$database;

select '历史最高会话数：' || sessions_highwater from v$license;

select '当前连接数:' || count(*)  from v$process;

select '当前进程数:' || value  from v$parameter where name = 'processes';

Select '并发连接数:' || count(*)  from v$session where status = 'ACTIVE';

select '版本信息:' || BANNER  from v$version where rownum<2;

set heading on

column username justify left format a14;

column 用户名 justify left format a14;

column count(username)  format a8;

column 用户连接的最大数 format a8;

select username 用户名, to_char(count(username)) 用户连接的最大数

from v$session

where username is not null

group by username;

column destination format a32;

column 存储路径 format a32;

select status 状态,destination 存储路径 from v$archive_dest;

column username format a16;

column 用户 format a16;

column tablespace_name format a16;

column 表空间名称 format a16;

col to_char(max_bytes / 1024 / 1024 || 'M') format a6;

column 限额 format a6;

select username 用户,

tablespace_name 表空间名称,

case max_bytes

when -1 then

'-1'

else

to_char(max_bytes / 1024 / 1024 || 'M')

end as "限额"

from dba_ts_quotas

order by username;

col username format a6;

select * from v$pwfile_users;

column b  justify right format a6;

col ff.s format a12;

col 表空间名称 format a12;

col ff.b format a8;

col 分配空间 format a12;

col (ff.b - fr.b) format a16;

col 已使用空间 format a16;

col fr.b format a12;

col 空闲空间 format a12;

col round((ff.b - fr.b) / ff.b * 100) || '% ' format a8;

col 已使用百分比 format a8;

select ff.s 表空间名称,

to_char(ff.b) 分配空间,

to_char((ff.b - fr.b)) 已使用空间,

to_char(fr.b) 空闲空间,

to_char(round((ff.b - fr.b) / ff.b * 100) || '% ') 已使用百分比

from (select tablespace_name s, sum(bytes) / 1024 / 1024 b

from dba_data_files

group by tablespace_name) ff,

(select tablespace_name s, sum(bytes) / 1024 / 1024 b

from dba_free_space

group by tablespace_name) fr

where ff.s = fr.s;

column file_name format a36;

column 文件存储路径 format a36;

column tablespace_name format a18;

column 表空间名称 format a18;

select file_name 文件存储路径, tablespace_name 表空间名称

from dba_data_files;

column username format a18;

column 用户名称 format a18;

column decode(lock_date, null, 'unlocked', 'locked') format a8;

column 锁定状态  format a8;

select username 用户名称,

decode(lock_date, null, 'unlocked', 'locked') 锁定状态

from dba_users;

column increment_by justify left format a12;

column tablespace_name format a18;

column 表空间名称 format a18;

column autoextensible format a6;

column increment_by format a8;

select tablespace_name 表空间名称, autoextensible, to_char(increment_by)

from dba_data_files;

column grantee format a8;

column 用户 format a8;

column granted_role format a6;

column 权限类型 format a6;

select grantee 用户, granted_role 权限类型

from dba_role_privs

where granted_role = 'DBA';

prompt 以下是检查是否有失效的索引分析数据：

select index_name "索引",

owner  "属主",

table_name  "表名",

tablespace_name  "表空间"

from dba_indexes

where owner not in ('SYS','SYSTEM')

and status != 'VALID'

order by owner;

prompt 以下检查是否有失效的触发器、视图、存储过程、函数的信息：

select object_name  "对象",

object_type  "类型",

owner        "属主",

status       "状态"

from dba_objects

where status !='VALID'

and owner not in ('SYS','SYSTEM')

and object_type in   ('TRIGGER','VIEW','PROCEDURE','FUNCTION')

order by owner,object_type;

prompt 检查是否存在运行失败的JOB的信息：

select job,

log_user "属主",

to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "上次执行时间",

to_char(next_date,'yyyy-mm-dd hh24:mi:ss') "下次执行时间",

failures  "失败次数",

what  "内容"

from dba_jobs

where failures !=0 or failures is not null;

spool off;