数据库信息收集脚本怎么写-编程学习网

数据库信息收集脚本怎么写，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

数据库信息统计：10.2.0.5
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl

数据大小
select round(sum(bytes)/1024/1024/1024,2) from dba_data_files
union all
select round(sum(bytes)/1024/1024/1024,2) from dba_segments;

ROUND(SUM(BYTES)/1024/1024/1024,2)
----------------------------------
190.88
.16

归档信息：
SQL> archive log list
数据库日志模式非存档模式
自动存档禁用
存档终点 E:\oracle\product\10.2.0\db_1\RDBMS
最早的联机日志序列 11954
当前日志序列 11961
字符集：
SQL> select parameter,value from nls_database_parameters where parameter in('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET');
PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_CHARACTERSET
ZHS16GBK

内存配置：
set pagesize 1000 linesize 500
col name for a30
select name, round(value / 1024 / 1024, 0)||'M' Mb
from v$parameter
where name in ('memory_max_target',
'memory_target',
'sga_max_size',
'shared_pool_size',
'large_pool_size',
'sga_target',
'db_cache_size',
'db_keep_cache_size',
'pga_aggregate_target',
'java_pool_size',
'streams_pool_size');
NAME MB
------------------------------ -------------------
sga_max_size 1000M
shared_pool_size 0M
large_pool_size 0M
java_pool_size 0M
streams_pool_size 0M
sga_target 1000M
db_cache_size 0M
db_keep_cache_size 0M
pga_aggregate_target 500M

1、用户表空间
set pagesize 1000 linesize 500
col username for a20
col default_tablespace for a30
col temporay_tablespace for a10
select username,default_tablespace,temporary_tablespace from dba_users where account_status='OPEN' order by 1;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------- ------------------------------ ------------------------
--
MGMT_VIEW SYSTEM TEMP
RPTUSER RRT_DATA RRT_TEMP
SYS SYSTEM TEMP
SYSMAN SYSAUX TEMP
SYSTEM SYSTEM TEMP

2、查看用户表空间大小和使用率
--表空间大小
set pagesize 1000 linesize 500
col file_name for a50
select file_name,round(bytes/1024/1024,0) "real(MB)",AUTOEXTENSIBLE,round(MAXBYTES/1024/1024/1024,0) "max(GB)" from dba_data_files
where tablespace_name in
(
select tablespace_name from dba_tablespaces
)
order by 1;

FILE_NAME real(MB) AUT max(GB)
-------------------------------------------------- ---------- --- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RRT_BAK_01.O 5000 NO 0
RA

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RRT_DATA_01. 5000 NO 0
ORA

--查看表空间使用率
set pagesize 1000 linesize 500
col t1 for a30
col t2 for a10
select t1,t2,t3||'%' t3 from
(select Total.Tname T1,
Total.Total_Size||'M' T2,
Round((Total.Total_Size - Used.free_size) / Total.Total_Size, 1) * 100 as T3
from (
-- datafile
select tablespace_name as TName,
round(sum(bytes) / (1024 * 1024), 1) as Total_size
from dba_data_files
group by tablespace_name) Total,
(
-- free space
select tablespace_name as TName,
round(sum(bytes) / (1024 * 1024), 1) as Free_size
from dba_free_space
group by tablespace_name) Used
where Total.TName = Used.TName(+)
order by 3 desc);

T1 T2 T3
------------------------------ ---------- -------------
---
RRT_IDX 48000M 91%
RRT_DATA 118960M 87%
UNDOTBS02 20000M 76%
SYSAUX 2000M 52%
SYSTEM 1000M 31%
RRT_BAK 5000M 27%
USERS 500M 1%

=====================添加表空间=====================
set pagesize 1000 linesize 1000
select 'create tablespace "' || a.tablespace_name || '"' || ' datafile ' || '''' ||
'/oradata2/zxin/' || lower(a.tablespace_name) || '_01.dbf' || '''' ||
' size ' || a.ts || 'm' ||
' extent management local segment space management auto;'
from (select tablespace_name, round(sum(bytes) / (1024 * 1024), 1) ts
from dba_data_files
group by tablespace_name
order by 2) a;

