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

閱讀(717) | 評論(2) | 轉發(0) |

總結

以上是生活随笔為你收集整理的oracle数据抽取脚本,oracle的一些信息抽取脚本.sql的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。