共计 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 行业资讯频道了解更多相关知识。
