oracle数据抽取脚本,oracle的一些信息抽取脚本.sql

spool  guowang.log

----set pagesize 9999

----set linesize 9999

----set long 99999

set echo off

set feedback off

alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";

prompt '********************数据库部署方式********************'

select name,value from v$parameter where name='cluster_database';

prompt '********************数据库软件个数及名称********************'

prompt '********************部署的数据库类型及版本********************'

select * from v$version;

prompt '********************数据库实例个数及名称********************'

select instance_number,instance_name from v$instance;

prompt '********************数据库用户名称********************'

select username from dba_users;

prompt '********************表空间名称及大小(G)********************'

SELECT   UPPER (f.tablespace_name) "TABLESPACE_NAME",

d.tot_grootte_mb "TOTAL_SIZE(M)",

TO_CHAR (ROUND (  (d.tot_grootte_mb - f.total_bytes)

/ d.tot_grootte_mb

* 100,

2

),

'990.99'

)||'%' "PER_USERD",d.tot_grootte_mb - f.total_bytes "USERD(M)",f.total_bytes "FREE(M)"

FROM (SELECT   tablespace_name,

ROUND (SUM (BYTES) / (1024 * 1024), 2) total_bytes

FROM SYS.dba_free_space

GROUP BY tablespace_name) f,

(SELECT   dd.tablespace_name,

ROUND (SUM (dd.BYTES) / (1024 * 1024), 2) tot_grootte_mb

FROM SYS.dba_data_files dd

GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name

ORDER BY 3 DESC;

prompt '********************数据库文件存放路径********************'

select name from v$datafile;

prompt '********************业务数据存放位置********************'

prompt '********************业务数据存储机制********************'

prompt '********************业务系统现有数据量(G)********************'

select owner,SUM(BYTES/1024/1024) from dba_segments group by owner;

prompt '********************数据库最大连接用户数********************'

select value from v$parameter where name='processes';

prompt '********************数据库数据的处理类型********************'

prompt '********************数据库操作高峰时段********************'

prompt '********************数据库字符集********************'

select * from nls_database_parameters

prompt '********************内存分配量(SGA)(M)********************'

select name,VALUE/1024/1024 from v$parameter where name in('sga_max_size','sga_target');

prompt '********************listerner端口********************'

host lsnrctl stat

prompt '********************归档日志日增长数********************'

select trunc(first_time) "DATE",thread#,count(1) "ARCH_PER_DAY",round(count(1) / 24) "ARCH_PER_HR"

from v$log_history

group by trunc(first_time), thread#

order by 1,3 desc;

----------------------------

select to_char(first_time, 'yyyy-mm-dd hh24') "DATE",

thread#,

count(1) "ARCH_HR"

from v$log_history

where trunc(first_time) in

(select d_time

from (select max(count(1)) m_arch

from v$log_history

group by trunc(first_time)) a,

(select trunc(first_time) d_time, count(1) d_arch

from v$log_history

group by trunc(first_time)) b

where a.m_arch = b.d_arch)

group by to_char(first_time, 'yyyy-mm-dd hh24'), thread#

order by thread#,3 desc;

prompt '********************归档日志大小(MB)********************'

select bytes/1024/1024 from v$log;

prompt '********************归档日志总增长量

日增长量计算：

(每天生成的生产日志个数*大小)********************'

spool off;

exit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22741583/viewspace-683960/，如需转载，请注明出处，否则将追究法律责任。