5
column OWNER for a30
column DIRECTORY_NAME for a30
column DIRECTORY_PATH for a50
column PARAMETER for a40
column VALUE for a40
column sid format 9999
column command format a20
column program format a25
column username format a15
column machine format a15
column event format a25
column sql_text format a40
column name format a30
column member format a30
column type format a20
column value format a35
column RY_TABLESPACE for a20
column PROFILE for a40
column table_name for a50
column grantor for a30
column GRANTEE for a30
column KSPPDESC for a50
column KSPPINM for a50
column KSPPSTVL for a50
column FILE_NAME for a70
column DB_LINK for a40
column HOST for a40
column table_name for a50
column segment_name for a50
set linesize 1000
set trims on
set echo off
set termout off
spool user_exp_check.log
create table exp_check as
select 1 as id , username from dba_users
where account_status='OPEN'
and username not in ('ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS',
'LBACSYS','MDSYS', 'MGMT_VIEW', 'OLAPSYS', 'ORDDATA', 'OWBSYS','ORDPLUGINS',
'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS','SYSMAN', 'SYSTEM',
'WK_TEST', 'WKSYS', 'WKPROXY','WMSYS', 'XDB')
;
set heading off
set feedback off
select '##check directorts' from dual;
set heading on
set feedback on
select * from dba_directories;
set heading off
set feedback off
select '##check Character set' from dual;
set heading on
set feedback on
SELECT * FROM NLS_DATABASE_PARAMETERS;
set heading off
set feedback off
select '##check open user' from dual;
set heading on
set feedback on
select username,default_tablespace,temporary_tablespace,profile,account_status
from dba_users where account_status='OPEN' order by 1,2;
set heading off
set feedback off
select '##check user privilege and role' from dual;
set heading on
set feedback on
select owner,table_name,grantor,privilege from dba_tab_privs where grantee in
(select username from exp_check);
select grantee,privilege from dba_sys_privs where grantee in (select username
from exp_check) union select grantee,privilege from dba_sys_privs where grantee
in (select granted_role from dba_role_privs where grantee in (select username
from exp_check));
set heading off
set feedback off
select '##check user index' from dual;
set heading on
set feedback on
select owner,count(*) from dba_indexes where owner in (select username from
exp_check) group by owner order by 1,2;
set heading off
set feedback off
select '##check user lob' from dual;
set heading on
set feedback on
select l.owner,l.table_name,l.segment_name,sum(s.Bytes)/1024/1024/1024 GB from
dba_lobs l left join dba_segments s on l.segment_name=s.segment_name where
l.owner in (select username from exp_check) group by
l.owner,l.table_name,l.segment_name order by 4;
set heading off
set feedback off
select '##check user object count' from dual;
set heading on
set feedback on
select owner,object_type,status,count(*) from dba_objects where owner in
(select username from exp_check) and object_type !='LOB' group by
owner,object_type,status order by 1,2;
set heading off
set feedback off
select '##check user object in system/sysaux' from dual;
set heading on
set feedback on
select owner,segment_name,tablespace_name from dba_segments where owner in
(select username from exp_check) and tablespace_name in ('system','sysaux');
/*
if user segment data in sys tablespace
table:alter table tab_name move tablespace tbs_name;
index:alter index index_name rebuild tablespace tbs_name;
*/
set heading off
set feedback off
select '##check invalid object' from dual;
set heading on
set feedback on
select owner,object_type,count(*) from dba_objects
where owner in (select username from exp_check)
and status='INVALID'
and object_type not in ('INDEX','LOB') group by owner,object_type order by
1,2,3;
--Parameters that need to be modified for the partition table
--Implied parameter of the initial size of the partition table that needs to be
modified for 10g migration to 12C
--alter system set "_partition_large_extents=flase";
set heading off
set feedback off
select '##check user partition' from dual;
set heading on
set feedback on
select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx=y.indx
and ksppinm in('_partition_large_extents');
set heading off
set feedback off
select '##check db space' from dual;
set heading on
set feedback on
select
df.tablespace_name,
round( df.total_space / 1048576, 0 ) "Total Space(MB)",
round( ( df.total_space - nvl( fs.free_space, 0 ) ) / 1048576, 0 ) "Used
Space(MB)",
round( nvl( fs.free_space, 0 ) / 1048576, 0 ) "Free Space(MB)",
round( 100 * ( 1 - nvl( fs.free_space, 0 ) / df.total_space ), 2 ) "%Used",
to_char( Sysdate, 'yyyy-mm-dd hh24:mi' ) Stat_Time
from
( select tablespace_name, sum( bytes ) total_space from dba_data_files group
by tablespace_name ) df,
( select b.tablespace_name, sum( b.bytes ) free_space from dba_free_space b
group by b.tablespace_name ) fs
where
df.tablespace_name = fs.tablespace_name(+)
and exists ( select * from dba_tablespaces ts where ts.contents <> 'TEMPORARY'
and ts.tablespace_name = df.tablespace_name )
union
select
df.tablespace_name,
round( df.total_space / 1048576, 0 ) "Total Space(MB)",
round( nvl( us.used_space, 0 ) / 1048576, 0 ) "Used Space(MB)",
round( ( df.total_space - nvl( us.used_space, 0 ) ) / 1048576, 0 ) "Free
Space(MB)",
round( 100 * ( nvl( us.used_space, 0 ) / df.total_space ), 2 ) "%Used",
to_char( Sysdate, 'yyyy-mm-dd hh24:mi' ) Stat_Time
from
( select tablespace_name, sum( bytes ) total_space from dba_temp_files group
by tablespace_name ) df,
( select b.tablespace_name, sum( b.bytes_cached ) used_space from
v$temp_extent_pool b group by b.tablespace_name ) us
where
df.tablespace_name = us.tablespace_name(+)
and exists ( select * from dba_tablespaces ts where ts.contents = 'TEMPORARY'
and ts.tablespace_name = df.tablespace_name )
order by 5 desc;
of 5
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
1
2
9-数据库人的进阶之路：从PG分区、SQL优化到拥抱AI未来（罗敏）.pptx
3
1-PG版本兼容性案例（彭冲）.pptx
4
2-TDSQL PG在复杂查询场景中的挑战与实践-opensource.pdf
5
6-PostgreSQL 哈希索引原理浅析（文一）.pdf
6
8-基于PG向量和RAG技术的开源知识库问答系统MaxKB.pptx
7
3-AI时代的变革者-面向机器的接口语言(MOQL)_吕海波.pptx
8
4-IvorySQL V4：双解析器架构下的兼容性创新实践.pptx
9
7-拉起PG好伙伴DifySupaOdoo.pdf
10
《云原生安全攻防启示录》李帅臻.pdf
文档被以下合辑收录
相关文档
评论