数据库信息收集脚本怎么写
数据库信息收集脚本怎么写，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
成都创新互联专业为企业提供华蓥网站建设、华蓥做网站、华蓥网站设计、华蓥网站制作等企业网站建设、网页设计与制作、华蓥企业网站模板建站服务，十余年华蓥做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
数据库信息统计：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';
关于数据库信息收集脚本怎么写问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注创新互联行业资讯频道了解更多相关知识。
分享名称：数据库信息收集脚本怎么写
分享地址：http://myzitong.com/article/pddjog.html