--0.更新统计信息(注意修改OWNER值)
--0.1.ORACLE环境拼接收集统计信息的SQL

SPOOL /home/oracle/GATHER_TABLE_STATS.sql
SET LINESIZE 999
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>''' || OWNER ||
       ''',TABNAME=>''' || TABLE_NAME ||
       ''',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,DEGREE=>4);' AS "收集统计信息"
  FROM DBA_TABLES
 WHERE OWNER = 'SCOTT';
 SPOOL OFF

--0.2.DM环境拼接收集统计信息的SQL
--查看统计信息
DBMS_STATS.TABLE_STATS_SHOW('SYSDBA','TEST')

--收集指定用户下所有表所有列的统计信息：
SET LINESIZE 999
SELECT 'CALL DBMS_STATS.GATHER_SCHEMA_STATS('''|| USERNAME || ''',100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' AS "收集表的统计信息"
FROM DBA_USERS
 WHERE USERNAME = 'SYSDBA';

--收集指定用户下所有索引的统计信息：
SET LINESIZE 999
SELECT 'CALL DBMS_STATS.GATHER_SCHEMA_STATS('''|| USERNAME || ''',1.0,TRUE,''FOR ALL INDEXED SIZE AUTO'');' AS "收集表的统计信息"
FROM DBA_USERS
 WHERE USERNAME = 'SYSDBA';
 
--或 收集某个表的单个索引统计信息：
SELECT 'CALL DBMS_STATS.GATHER_INDEX_STATS('''|| OWNER ||''','''||INDEX_NAME||''');' AS "收集统计信息"
  FROM DBA_INDEXES 
 WHERE OWNER = 'SYSDBA' 
   AND TABLE_NAME = 'TEST';

--收集指定用户下某表统计信息：
SET LINESIZE 999
SELECT 'CALL DBMS_STATS.GATHER_TABLE_STATS('''|| OWNER ||''','''||TABLE_NAME||''',NULL,100,TRUE,''FOR ALL COLUMNS SIZE AUTO'');' AS "收集统计信息"
  FROM DBA_TABLES
 WHERE OWNER = 'SYSDBA';

--收集某表某列的统计信息：
STAT 100 ON TABLE_NAME(COLUMN_NAME);

--1.筛选大表
--1.1.查看业务用户下每个表的行数
SELECT OWNER, TABLE_NAME, NUM_ROWS
  FROM DBA_TABLES
 WHERE OWNER = 'SYS'
   AND NUM_ROWS > 5000
 ORDER BY 3 DESC;
 
--1.2.查看业务用户下每个表或者索引的占用空间
SELECT OWNER, SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 "占用空间（MB）"
  FROM DBA_SEGMENTS
 WHERE OWNER IN ('SYS')
   AND SEGMENT_TYPE = 'TABLE'
 GROUP BY OWNER, SEGMENT_NAME
 ORDER BY 3 ;

SELECT OWNER, SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 "占用空间（MB）"
  FROM DBA_SEGMENTS
 WHERE OWNER IN ('SYS')
   AND SEGMENT_TYPE = 'INDEX'
 GROUP BY OWNER, SEGMENT_NAME
 ORDER BY 3 DESC;
 
--1.3.查看业务用户下每个表行数,表段和索引段占用空间之和
SET LINESIZE 999
WITH T_TAB AS
     (SELECT A.OWNER,
              A.TABLE_NAME,
              A.NUM_ROWS,
              SUM(B.BYTES)/1024/1024 MB
         FROM DBA_TABLES A,
              DBA_SEGMENTS B
        WHERE A.TABLE_NAME=B.SEGMENT_NAME
          AND A.OWNER=B.OWNER
          AND A.OWNER IN ('SCOTT')   ---修改模式名
          AND A.NUM_ROWS>10          ---修改行数
     GROUP BY A.OWNER,
              A.TABLE_NAME,
              A.NUM_ROWS
     )
     ,
     T_IND AS
     (SELECT A.OWNER,
              A.TABLE_NAME,
              A.INDEX_NAME,
              SUM(B.BYTES)/1024/1024 MB
         FROM DBA_INDEXES A,
              DBA_SEGMENTS B
        WHERE A.INDEX_NAME=B.SEGMENT_NAME 
          AND A.OWNER=B.OWNER
     GROUP BY A.OWNER,
              A.TABLE_NAME,
              A.INDEX_NAME
     )
   SELECT T_TAB.OWNER,
          T_TAB.TABLE_NAME ,
          T_TAB.NUM_ROWS, 
          T_TAB.MB AS TABLE_SIZE_MB, 
          NVL(T_IND.MB,0) AS INDEX_SIZE_MB,
          (T_TAB.MB+NVL(T_IND.MB,0)) AS TOTAL_SIZE_MB
     FROM T_TAB
LEFT JOIN T_IND
       ON T_TAB.OWNER=T_IND.OWNER
      AND T_TAB.TABLE_NAME=T_IND.TABLE_NAME
 ORDER BY 4 DESC;
 
--2.分区表
--2.1.查看是一级分区还是二级分区表
SELECT DISTINCT OWNER, TABLE_NAME, PARTITIONING_TYPE
FROM DBA_TAB_PARTITIONS
WHERE OWNER = 'SYS';

--3.大字段表
SELECT OWNER, TABLE_NAME, NUM_ROWS, SUM(SIZE_MB) SIZE_MB
  FROM (SELECT L.OWNER,
               L.TABLE_NAME,
               S.SEGMENT_NAME,
               T.NUM_ROWS,
               S.BYTES / 1024 / 1024 SIZE_MB
          FROM DBA_LOBS L, DBA_SEGMENTS S, DBA_TABLES T
         WHERE L.OWNER = T.OWNER
           AND L.TABLE_NAME = T.TABLE_NAME
           AND L.OWNER = S.OWNER
           AND L.SEGMENT_NAME = S.SEGMENT_NAME
        UNION ALL
        SELECT T.OWNER,
               T.TABLE_NAME,
               S.SEGMENT_NAME,
               T.NUM_ROWS,
               S.BYTES / 1024 / 1024 SIZE_MB
          FROM DBA_TABLES T, DBA_SEGMENTS S
         WHERE T.OWNER = S.OWNER
           AND T.TABLE_NAME = S.SEGMENT_NAME) V1
 WHERE V1.OWNER = 'SCOTT'
 GROUP BY OWNER, TABLE_NAME, NUM_ROWS;

--4.没有主键或者唯一建的表
--4.1.ORACLE查询SQL
SELECT T.OWNER, T.TABLE_NAME
  FROM DBA_TABLES T
  LEFT JOIN DBA_CONSTRAINTS C
    ON T.OWNER = C.OWNER
   AND T.TABLE_NAME = C.TABLE_NAME
   AND C.CONSTRAINT_TYPE IN ('P', 'U')
 WHERE T.OWNER = 'SYSDBA'    ---修改模式名
   AND C.TABLE_NAME IS NULL;
		   
--4.2.DM查询SQL
    SELECT T.NAME
      FROM SYSCONS C
RIGHT JOIN SYSOBJECTS T
        ON (C.TABLEID = T.ID 
                  AND C.TYPE$ = 'P')
     WHERE T.SUBTYPE$='UTAB'
       AND T.SCHID = SF_GET_SCHEMA_ID_BY_NAME('SYSDBA')   -- IN  (SELECT SCHID FROM SYSOBJECTS SCH WHERE TYPE$='SCH' AND SCH.NAME IN  ('SYSDBA', 'DMDDBA')) ---修改模式名
       AND C.ID IS NULL
 
--5.备份表或者带LOG日志表
WITH T_TAB AS
     (SELECT A.OWNER,
              A.TABLE_NAME,
              A.NUM_ROWS,
              SUM(B.BYTES)/1024/1024 MB
         FROM DBA_TABLES A,
              DBA_SEGMENTS B
        WHERE A.TABLE_NAME=B.SEGMENT_NAME
          AND A.OWNER=B.OWNER
          AND A.OWNER IN ('SYS')  ---修改模式名
		  AND (A.TABLE_NAME LIKE '%BAK%' OR A.TABLE_NAME LIKE '%BAK%' OR A.TABLE_NAME LIKE '%LOG%' OR A.TABLE_NAME LIKE '%LOG%')
          AND A.NUM_ROWS>1000
     GROUP BY A.OWNER,
              A.TABLE_NAME,
              A.NUM_ROWS
     )
     ,
     T_IND AS
     (SELECT A.OWNER,
              A.TABLE_NAME,
              A.INDEX_NAME,
              SUM(B.BYTES)/1024/1024 MB
         FROM DBA_INDEXES A,
              DBA_SEGMENTS B
        WHERE A.INDEX_NAME=B.SEGMENT_NAME 
          AND A.OWNER=B.OWNER
     GROUP BY A.OWNER,
              A.TABLE_NAME,
              A.INDEX_NAME
     )
   SELECT T_TAB.OWNER,
          T_TAB.TABLE_NAME ,
          T_TAB.NUM_ROWS, 
          T_TAB.MB AS TABLE_SIZE_MB, 
          NVL(T_IND.MB,0) AS INDEX_SIZE_MB,
          (T_TAB.MB+NVL(T_IND.MB,0)) AS TOTAL_SIZE_MB
     FROM T_TAB
LEFT JOIN T_IND
       ON T_TAB.OWNER=T_IND.OWNER
      AND T_TAB.TABLE_NAME=T_IND.TABLE_NAME
 ORDER BY 4 DESC;

--6.临时表
SELECT OWNER, TABLE_NAME, TEMPORARY_TABLESPACE 
FROM DBA_TABLES
WHERE TEMPORARY = 'Y';

--7.物化视图日志表
SELECT OWNER, MVIEW_NAME, LOG_TABLE
FROM DBA_MVIEWS
WHERE OWNER = 'YOUR_USER'
AND LOG_OWNER = 'YOUR_USER';

--8.内部表
根据实际情况添加DISABLE参数

--9.查看用户对象数据总数
--9.1达梦指定模式查询
  SELECT COUNT(*), 
         SUBTYPE$ 
    FROM SYSOBJECTS 
   WHERE SCHID = SF_GET_SCHEMA_ID_BY_NAME('PAPERLESS_CONFERENCE') ---修改模式名
GROUP BY SUBTYPE$;

--9.2 通用查询
  SELECT OBJECT_TYPE,
         COUNT(OBJECT_NAME)
    FROM DBA_OBJECTS
   WHERE OWNER = 'PAPERLESS' ---修改模式名
     AND OBJECT_TYPE NOT LIKE 'SCH'
GROUP BY OBJECT_TYPE;

10.查询归档量
10.1.Oracle查询
SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MM-DD') AS DATE1,
       COUNT(0) AS CNT,
       ROUND(SUM((BLOCKS * BLOCK_SIZE) / 1024 / 1024)) AS MB
  FROM V$ARCHIVED_LOG
 GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MM-DD')
 ORDER BY DATE1 DESC;
10.2.DM查询
略

附件：异构迁移常用SQL.txt