create tablespace "USERS" datafile '/oradata2/zxin/users_01.dbf' size 500m exten
t management local segment space management auto;
create tablespace "SYSTEM" datafile '/oradata2/zxin/system_01.dbf' size 1000m ex
tent management local segment space management auto;
create tablespace "SYSAUX" datafile '/oradata2/zxin/sysaux_01.dbf' size 2000m ex
tent management local segment space management auto;
create tablespace "RRT_BAK" datafile '/oradata2/zxin/rrt_bak_01.dbf' size 5000m
extent management local segment space management auto;
create tablespace "UNDOTBS02" datafile '/oradata2/zxin/undotbs02_01.dbf' size 20
000m extent management local segment space management auto;
create tablespace "RRT_IDX" datafile '/oradata2/zxin/rrt_idx_01.dbf' size 48000m
extent management local segment space management auto;
create tablespace "RRT_DATA" datafile '/oradata2/zxin/rrt_data_01.dbf' size 1189
60m extent management local segment space management auto;

已选择7行。

===============================undo和临时表空间=====================
undo 4000m
select tablespace_name,sum(bytes)/1024/1024 mb from dba_temp_files group by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
TEMP 2000
RRT_TEMP 10000

查看安装组件：
col comp_id for a15
col version for a15
col comp_name for a30
select comp_id,comp_name,version from dba_registry ;
COMP_ID COMP_NAME VERSION
--------------- ------------------------------ ---------------
OWM Oracle Workspace Manager 10.2.0.5.0
EM Oracle Enterprise Manager 10.2.0.5.0
CATALOG Oracle Database Catalog Views 10.2.0.5.0
CATPROC Oracle Database Packages and T 10.2.0.5.0
ypes

==============赋权限===================
select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in ('SH_TYDMTJR_IMMCC') order by grantee;
'GRANT'||PRIVILEGE||'TO'||GRANTEE||';'
---------------------------------------------------------------------------------
grant CREATE VIEW to BDP114;
grant UNLIMITED TABLESPACE to BDP114;
grant CREATE VIEW to BST114;
grant UNLIMITED TABLESPACE to BST114;

===================查询失效对象==================
select owner,object_name from dba_objects where status='INVALID' and owner in ('BDP114','BST114') order by 1;
===================查看用户信息======================
select owner,object_type,count(*) from dba_objects where owner in ('SH_TYDMTJR_IMMCC') group by owner,object_type order by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
BDP114 DATABASE LINK 11

===========个别表赋权限==================
select 'GRANT SELECT ON A.'||object_name||' to B;' from dba_objects where owner='A' and object_type='TABLE';
declare
begin
for cr in (select table_name from dba_tables where owner='表属主') loop
execute immediate
'grant select on 表属主.'||cr.table_name||' to 目标用户';
end loop;
end;

===============mysql=========
select count(*) from sh_kd_zj.t_workorder_info_112;
select table_name,table_rows,data_length/1024/1024 "data_length",create_time,table_collation from
information_schema.tables where table_schema = 'sh_kd_zj' order by table_rows desc
更改awr保存时间：
select * from dba_hist_wr_control;
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60,retention => 30*24*60);

select concat('union all select ''', table_schema ,''' as db ,''',table_name,''' as tbname, count(1) as rows from ', table_schema ,'.',table_name ) as sqlexe from information_schema.tables as t where t.table_type = 'base table' and t.table_schema = 'sh_kd_zj';
select concat('union all select ''', table_schema ,''' as db ,''',table_name,''' as tbname, count(1) as rows from ', table_schema ,'.',table_name ) as sqlexe from information_schema.tables as t where t.table_schema = 'sh_kd_zj';