Shulou(Shulou.com)05/31 Report--

How to write the database information collection script, in view of this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Database information statistics: 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

Data size

Select round (sum (bytes) / 1024Universe 1024Universe 2) from dba_data_files

Union all

Select round (sum (bytes) / 1024Universe 1024Universe 2) from dba_segments

ROUND (SUM (BYTES) / 1024Universe 1024Universe 1024pr 2)

-

190.88

. 16

Archived information:

SQL > archive log list

Database log mode non-archiving mode

Automatic archiving is disabled

Archive end E:\ oracle\ product\ 10.2.0\ db_1\ RDBMS

The earliest online log sequence 11954

Current log sequence 11961

Character set:

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

Memory configuration:

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. User tablespace

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. View the size and usage of user tablespaces

-- tablespace size

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

-- View tablespace usage

Set pagesize 1000 linesize 500

Col t1 for a30

Col t2 for a10

Select T1 from T2 T3 | |'%'T3 Personality

(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%

= add tablespace =

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

Seven rows have been selected.

= = undo and temporary tablespace =

Undo 4000m

Select tablespace_name,sum (bytes) / 1024 Universe 1024 mb from dba_temp_files group by tablespace_name

TABLESPACE_NAME MB

TEMP 2000

RRT_TEMP 10000

View the installation components:

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

= Grant permissions =

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

= query invalid objects =

Select owner,object_name from dba_objects where status='INVALID' and owner in ('BDP114','BST114') order by 1

= View user information = =

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

= individual tables grant permissions =

Select 'GRANT SELECT ON A.' | | object_name | |' to Bansi 'from dba_objects where owner='A' and object_type='TABLE'

Declare

Begin

For cr in (select table_name from dba_tables where owner=' owner') loop

Execute immediate

'The grant select on belongs to the owner.'| | cr.table_name | |'to target user'

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

Change the awr save time:

Select * from dba_hist_wr_control

Exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval = > 60 years = > 30 years 24 years 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'

The answer to the question about how to write the database information collection script is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.