作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看SQL 大全（四）|数据库迁移升级时常用 SQL 语句，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达

在做 Oracle 升级或者迁移的时候，例如从 Oracle 10g 升级到 11g,从 11g 升级到 19c,亦或者是刚入职刚换项目接触一个数据库的时候，需要查看数据库中的一些信息以便对它有一个更加深入的了解，所以就需要通过一些 SQL 语句来了解他，如下整理了一些相关的常用 SQL 分享给有需要的朋友，可在本公众号后台回复【SQL大全四】获取。另外:近期新建一个微信交流群，现 100 多人了，算比较活跃，也会在群中不定期举行抽奖、发红包福利，如有需要的可添加我个人微信【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  240col profile for a20set pages 999col username for a25col ACCOUNT_STATUS for a18select 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_DATADBA_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 ASSELECT 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 DUNION ALLSELECT 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 DUNION ALLSELECT 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 DUNION ALLSELECT 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 9999SELECT * FROM VW_USER_PRIVS_PROD_CC D WHERE D.GRANTEE = 'PROD_CC';

通过系统包DBMS_METADATA.GET_DDL也可以获取用户的权限信息，如下所示：

SET LONG 9999 LINE 999 PAGES 999SELECT DBMS_METADATA.GET_DDL('USER', 'PROD_CC') DDL_SQL FROM DUALUNION ALLSELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'PROD_CC') FROM DUALUNION ALLSELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'PROD_CC') FROM DUALUNION ALLSELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'PROD_CC') FROM DUAL;

3、检查时区

select dbtimezone from dual;
DBTIME------+00:00SQL> !dateFri Apr 15 16:30:50 CST 2022

4、检查字符集

col PARAMETER for a30col VALUE for a30 select * from nls_database_parameters where parameter like '%CHARACTERSET%';PARAMETER                      VALUE------------------------------ ------------------------------NLS_CHARACTERSET                AL32UTF8NLS_NCHAR_CHARACTERSET         AL16UTF16
select userenv('language') from dual;
USERENV('LANGUAGE')----------------------------------------------------AMERICAN_AMERICA.AL32UTF8

5、查看补丁信息

col opatch for a30col comments for a99select 'opatch',comments from dba_registry_history;
OPATC COMMENTS------ ---------------------------------------------------------------------------------------------------opatch Patchset 11.2.0.2.0opatch Patchset 11.2.0.2.0

$ORACLE_HOME/OPatch/opatch lspatches29141201;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 a40select 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 a20col description for a80select * from  database_compatible_level;col value clearcol description clear VALUE                DESCRIPTION-------------------- -------------------------------------------------11.2.0.4.0           Database will be completely compatible with this                     software version

‍

‍本文 PDF 电子版如有需要的朋友，可在本公众号后台回复【SQL大全四】获取。‍

‍

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 9999select 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.85962CC_CB                             105.377991CC_OP                             7.37866211PROD_CO                           .458251953PROD_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, modulefrom V$SQL,(select object_namefrom DBA_OBJECTS Owhere owner = 'PROD_CC'and object_type in ('FUNCTION', 'PACKAGE'))where (instr(upper(sql_text), object_name) > 0)and plsql_exec_time > 0and 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                     7352PROD_CB                      INDEX                     7125PROD_CF                      INDEX                     4566PROD_CC                      SEQUENCE                  1151PROD_CC                      TABLE                     1144PROD_CB                      SEQUENCE                  1115PROD_CB                      TABLE                     1106PROD_CF                      SEQUENCE                   676PROD_CF                      TABLE                      668PROD_CC                      LOB                        126PROD_CB                      LOB                        118PROD_CF                      LOB                         55PROD_CC                      FUNCTION                    18PROD_CB                      FUNCTION                    17PROD_CU                      INDEX                       15PROD_CC                      PROCEDURE                    3PROD_CU                      TABLE                        3PROD_CB                      PROCEDURE                    2PROD_CU                      SEQUENCE                     2PROD_CC                      TRIGGER                      1PROD_CC                      TYPE                         1PROD_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 iwhere 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 345col OBJECT_NAME for a40select 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                     TABLESYSTEM                         AQ$_SCHEDULES_PRIMARY             INDEXSYSTEM                         DBMS_REPCAT_AUTH                 PACKAGE BODYSYSTEM                         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;

本文 PDF 电子版如有需要的朋友，可在本公众号后台回复【SQL大全四】获取。

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 200select max(end_time) LATEST, operation from DBA_OPTSTAT_OPERATIONSwhere 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)NVARCHAR2NUMBERCHARCLOBDATEBLOBVARCHAR2

本文 PDF 电子版如有需要的朋友，可在本公众号后台回复【SQL大全四】获取。

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 a15col USERNAME for a15col HOST for a45select * 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$ UWHERE 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 180tti 'Tablespace Usage Status'col "TOTAL(GB)" for 99,999,999.999col "USAGE(GB)" for 99,999,999.999col "FREE(GB)" for 99,999,999.999 col "EXTENSIBLE(GB)" for 99,999,999.999col "MAX_SIZE(GB)" for 99,999,999.999col "FREE PCT %" for 999.99col "USED PCT OF MAX %" for 999.99col "NO_AXF_NUM" for 9999col "AXF_NUM" for 999select 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_NUMfrom 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_BYTESfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name       ,sum(bytes) bytesfrom dba_free_spacegroup by tablespace_name) fwhere 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 allselect 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_NUMfrom 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_BYTESfrom dba_temp_filesgroup by tablespace_name) a,(select tablespace_name      , sum(bytes_used) bytes from v$temp_extent_poolgroup by tablespace_name) twhere 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_CONTROLSYSMAN                         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之路】，第一时间一起学习新知识！

本文已生成 PDF 电子版如有需要的朋友，可在本公众号后台回复【SQL大全四】获取。

———————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
———————————————————————————

分享几个数据库备份脚本

Oracle 表碎片检查及整理方案

OGG|Oracle GoldenGate 基础2022 年公众号历史文章合集整理

Oracle 19c RAC 遇到的几个问题OGG|Oracle 数据迁移后比对一致性OGG|Oracle GoldenGate 微服务架构Oracle 查询表空间使用率超慢问题一则Oracle 11g升级到19c需要关注的几个问题国产数据库|TiDB 5.4 单机快速安装初体验Oracle ADG 备库停启维护流程及增量恢复Linux 环境搭建 MySQL8.0.28 主从同步环境