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

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

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

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