數據庫信息收集腳本怎么寫

143次閱讀

共計 8070 個字符，預計需要花費 21 分鐘才能閱讀完成。

數據庫信息收集腳本怎么寫，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

數據庫信息統計：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

關于數據庫信息收集腳本怎么寫問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

正文完