数据库信息收集脚本怎么写

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

数据库信息统计：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'; 

关于数据库信息收集脚本怎么写问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。