在做 Oracle 升级或者迁移的时候，例如从 Oracle 10g 升级到 11g,从 11g 升级到 19c,亦或者是刚入职刚换项目接触一个数据库的时候，需要查看数据库中的一些信息以便对它有一个更加深入的了解，所以就需要通过一些 SQL 语句来了解他，如下整理了一些相关的常用 SQL 分享给有需要的朋友，可在公众号后台回复【SQL大全四】获取。
作者：JiekeXu
来源 | JiekeXu DBA 之路（ID: JiekeXu_IT）
转载请联系授权 | (微信 ID：JiekeXu_DBA)
SQL 大全一 https://www.modb.pro/doc/22598
SQL 大全二 https://www.modb.pro/db/45337
SQL 大全三 https://www.modb.pro/doc/91589
SQL 大全四 https://www.modb.pro/doc/103483
Oracle DBA 日常维护 SQL 脚本大全（收藏版） https://www.modb.pro/db/44364
本文 SQL 均是在运维工作中总结整理而成的，部分 SQL 来源于互联网，但现在已经不知道具体是来源哪个网站，如有侵权，可联系我及时删除，谢谢！
1、查看业务用户相关信息
set line 240
col profile for a20
set pages 999
col username for a25
col ACCOUNT_STATUS for a18
select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE,DEFAULT_TABLESPACE from dba_users where account_status='OPEN' and DEFAULT_TABLESPACE not in ('SYSTEM','USERS','OGG_TBS') order by CREATED asc;
USERNAME ACCOUNT_STATUS CREATED PROFILE DEFAULT_TABLESPACE
------------------------- ------------------ --------- -------------------- ------------------------------
TEST_PY OPEN 25-NOV-20 DEFAULT TEST_PY_DATA
DBA_BAK OPEN 08-JUN-22 DEFAULT DBA_BAK_DATA
select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE,DEFAULT_TABLESPACE from dba_users
where account_status='OPEN'
and DEFAULT_TABLESPACE!='USERS' order by CREATED asc;
2、用户权限收集
DROP TABLE SCOTT.T_TMP_USER_JIEKE;
CREATE TABLE SCOTT.T_TMP_USER_JIEKE(ID NUMBER, USERNAME VARCHAR2(50), EXEC_SQL VARCHAR2(4000),CREATE_TYPE VARCHAR2(20) );
DROP SEQUENCE SCOTT.S_T_TMP_USER_JIEKE;
CREATE SEQUENCE SCOTT.S_T_TMP_USER_JIEKE;
BEGIN
FOR CUR IN (SELECT D.USERNAME,
D.DEFAULT_TABLESPACE,
D.ACCOUNT_STATUS,
'create user ' || D.USERNAME || ' identified by ' ||
D.USERNAME || ' default tablespace ' ||
D.DEFAULT_TABLESPACE || ' TEMPORARY TABLESPACE ' ||
D.TEMPORARY_TABLESPACE || ';' CREATE_USER,
REPLACE(TO_CHAR(DBMS_METADATA.GET_DDL('USER',D.USERNAME)),CHR(10),'') CREATE_USER1
FROM DBA_USERS D
--WHERE D.USERNAME NOT IN ('ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','BI','CTXSYS','DBSNMP','DIP','DMSYS','DVSYS','EXFSYS','FLOWS_FILES','HR','IX','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OE','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PM','REMOTE_SCHEDULER_AGENT','SCOTT','SH','SI_INFORMATN_SCHEMA','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKPROXY','WKSYS','WMSYS','XDB','XS$NULL')) LOOP
WHERE D.USERNAME IN ('PROD_CC','PROD_CB','PROD_CF','PROD_CU')) LOOP
--create user
INSERT INTO SCOTT.T_TMP_USER_JIEKE
(ID, USERNAME, EXEC_SQL, CREATE_TYPE)
VALUES
(SCOTT.S_T_TMP_USER_JIEKE.NEXTVAL, CUR.USERNAME, CUR.CREATE_USER, 'USER');
---system privilege
INSERT INTO SCOTT.T_TMP_USER_JIEKE
(ID, USERNAME, EXEC_SQL, CREATE_TYPE)
SELECT SCOTT.S_T_TMP_USER_JIEKE.NEXTVAL,
CUR.USERNAME,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ';'
END PRIV,
'DBA_SYS_PRIVS'
FROM DBA_SYS_PRIVS D
WHERE D.GRANTEE = CUR.USERNAME;
---role privilege
INSERT INTO SCOTT.T_TMP_USER_JIEKE
(ID, USERNAME, EXEC_SQL, CREATE_TYPE)
SELECT SCOTT.S_T_TMP_USER_JIEKE.NEXTVAL,
CUR.USERNAME,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION;'
ELSE
'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ';'
END PRIV,
'DBA_ROLE_PRIVS'
FROM DBA_ROLE_PRIVS D
WHERE D.GRANTEE = CUR.USERNAME;
---objects privilege
INSERT INTO SCOTT.T_TMP_USER_JIEKE
(ID, USERNAME, EXEC_SQL, CREATE_TYPE)
SELECT SCOTT.S_T_TMP_USER_JIEKE.NEXTVAL,
CUR.USERNAME,
CASE
WHEN D.GRANTABLE = 'YES' THEN
'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
D.TABLE_NAME || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
END PRIV,
'DBA_TAB_PRIVS'
FROM DBA_TAB_PRIVS D
WHERE D.GRANTEE = CUR.USERNAME;
---column privilege
INSERT INTO SCOTT.T_TMP_USER_JIEKE
(ID, USERNAME, EXEC_SQL, CREATE_TYPE)
SELECT SCOTT.S_T_TMP_USER_JIEKE.NEXTVAL,
CUR.USERNAME,
CASE
WHEN D.GRANTABLE = 'YES' THEN
'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
END PRIV,
'DBA_COL_PRIVS'
FROM DBA_COL_PRIVS D
WHERE D.GRANTEE = CUR.USERNAME ;
END LOOP;
COMMIT;
END;
/
--SELECT * FROM SCOTT.T_TMP_USER_JIEKE;
SELECT * FROM SCOTT.T_TMP_USER_JIEKE where username in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU');
可以直接运行EXEC_SQL列来创建用户并赋予相应的权限。另外，可以创建如下的视图：
CREATE OR REPLACE VIEW VW_USER_PRIVS_PROD_CC AS
SELECT D.GRANTEE,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || D.PRIVILEGE || ' TO ' || D.GRANTEE || ';'
END PRIV,
'SYSTEM_GRANT' TYPE,
'DBA_SYS_PRIVS' FROM_VIEW
FROM DBA_SYS_PRIVS D
UNION ALL
SELECT D.GRANTEE,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION;'
ELSE
'GRANT ' || D.GRANTED_ROLE || ' TO ' || D.GRANTEE || ';'
END PRIV,
'SYSTEM_GRANT' TYPE,
'DBA_SYS_PRIVS' FROM_VIEW
FROM DBA_ROLE_PRIVS D
UNION ALL
SELECT D.GRANTEE,
CASE
WHEN D.GRANTABLE = 'YES' THEN
'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
D.TABLE_NAME || ' TO ' || D.GRANTEE || ' WITH GRANT OPTION ;'
ELSE
'GRANT ' || D.PRIVILEGE || ' ON ' || D.OWNER || '.' ||
D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
END PRIV,
'SYSTEM_GRANT' TYPE,
'DBA_SYS_PRIVS' FROM_VIEW
FROM DBA_TAB_PRIVS D
UNION ALL
SELECT D.GRANTEE,
CASE
WHEN D.GRANTABLE = 'YES' THEN
'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || D.PRIVILEGE || ' (' || D.COLUMN_NAME || ') ON ' ||
D.OWNER || '.' || D.TABLE_NAME || ' TO ' || D.GRANTEE || ';'
END PRIV,
'COL_GRANT' TYPE,
'DBA_COL_PRIVS' FROM_VIEW
FROM DBA_COL_PRIVS D;
这样就可以直接查询某个用户的权限了：
set line 9999
SELECT * FROM VW_USER_PRIVS_PROD_CC D WHERE D.GRANTEE = 'PROD_CC';
通过系统包DBMS_METADATA.GET_DDL也可以获取用户的权限信息，如下所示：
SET LONG 9999 LINE 999 PAGES 999
SELECT DBMS_METADATA.GET_DDL('USER', 'PROD_CC') DDL_SQL FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'PROD_CC') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'PROD_CC') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'PROD_CC') FROM DUAL;
3、检查时区
select dbtimezone from dual;
DBTIME
------
+00:00
SQL> !date
Fri Apr 15 16:30:50 CST 2022
4、检查字符集
col PARAMETER for a30
col VALUE for a30
select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
5、查看补丁信息
col opatch for a30
col comments for a99
select 'opatch',comments from dba_registry_history;
OPATC COMMENTS
------ ---------------------------------------------------------------------------------------------------
opatch Patchset 11.2.0.2.0
opatch Patchset 11.2.0.2.0
$ORACLE_HOME/OPatch/opatch lspatches
29141201;OCW Patch Set Update : 11.2.0.4.190416 (29141201)
29141056;Database Patch Set Update : 11.2.0.4.190416 (29141056)
6、检查数据库组件的安装情况
set pages 345 line 456
col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
select COMP_ID, STATUS from DBA_REGISTRY;
7、检查是否使用索引压缩（keycompression）
select index_name,table_name from dba_indexes where compression='ENABLE';
select owner,table_name from dba_tables where owner not in ('SYS','SYSTEM','GOLDENGATE','SYSMAN','EXFSYS','CTXSYS','WMSYS','APEX_030200','DBSNMP','OGG') and iot_type is not null;
8、检查是否存在同名数据文件
select substr(file_name,-6,2) from dba_data_files
where tablespace_name='CC_DATA' order by 1;
select file_name from dba_data_files
where tablespace_name='CC_DATA' order by 1;
9、检查永久表空间
select t.tablespace_name tablesapce_name,count(f.file_id),sum(f.bytes/1024/1024/1024) GB
from dba_tablespaces t,dba_data_files f
where t.tablespace_name=f.tablespace_name
and t.contents='PERMANENT'
and t.tablespace_name in ('CC_DATA','CC_INDEX','CB_DATA','PROD_CF_TBS','PROD_CU_DATA','PROD_CO_DATA','CC_GP_DATA')
group by t.tablespace_name order by 2;
10、检查表空间是否加密
select tablespace_name,encrypted from dba_tablespaces;
Select * from dba_encrypted_columns;
11、检查源端compatible参数
show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0 --必须大于10.2
如何确定数据库的兼容性级别?
col value for a20
col description for a80
select * from database_compatible_level;
col value clear
col description clear
VALUE DESCRIPTION
-------------------- -------------------------------------------------
11.2.0.4.0 Database will be completely compatible with this
software version
12、检查业务用户视图
select owner,view_name from dba_views
where owner in (select username from dba_users
where account_status='OPEN' and DEFAULT_TABLESPACE not in ('USERS','SYSTEM'));
13、检查无效对象
select * from DBA_INVALID_OBJECTS;
select count(*) from DBA_INVALID_OBJECTS;
Create table scott.tmp_invalid_objects45 as select * from dba_invalid_objects;
--重新编译无效对象
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql
select owner,object_name,object_type,created,last_ddl_time,timestamp from DBA_INVALID_OBJECTS where owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU');
--如果有用户自定义对象，可使用 dbms_metdata.get_ddl 查看定义语句
set long 9999 pagesize 9999
select dbms_metadata.get_ddl('FUNCTION','SPLITSTR','PROD_CB') from dual;
14、统计源端需要迁移的表空间和数据文件大小
select distinct(tablespace_name) from dba_data_files d
where d.tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2','UNDOTBS3','UNDOTBS4');
select d.file_id,
d.tablespace_name,
(select (sum(nb.bytes / 1024 / 1024))
from dba_data_files nb
where nb.tablespace_name = d.tablespace_name) ts_size_m,
(d.user_bytes / 1024 / 1024) file_use_size_m
from dba_data_files d
where d.tablespace_name not in
('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4')
order by file_id;
15、统计用户表总大小
select d.owner,(sum(bytes)/1024/1024) sizes_m from dba_segments d
where d.owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.segment_name and d.owner=b.owner)
group by d.owner order by sum(bytes) desc;
select d.owner,(sum(bytes)/1024/1024/1024) sizes_g from dba_segments d
where d.owner in ('CC_SZ','CC_CB','CC_OP','PROD_CU','PROD_CO')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.segment_name and d.owner=b.owner)
group by d.owner order by sum(bytes) desc;
OWNER SIZES_G
------------------------------ ----------
CC_SZ 1392.85962
CC_CB 105.377991
CC_OP 7.37866211
PROD_CO .458251953
PROD_CU .004638672
select (sum(bytes)/1024/1024/1024) sizes_g from dba_segments;
SIZES_G
----------
1590.34473
16、统计用户对象的个数和类型
对象总数
select d.owner,count(1) from dba_objects d
where d.owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO')
and d.owner not in ('PUBLIC')
and not exists (select 1 from dba_recyclebin b where b.object_name=d.object_name and d.owner = b.owner)
group by d.owner order by count(1) desc;
--查找使用自建函数的 SQL
select distinct sql_id, sql_text, module
from V$SQL,
(select object_name
from DBA_OBJECTS O
where owner = 'PROD_CC'
and object_type in ('FUNCTION', 'PACKAGE'))
where (instr(upper(sql_text), object_name) > 0)
and plsql_exec_time > 0
and regexp_like(upper(sql_fulltext), '^[SELECT]')
and parsing_schema_name = 'PROD_CC';
对象类型汇总
select d.owner,d.object_type,count(1) from dba_objects d
where d.owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO')
and d.owner not in ('PUBLIC')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.object_name and d.owner = b.owner)
group by d.owner,d.object_type
order by count(1) desc;
OWNER OBJECT_TYPE COUNT(1)
------------------------------ ------------------- ----------
PROD_CC INDEX 7352
PROD_CB INDEX 7125
PROD_CF INDEX 4566
PROD_CC SEQUENCE 1151
PROD_CC TABLE 1144
PROD_CB SEQUENCE 1115
PROD_CB TABLE 1106
PROD_CF SEQUENCE 676
PROD_CF TABLE 668
PROD_CC LOB 126
PROD_CB LOB 118
PROD_CF LOB 55
PROD_CC FUNCTION 18
PROD_CB FUNCTION 17
PROD_CU INDEX 15
PROD_CC PROCEDURE 3
PROD_CU TABLE 3
PROD_CB PROCEDURE 2
PROD_CU SEQUENCE 2
PROD_CC TRIGGER 1
PROD_CC TYPE 1
PROD_CF FUNCTION 1
22 rows selected.
检查业务用户自建对象
select OWNER,OBJECT_TYPE,OBJECT_NAME from dba_objects d where d.owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO') and OBJECT_TYPE not in ('INDEX','SEQUENCE','LOB','TABLE') order by 2,1;
17、检查无效索引
select owner,index_name,status from dba_indexes
where status='UNUSABLE' order by 1,2;
select i.owner,i.index_name,p.partition_name,p.status from dba_ind_partitions p,dba_indexes i
where p.index_name=i.index_name and p.status='UNUSABLE' order by 1,2,3;
select i.owner,i.index_name,s.subpartition_name,s.status from dba_ind_subpartitions s,dba_indexes i
where s.index_name=i.index_name and s.status='UNUSABLE' order by 1,2,3;
18、确认系统用户是否包含业务对象
--检查SYS和SYSTEM的重复对象，返回如下行则正常。
set line 345
col OBJECT_NAME for a40
select owner,object_name,object_type from dba_objects
where (object_name,object_type)
in (select object_name,object_type from dba_objects where owner='SYS')
and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ---------------------------------------- -------------------
SYSTEM AQ$_SCHEDULES TABLE
SYSTEM AQ$_SCHEDULES_PRIMARY INDEX
SYSTEM DBMS_REPCAT_AUTH PACKAGE BODY
SYSTEM DBMS_REPCAT_AUTH PACKAGE
select owner,segment_name,segment_type,tablespace_name from dba_segments
where tablespace_name in('SYSTEM','SYSAUX')
and owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO');
19、确认操作系统字节序
select PLATFORM_ID,PLATFORM_NAME,ENDIAN_FORMAT
from v$transportable_platform
where PLATFORM_NAME in ('Linux x86 64-bit','AIX-Based Systems (64-bit)');
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
6 AIX-Based Systems (64-bit) Big
13 Linux x86 64-bit Little
20、检查表空间是否具有自包含特性
SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",
round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%"
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY 4;
exec sys.dbms_tts.transport_set_check('CC_DATA,CCBSCF_INDEX,T4_CBMC_DATA,PROD_CF_TBS,PROD_CU_DATA,PROD_CO_DATA',true);
select * from sys.transport_set_violations;
21、检查是否存在用户使用 TSTZ 字段
select c.owner||'.'||c.table_name ||'('||c.column_name ||') -' || c.data_type || '' col
from dba_tab_cols c,dba_objects o
where c.data_type like '%WITH TIME ZONE'
and c.owner=o.owner and c.table_name=o.object_name
and o.object_type='TABLE'
order by col;
22、检查兼容的高级队列（Compatible Advanced Queues）
select owner,queue_table,recipients,compatible
from dba_queue_tables
where recipients='MULTIPLE'
and compatible like '%8.0%';
23、检查基于XMLSchema的XMLType对象
select distinct owner from dba_xml_schemas;
select distinct p.tablespace_name
from dba_tablespaces p,dba_xml_tables x,dba_users u,all_tables t
where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
and x.owner=u.username;
24、检查 SPATIAL 空间组件对象
select owner,index_name from dba_indexes where ityp_name='SPATIAL_INDEX';
select owner,table_name,column_name from dba_tab_columns
where data_type='SDO_GEOMETRY' and owner!='MDSYS'
order by 1,2,3;
25、检查索引组织表、外部表、临时表、物化视图
select owner,table_name from dba_tables
where IOT_TYPE is not null and owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO');
select owner,table_name from dba_external_tables
where owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO');
select owner,table_name from dba_tables
where temporary='Y' and owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO');
select owner,count(*) from dba_mviews group by owner;
26、检查 Opaque Types 类型字段
Select distinct owner,data_type from dba_tab_columns
where owner in ('PROD_CC','PROD_CB','PROD_CF','PROD_CU','PROD_CO');
27、收集数据字典统计信息
查询最近的统计信息收集
set linesize 200
select max(end_time) LATEST, operation from DBA_OPTSTAT_OPERATIONS
where operation in ('gather_dictionary_stats', 'gather_fixed_objects_stats')
group by operation;
收集统计信息命令如下
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
收集几个聚簇索引的统计信息
--Bug 25286819 : CLUSTER INDEX STATS NOT GATHERED WHEN STALE TABLE OR DICTIONARY STATS ARE GATHER
exec dbms_stats.gather_schema_stats('SYS');
exec dbms_stats.gather_index_stats('SYS','I_OBJ#');
exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#');
exec dbms_stats.gather_index_stats('SYS','I_TS#');
exec dbms_stats.gather_index_stats('SYS','I_USER#');
exec dbms_stats.gather_index_stats('SYS','I_TOID_VERSION#');
exec dbms_stats.gather_index_stats('SYS','I_MLOG#');
exec dbms_stats.gather_index_stats('SYS','I_RG#');
28、查看数据字典状态
该 dbupgdiag.sql 脚本在升级之前或之后收集有关数据库状态的诊断信息。从 My Oracle Support 556610.1 下载脚本，并以数据库 SYS 用户身份运行脚本。该脚本在名为 db_upg_diag_sid_timestamp.log 的日志文件中以可读格式生成诊断信息 ，其中 sid 是数据库的 Oracle 系统标识符， timestamp 是生成文件的时间。
SQL> @dbupgdiag.sql
Enter location for Spooled output as Parameter 1:
Enter value for 1: /tmp
more db_upg_diag_jiekedb_21_Apr_2022_1102.log
29、确认是否有物化视图刷新
select o.name from sys.obj$ o,sys.user$ u,sys.sum$ s where o.type#=42 and bitand(s.mflags,8)=8;
30、查看数据类型
select distinct(DATA_TYPE) from all_tab_columns where owner='PROD_CC';
select distinct(DATA_TYPE) from user_tab_columns;
DATA_TYPE
--------------------------------------------------------------------------------
TIMESTAMP(6)
NVARCHAR2
NUMBER
CHAR
CLOB
DATE
BLOB
VARCHAR2
31、确认数据文件不需要介质恢复，且不处于备份（backup）模式
select * from v$recover_file;
select * from v$backup where status !='NOT ACTIVE';
32、处理分布式事务
select * from dba_2pc_pending;
如果上面有返回行，执行下面内容。
select local_tran_id from dba_2pc_pending;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
Commit;
33、清理回收站信息
Select count(*) from dba_recyclebin;
select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME
from dba_objects where CREATED >=to_date('2023-05-20 15:38:58','yyyy-mm-dd HH24:MI:SS')
and owner!='SYS' and OBJECT_TYPE='TABLE';
purge dba_recyclebin;
34、检查表空间和数据文件的状态
select tablespace_name,status from dba_tablespaces;
select status,online_status,count(*) from dba_data_files
group by status,online_status;
35、获取创建 DBLINK 的脚本
col DB_LINK for a15
col USERNAME for a15
col HOST for a45
select * from dba_db_links;
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
36、获取创建表空间语句
创建所有表空间语句，以用于从生产端导入所有权限及用户，相关脚本如下：
set heading off feedback off trimspool on linesize 500
spool create_tablespace.sql
prompt /* ===================== */
prompt /* Create user tablespaces */
prompt /* ===================== */
select 'create TABLESPACE ' || tablespace_name ||
' DATAFILE ' ||'''+DATA/JREDB/DATAFILE/'||tablespace_name||'.dbf'''||' size 10M
autoextend on;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','USERS')
and contents = 'PERMANENT';
prompt /* ===================== */
prompt /* Create user temporary tablespaces */
prompt /* ===================== */
select 'create TEMPORARY TABLESPACE ' || tablespace_name ||
' TEMPFILE ' ||'''+DATA/JREDB/DATAFILE/'||tablespace_name||'.dbf'''||' size 10M
autoextend on;'
from dba_tablespaces
where tablespace_name not in ('TEMP')
and contents = 'TEMPORARY';
spool off
37、查询表空间使用率
set pagesize 1000 linesize 180
tti 'Tablespace Usage Status'
col "TOTAL(GB)" for 99,999,999.999
col "USAGE(GB)" for 99,999,999.999
col "FREE(GB)" for 99,999,999.999
col "EXTENSIBLE(GB)" for 99,999,999.999
col "MAX_SIZE(GB)" for 99,999,999.999
col "FREE PCT %" for 999.99
col "USED PCT OF MAX %" for 999.99
col "NO_AXF_NUM" for 9999
col "AXF_NUM" for 999
select d.tablespace_name "TBS_NAME"
,d.contents "TYPE"
,nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)"
,nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024/1024 "USAGE(GB)"
,nvl(f.bytes,0)/1024/1024/1024 "FREE(GB)"
,nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "FREE PCT %"
,nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)"
,nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)"
,nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %"
,a.NO_AXF_NUM
,a.AXF_NUM
from sys.dba_tablespaces d,
(select tablespace_name
,sum(bytes) bytes
,sum(decode(autoextensible,'YES',maxbytes - bytes,0 )) ARTACAK
,count(decode(autoextensible,'NO',0)) NO_AXF_NUM
,count(decode(autoextensible,'YES',0)) AXF_NUM
,sum(decode(maxbytes, 0, BYTES, maxbytes)) MAX_BYTES
from dba_data_files
group by tablespace_name
) a,
(select tablespace_name
,sum(bytes) bytes
from dba_free_space
group by tablespace_name
) f
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and not (d.extent_management like 'LOCAL'and d.contents like 'TEMPORARY')
union all
select d.tablespace_name "TBS_NAME",d.contents "TYPE",nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)",nvl(t.bytes,0)/1024/1024/1024 "USAGE(GB)",nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024/1024 "FREE(GB)",nvl(t.bytes/a.bytes * 100,0) "FREE PCT %",nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)",nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)",nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %",a.NO_AXF_NUM,a.AXF_NUM
from sys.dba_tablespaces d,
(select tablespace_name
,sum(bytes) bytes
,sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK
,count(decode(autoextensible,'NO',0)) NO_AXF_NUM
,count(decode(autoextensible,'YES',0)) AXF_NUM
,sum(decode(maxbytes, 0, BYTES, maxbytes)) MAX_BYTES
from dba_temp_files
group by tablespace_name
) a,
(select tablespace_name
, sum(bytes_used) bytes
from v$temp_extent_pool
group by tablespace_name
) t
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
and d.extent_management like 'LOCAL'
and d.contents like 'TEMPORARY%'
order by 3 desc;
38、比对新旧环境中的 profile 是否一致
select distinct(t.pro) from
(select s.profile pro,l.profile pro2
from dba_profiles@dblink s,dba_profiles l
where s.profile = l.profile(+)
) t
where t.pro2 is null order by t.pro;
--目标环境需要创建连接源环境的 DBLINK
39、查看 ADMINISTER DATABASE TRIGGER 权限
检查拥有 ADMINISTER DATABASE TRIGGER 权限的用户。如果用户创建了数据库级别的触发器，则必须要拥有 ADMINISTER DATABASE TRIGGER 权限。
select owner,trigger_name from dba_triggers where base_object_type='DATABASE' and owner not in (select grantee from dba_sys_privs where privilege='ADMINISTER DATABASE TRIGGER');
OWNER TRIGGER_NAME
------------------------------ ------------------------------
SYSTEM LOGON_IP_CONTROL
SYSMAN MGMT_STARTUP
Grant ADMINISTER DATABASE TRIGGER TO OWNER;
40、无效对象警告和 DBA 注册表错误
在开始升级之前，Oracle 强烈建议您运行升级前信息工具 ( preupgrd.jar)。
升级前信息工具识别无效的 SYS 和 SYSTEM 对象，以及其他无效对象。用于 utlrp.sql 重新编译无效对象。如果您在升级之前未能执行此操作，则很难确定系统中的哪些对象在开始升级之前是无效的，以及哪些对象由于升级而变得无效。
$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT
升级前执行
SQL>@/u01/app/oracle/cfgtoollogs/jiekedb/preupgrade/preupgrade_fixups.sql
升级后执行
SQL>@/u01/app/oracle/cfgtoollogs/jiekedb/preupgrade/postupgrade_fixups.sql
欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
最后修改时间：2023-05-19 09:35:30
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
