溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
數(shù)據(jù)庫信息收集腳本怎么寫，針對這個問題，這篇文章詳細介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
數(shù)據(jù)庫信息統(tǒng)計：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
數(shù)據(jù)大小
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
數(shù)據(jù)庫日志模式 非存檔模式
自動存檔 禁用
存檔終點 E:\oracle\product\10.2.0\db_1\RDBMS
最早的聯(lián)機日志序列 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
內(nèi)存配置：
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
==============賦權(quán)限===================
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
===========個別表賦權(quán)限==================
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';
關(guān)于數(shù)據(jù)庫信息收集腳本怎么寫問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
