ORACLE
TABLE of contents
oracle
Oracle 在线学习：https://livesql.oracle.com
Oracle 11g R2 在线数据库：http://sqlfiddle.com/#!4/4cc558/3
Oralce 元数据查询
查权限
Oracle数据库提供了一些系统视图和表，可以查询当前用户的权限信息。以下是一些常用的系统视图和表：
数据库对象信息
DBA_OBJECTS - 显示数据库中所有对象的基本信息,包括对象名称、类型、状态等。
DBA_DEPENDENCIES - 显示数据库中对象之间的依赖关系,包括被依赖对象和依赖对象的信息。
DBA_ERRORS - 显示数据库中存在编译错误的对象信息,包括对象名称、类型、错误行号和错误信息。
DBA_SOURCE - 显示数据库中对象的源代码信息,包括对象名称、类型和源码内容。
DBA_VIEWS - 显示数据库中所有视图的信息,包括视图名称、所有者、视图定义等。
DBA_TRIGGERS - 显示数据库中所有触发器的信息,包括触发器名称、类型、触发事件、触发器定义等。
DBA_TYPES - 显示数据库中所有用户定义类型的信息,包括类型名称、所有者、类型属性等。
DBA_PROCEDURES - 显示数据库中所有存储过程的信息,包括过程名称、所有者、参数信息等。
DBA_FUNCTIONS - 显示数据库中所有函数的信息,包括函数名称、所有者、参数信息等。
DBA_PACKAGES - 显示数据库中所有包的信息,包括包名称、所有者、规格和主体定义等。
存储信息
DBA_DATA_FILES - 显示数据库中所有数据文件的信息,包括文件名称、大小、所属表空间、状态等。
DBA_TABLESPACES - 显示数据库中所有表空间的信息,包括表空间名称、类型、状态、大小、使用情况等。
DBA_SEGMENTS - 显示数据库中所有段的信息,包括段名称、类型(表、索引等)、所属对象、分配的数据块数量等。
DBA_EXTENTS - 显示数据库中所有区的信息,包括区所属的段、区起始块号、区大小等。
DBA_FREE_SPACE - 显示数据库中所有可用空间的信息,包括表空间名称、起始块号、可用块数及可用空间大小。
DBA_TEMP_FILES - 显示数据库中所有临时文件的信息,包括文件名称、大小、所属表空间等。
DBA_TEMP_FREE_SPACE - 显示数据库中所有临时表空间的可用空间信息,包括表空间名称、可用块数及可用空间大小。
DBA_UNDO_EXTENTS - 显示数据库中所有撤销段的区信息,包括区所属的撤销段、区起始块号、区大小等。
DBA_ROLLBACK_SEGS - 显示数据库中所有回滚段的信息,包括回滚段名称、状态、活跃事务数等。
表信息
DBA_TABLES - 显示数据库中所有表的基本信息,包括表名、所有者、clustered 属性等。
DBA_TAB_COLUMNS - 显示数据库中所有表的列信息,包括列名、数据类型、长度等。
DBA_TAB_COMMENTS - 显示数据库中所有表和列的注释信息。
DBA_COL_COMMENTS - 这个视图可以查看数据库中所有表的列注释信息,包括表名、列名、列注释
DBA_TAB_HISTOGRAMS - 显示数据库中表列的直方图信息。
DBA_TAB_MODIFICATIONS - 显示数据库中表的修改信息,包括上次修改时间、行数变化等。
DBA_TAB_PARTITIONS - 显示数据库中所有表分区的信息,包括表名、分区名、分区方式、分区键值等。
DBA_TAB_SUBPARTITIONS - 显示数据库中所有表子分区的信息,包括表名、分区名、子分区名、子分区方式、子分区键值等。
DBA_PART_KEY_COLUMNS - 这个视图可以查询数据库中所有分区表的分区键列信息,包括表名、分区键列名、列位置等。
DBA_PART_TABLES - 显示数据库中所有分区表的基本信息,包括表名、所有者、分区类型、分区键列等。
DBA_PART_COL_STATISTICS - 显示数据库中所有分区表分区列的统计信息,包括表名、分区名、列名、最小值、最大值等。
DBA_PART_INDEXES - 显示数据库中所有分区表的索引信息,包括索引名称、所属表、分区方式、分区键列等。
DBA_PART_KEY_COLUMNS - 显示数据库中所有分区表的分区键列信息,包括表名、分区键列名、列位置等。
DBA_INDEXES - 显示数据库中所有索引的信息,包括索引名称、所属表、索引类型、关联列等。
DBA_IND_COLUMNS - 显示数据库中所有索引列的信息,包括索引名称、表名、列名、列顺序等。
DBA_IND_EXPRESSIONS - 这个视图可以进一步查看函数索引关联的表达式信息,包括索引名称、表名、索引表达式等。
DBA_SYNONYMS - 显示数据库中所有同义词的信息,包括同义词名、所有者、关联对象等。
DBA_SEQUENCES - 显示数据库中所有序列的信息,包括序列名、所有者、当前值、增量等。
-- 约束信息
DBA_CONSTRAINTS - 除了前面提到的显示所有约束信息之外,这个视图还可以通过约束类型为 "P" 的记录,查看数据库中所有主键的信息,包括主键名称、关联表、关联列等。
DBA_CONS_COLUMNS - 这个视图可以进一步查看主键约束关联的列信息,包括主键名称、关联表名、关联列名等。
权限信息
DBA_TAB_PRIVS - 显示数据库中所有对象权限的信息,包括被授权的对象、权限类型、被授权的用户等。
DBA_ROLE_PRIVS - 显示数据库中所有角色权限的信息,包括被授予角色的用户、被授予的角色等。
DBA_PROFILES - 显示数据库中所有配置文件的信息,包括配置文件名称、参数设置等。
DBA_USERS - 显示数据库中所有用户的信息,包括用户名、默认表空间、创建时间等。
DAB_JOB相关
DBA_JOBS - 显示数据库中所有由 DBA 或系统管理员创建的作业的信息,包括作业编号、作业名称、作业状态、上次运行时间等。
DBA_JOBS_RUNNING - 显示数据库中当前正在运行的 DBA 作业的信息,包括作业编号、作业名称、执行进度等。
DBA_JOB_LOGS - 显示数据库中 DBA 作业的执行历史记录,包括作业编号、作业名称、执行时间、执行状态等。
DBA_SCHEDULER_JOBS相关
DBA_SCHEDULER_JOBS - 显示数据库中所有已定义的计划作业的信息,包括作业名称、作业类型、作业状态、上次运行时间等。
DBA_SCHEDULER_JOB_RUN_DETAILS - 显示数据库中计划作业的运行历史详细信息,包括作业名称、运行开始时间、运行结束时间、运行状态等。
DBA_SCHEDULER_JOB_ARGUMENTS - 显示数据库中计划作业的输入参数信息,包括作业名称、参数名称、参数值等。
DBA_SCHEDULER_PROGRAM_ARGS - 显示数据库中计划作业所关联程序的参数信息,包括程序名称、参数名称、参数数据类型等。
DBA_SCHEDULER_PROGRAMS - 显示数据库中已定义的计划作业所关联的程序信息,包括程序名称、程序类型、程序定义等。
DBA_SCHEDULER_SCHEDULES - 显示数据库中已定义的作业调度信息,包括调度名称、调度类型、调度频率等。
DBA_SCHEDULER_CHAIN_RULES - 显示数据库中已定义的作业链规则信息,包括作业链名称、前置作业、后置作业等。
DBA_SCHEDULER_CHAIN_STEPS - 显示数据库中已定义的作业链步骤信息,包括作业链名称、步骤名称、步骤类型等。
其他信息
DBA_DB_LINKS - 显示数据库中所有数据库链接的信息。
DBA_DIRECTORIES - 显示数据库中所有目录的信息。
DBA_RECYCLEBIN - 显示数据库回收站中所有对象的信息。
查权限
Oracle数据库提供了一些系统视图和表，可以查询当前用户的权限信息。以下是一些常用的系统视图和表：
DBA_SYS_PRIVS：该视图显示了授予用户的系统级权限。
DBA_TAB_PRIVS：该视图显示了授予用户的表级权限。
DBA_ROLE_PRIVS：该视图显示了授予用户的角色权限。
SESSION_PRIVS：该视图显示了当前会话用户的权限。
USER_SYS_PRIVS：该视图显示了当前用户的系统级权限。
USER_TAB_PRIVS：该视图显示了当前用户的表级权限。
USER_ROLE_PRIVS：该视图显示了当前用户的角色权限。
请注意，查询系统级权限可能需要具备相应的权限或角色（如 SELECT_CATALOG_ROLE），以便查询相关的系统视图。
查表信息
-- 查用户所有的表
SELECT * FROM USER_TABLES ;
-- 查表的注释
SELECT * FROM USER_TAB_COMMENTS ;
-- 查询压缩表
SELECT * FROM USER_TABLES WHERE COMPRESSION = 'ENABLED';
-- 查用户所有的分区表（含二级分区）
SELECT * FROM USER_TAB_PARTITIONS ;
-- 查用户所有的二级分区表（仅二级分区）
SELECT * FROM USER_TABLES ;
查分区表相关的信息
-- 查分区表分区方式、分区字段分区数量（含一级分区和二级分区）
SELECT X.TABLE_NAME, X.PART_TYPE, X.PART_COLUMN_NAME, X.SUBPART_TYPE, X.SUBPART_COLUMN_NAME,X.PARTITION_COUNT
FROM (
SELECT P.TABLE_NAME, P.PARTITIONING_TYPE AS PART_TYPE, K.COLUMN_NAME AS PART_COLUMN_NAME,
P.SUBPARTITIONING_TYPE AS SUBPART_TYPE, SK.COLUMN_NAME AS SUBPART_COLUMN_NAME, P.PARTITION_COUNT AS PARTITION_COUNT
--,P.OWNER
FROM USER_PART_TABLES P INNER JOIN USER_PART_KEY_COLUMNS K ON P.TABLE_NAME = K.NAME
LEFT JOIN USER_SUBPART_KEY_COLUMNS SK ON K.NAME = SK.NAME
) X WHERE 1=1 -- AND X.OWNER= V_OWNER -- DBA_TABLES 需要
AND X.TABLE_NAME NOT LIKE 'BIN%';
查表的列
(1) 全部的列
-- 查表的列信息、类型长度、非空等
SELECT * FROM USER_TAB_COLUMNS ;
-- 查表的列注释
SELECT * FROM USER_COL_COMMENTS ;
--查表 注释
SELECT * FROM ALL_TAB_COMMENTS T WHERE T.OWNER = 'AMS';
--查表的列 注释
SELECT T.TABLE_NAME, T.COLUMN_NAME, C.COMMENTS FROM ALL_TAB_COLS T INNER JOIN
ALL_COL_COMMENTS C ON T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME
WHERE T.OWNER='AMS' AND C.OWNER = 'AMS';
（2）查主键列
-- 查表的列信息、类型长度、非空等
SELECT * FROM USER_TAB_COLUMNS ;
-- 查列的约束
SELECT * FROM USER_CONS_COLUMNS ;
-- 查表的主键 CONSTRINT_TYPE = 'P' 是主键
SELECT * FROM USER_CONSTRAINTS ;
-- 查表的主键
SELECT CC.TABLE_NAME, CC.CONSTRAINT_NAME, LISTAGG(CC.COLUMN_NAME,',')
WITHIN GROUP (ORDER BY CC.TABLE_NAME, CC.COLUMN_NAME)
FROM USER_CONS_COLUMNS CC, USER_CONSTRAINTS CS
WHERE CC.CONSTRAINT_NAME = CS.CONSTRAINT_NAME
GROUP BY CC.TABLE_NAME, CC.CONSTRAINT_NAME
ORDER BY CC.TABLE_NAME, CC.CONSTRAINT_NAME
（3）索引列
-- 查用户索引
SELECT * FROM USER_INDEXES ;
-- 查用户索引涉及到列
SELECT * FROM USER_IND_COLUMNS ;
（3）特殊索引
-- 查失效索引
SELECT * FROM USER_INDEXES WHERE STATUS = 'INVALID' ;
-- 查函数索引
SELECT * FROM USER_INDEXES WHERE INDEX_TYPE like '%FUNCTION-BASED%';
一次性查询
-- 查询索引
SELECT IC.TABLE_NAME, IC.INDEX_NAME,
LISTAGG(IC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY TABLE_NAME,INDEX_NAME, COLUMN_POSITION )
FROM USER_IND_COLUMNS IC
WHERE 1=1 AND TABLE_NAME = 'B_AUTOSTORE'
GROUP BY IC.TABLE_NAME, IC.INDEX_NAME
ORDER BY IC.INDEX_NAME, IC.INDEX_NAME
-- 查询表索引-含普通索引、函数索引等
SELECT T.TABLE_NAME, T.INDEX_NAME,
LISTAGG(CASE
WHEN INSTR(I.INDEX_TYPE, 'FUNCTION') > 0 THEN NEWCOLUMN_NAME
ELSE T.COLUMN_NAME || ' ' || T.DESCEND END, ',')
WITHIN GROUP (ORDER BY T.TABLE_NAME,T.INDEX_NAME,T.COLUMN_POSITION) AS COLUMN_NAME,
I.INDEX_TYPE,
I.UNIQUENESS
FROM (SELECT N.*,
(CASE
WHEN INSTR(N.COLUMN_NAME, 'SYS_NC') > 0 THEN
(SELECT INDEX_COLUMN_EXPRESSION(E.TABLE_NAME, E.INDEX_NAME, E.COLUMN_POSITION)
FROM USER_IND_EXPRESSIONS E
WHERE E.INDEX_NAME = N.INDEX_NAME AND E.COLUMN_POSITION = N.COLUMN_POSITION)
ELSE N.COLUMN_NAME END) AS NEWCOLUMN_NAME
FROM USER_IND_COLUMNS N
WHERE N.TABLE_NAME = 'ACT_RU_CASE_EXECUTION'
) T, USER_INDEXES I
WHERE T.INDEX_NAME = I.INDEX_NAME
AND T.TABLE_NAME = I.TABLE_NAME
GROUP BY T.TABLE_NAME, T.INDEX_NAME, I.INDEX_TYPE, I.UNIQUENESS
ORDER BY T.TABLE_NAME, T.INDEX_NAME ;
查带有自增分区的表
SELECT * FROM DBA_PART_TABLES T WHERE T.OWNER='AMS' AND T.INTERVAL IS NOT NULL ;
查存储
查表和索引的存储
--查询表及表的素引占用的存储空间 (删选大于1G的)
SELECT M.TABLE_NAME, M.TABLE_GB, M.INDEX_GB FROM (
-- 行转列-start
SELECT TABLE_NAME, TABLE_GB, INDEX_GB FROM (
-- 可查单表 START
SELECT O.TABLE_NAME, O.SEGMENT_TYPE, ROUND(SUM(GB), 4) AS OGB FROM (
SELECT CASE WHEN INSTR(T.SEGMENT_TYPE, 'TABLE') >0 THEN T.SEGMENT_NAME
ELSE ( SELECT I.TABLE_NAME FROM DBA_INDEXES I WHERE I.INDEX_NAME = T.SEGMENT_NAME)
END AS TABLE_NAME,
CASE WHEN INSTR(T.SEGMENT_TYPE, 'TABLE') >0 THEN 'TABLE' ELSE 'INDEX' END AS SEGMENT_TYPE,
T.PARTITION_NAME,
T.BYTES/1024/1024/1024 AS GB
FROM DBA_SEGMENTS T
WHERE T.OWNER = 'BVIS'
AND ( t.SEGMENT_NAME = 'ZXC_SUBPART_TEST' OR T.SEGMENT_NAME IN (
SELECT D.INDEX_NAME FROM DBA_INDEXES D WHERE D.TABLE_NAME = 'ZXC_SUBPART_TEST' )
)
AND PARTITION_NAME = ''
AND T.SEGMENT_NAME NOT LIKE 'BIN%'
AND T.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','INDEX','INDEX PARTITION','INDEX SUBPARTITION')
) O
WHERE 1=1 --AND O.TABLE_NAME = ''
GROUP BY O.TABLE_NAME, O.SEGMENT_TYPE, O.PARTITION_NAME
ORDER BY O.TABLE_NAME, O.SEGMENT_TYPE DESC, O.PARTITION_NAME
-- 可查单表 END
) X PIVOT (SUM(OGB) FOR SEGMENT_TYPE IN ('TABLE' AS TABLE_GB, 'INDEX' AS INDEX_GB) )
-- 行转列-end
) M
WHERE M.TABLE_GB > 0;
统计表数据信息
统计表、数据量、表数据存储、表索引存储
(1) 准备表
CREATE TABLE ZXC_QUERYCOUNT_TC(
TABLE_NAME VARCHAR2(40),
PART_TYPE VARCHAR2(20),
PART_COLUMN_NAME VARCHAR2(40),
SUBPART_TYPE VARCHAR2(20),
SUBPART_COLUMN_NAME VARCHAR2(40),
PARTITION_COUNT NUMBER(20),
TOTAL_COUNT VARCHAR2(200),
TBL_SIZE VARCHAR2(200),
TBL_IDX_SIZE VARCHAR2(200),
IF_VALID CHAR(1) DEFAULT '1',
QUERY_CRITERIA VARCHAR2(200),
VALUE1 VARCHAR2(200),
VALUE2 VARCHAR2(200),
LAST_OPDATE DATE DEFAULT SYSDATE,
EXT1 VARCHAR2(500),
EXT2 VARCHAR2(500)
);
COMMENT ON TABLE ZXC_QUERYCOUNT_TC IS '表信息统计';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.TABLE_NAME IS '表名称';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.PART_TYPE IS '一级分区类型';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.PART_COLUMN_NAME IS '一级分区字段';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.SUBPART_TYPE IS '二级分区类型';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.SUBPART_COLUMN_NAME IS '二级分区字段';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.PARTITION_COUNT IS '表分区数';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.TOTAL_COUNT IS '表数据记录数';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.TBL_SIZE IS '表数据存储大小,单位以查询时的SQL为准';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.TBL_IDX_SIZE IS '表索引存储大小,单位以查询时的SQL为准';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.IF_VALID IS '是否有效，1-有效;0无效';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.QUERY_CRITERIA IS '复杂查询时查询条件';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.VALUE1 IS '复杂查询时变量条件';
COMMENT ON COLUMN ZXC_QUERYCOUNT_TC.VALUE2 IS '复杂查询时变量条件';
CREATE TABLE ZXC_QUERYCOUNT_LIST_TC
(
TABLE_NAME VARCHAR2(40),
PART_NAME VARCHAR2(50),
SUBPART_COUNT NUMBER(20),
SUBPART_NAME VARCHAR2(50),
PART_DATA_COUNT VARCHAR2(200),
PART_TBL_SIZE VARCHAR2(200),
PART_IDX_SIZE VARCHAR2(200),
IF_VALID CHAR(1) DEFAULT '1',
LAST_OPDATE DATE DEFAULT SYSDATE,
EXT1 VARCHAR2(500),
EXT2 VARCHAR2(500)
);
COMMENT ON TABLE ZXC_QUERYCOUNT_LIST_TC IS '分区表分区信息统计';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.TABLE_NAME IS '表名称';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.PART_NAME IS '一级分区名称';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.SUBPART_COUNT IS '一级分区数量';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.SUBPART_NAME IS '一级分区名称';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.PART_DATA_COUNT IS '分区数据记录数';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.PART_TBL_SIZE IS '表数据存储大小,单位以查询时的SQL为准';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.PART_IDX_SIZE IS '表索引存储大小,单位以查询时的SQL为准';
COMMENT ON COLUMN ZXC_QUERYCOUNT_LIST_TC.IF_VALID IS '是否有效，1-有效;0无效';
(2) 初始化表进行进表
-- 普通表
INSERT INTO ZXC_QUERYCOUNT_TC(TABLE_NAME ) SELECT T.TABLE_NAME FROM USER_TABLES T
WHERE 1=1 --AND T.OWNER= V_OWNER -- DBA_TABLES 需要
AND T.TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM USER_TAB_PARTITIONS -- WHERE OWNER= V_OWNER
);
COMMIT ;
--select * from ZXC_QUERYCOUNT_TC WHERE PART_TYPE IS NULL;
-- 分区表表
INSERT INTO ZXC_QUERYCOUNT_TC(TABLE_NAME, PART_TYPE,PART_COLUMN_NAME, SUBPART_TYPE, SUBPART_COLUMN_NAME, TOTAL_PART_COUNT )
SELECT X.TABLE_NAME, X.PART_TYPE, X.PART_COLUMN_NAME, X.SUBPART_TYPE, X.SUBPART_COLUMN_NAME,X.PARTITION_COUNT
FROM (
SELECT P.TABLE_NAME, P.PARTITIONING_TYPE AS PART_TYPE, K.COLUMN_NAME AS PART_COLUMN_NAME,
P.SUBPARTITIONING_TYPE AS SUBPART_TYPE, SK.COLUMN_NAME AS SUBPART_COLUMN_NAME, P.PARTITION_COUNT AS PARTITION_COUNT
--,P.OWNER
FROM USER_PART_TABLES P INNER JOIN USER_PART_KEY_COLUMNS K ON P.TABLE_NAME = K.NAME
LEFT JOIN USER_SUBPART_KEY_COLUMNS SK ON K.NAME = SK.NAME
) X where 1=1 -- AND X.OWNER= V_OWNER -- DBA_TABLES 需要
AND x.TABLE_NAME not like 'BIN%';
COMMIT ;
--select * from ZXC_QUERYCOUNT_TC WHERE PART_TYPE IS NOT NULL;
-- 初始化分区表分区信息
INSERT INTO ZXC_QUERYCOUNT_LIST_TC(TABLE_NAME, PART_NAME, SUBPART_COUNT, SUBPART_NAME)
SELECT T.TABLE_NAME, T.PARTITION_NAME, T.SUBPARTITION_COUNT, S.SUBPARTITION_NAME
FROM USER_TAB_PARTITIONS T LEFT JOIN USER_TAB_SUBPARTITIONS S ON T.TABLE_NAME=S.TABLE_NAME AND T.PARTITION_NAME=S.PARTITION_NAME
WHERE 1=1 --AND T.TABLE_OWNER= V_OWNER
AND T.TABLE_NAME not like 'BIN%';
COMMIT ;
--select * from ZXC_QUERYCOUNT_LIST_TC ;
(3) 准备SQL脚本。根据需要调整
这里将建表和初始化也一起放进来了。
CREATE OR REPLACE PACKAGE ZXC_COUNT_PKG
IS
PROCEDURE INIT_TABLE_CREATE;
PROCEDURE INIT_TABLE_INFO(V_OWNER VARCHAR2);
PROCEDURE QUERY_TABLE_COUNT;
PROCEDURE QUERY_TABLE_SIZE(V_OWNER VARCHAR2, DBA_VIEW NUMBER DEFAULT 0);
PROCEDURE QUERY_PART_COUNT;
PROCEDURE QUERY_PART_SIZE(V_OWNER VARCHAR2, DBA_VIEW NUMBER DEFAULT 0);
END;
/
CREATE OR REPLACE PACKAGE body ZXC_COUNT_PKG
IS
TYPE CURS_TYPE IS REF CURSOR;
PROCEDURE INIT_TABLE_CREATE IS
V_SQL1 varchar2(2000) ;
V_SQL2 varchar2(2000) ;
BEGIN
V_SQL1 := 'CREATE TABLE ZXC_QUERYCOUNT_TC(
TABLE_NAME VARCHAR2(40),
PART_TYPE VARCHAR2(20),
PART_COLUMN_NAME VARCHAR2(40),
SUBPART_TYPE VARCHAR2(20),
SUBPART_COLUMN_NAME VARCHAR2(40),
PARTITION_COUNT NUMBER(20),
TOTAL_COUNT VARCHAR2(200),
TBL_SIZE VARCHAR2(200),
TBL_IDX_SIZE VARCHAR2(200),
IF_VALID CHAR(1) DEFAULT ''1'',
QUERY_CRITERIA VARCHAR2(200),
VALUE1 VARCHAR2(200),
VALUE2 VARCHAR2(200),
LAST_OPDATE DATE DEFAULT SYSDATE,
EXT1 VARCHAR2(500),
EXT2 VARCHAR2(500)
);';
EXECUTE IMMEDIATE V_SQL1;
V_SQL2 := 'CREATE TABLE ZXC_QUERYCOUNT_LIST_TC
(
TABLE_NAME VARCHAR2(40),
PART_NAME VARCHAR2(20),
SUBPART_COUNT NUMBER(20),
SUBPART_NAME VARCHAR2(20),
PART_DATA_COUNT VARCHAR2(200),
PART_TBL_SIZE VARCHAR2(200),
PART_IDX_SIZE VARCHAR2(200),
IF_VALID CHAR(1) DEFAULT ''1'',
LAST_OPDATE DATE DEFAULT SYSDATE,
EXT1 VARCHAR2(500),
EXT2 VARCHAR2(500)
);';
EXECUTE IMMEDIATE V_SQL2;
end;
PROCEDURE INIT_TABLE_INFO(V_OWNER VARCHAR2) IS
BEGIN
-- 普通表
INSERT INTO ZXC_QUERYCOUNT_TC(TABLE_NAME ) SELECT T.TABLE_NAME FROM USER_TABLES T
WHERE 1=1 --AND T.OWNER= V_OWNER -- DBA_TABLES 需要
AND T.TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM USER_TAB_PARTITIONS -- WHERE OWNER= V_OWNER
);
COMMIT ;
--select * from ZXC_QUERYCOUNT_TC WHERE PART_TYPE IS NULL;
-- 分区表表
INSERT INTO ZXC_QUERYCOUNT_TC(TABLE_NAME, PART_TYPE,PART_COLUMN_NAME, SUBPART_TYPE, SUBPART_COLUMN_NAME, TOTAL_PART_COUNT )
SELECT X.TABLE_NAME, X.PART_TYPE, X.PART_COLUMN_NAME, X.SUBPART_TYPE, X.SUBPART_COLUMN_NAME,X.PARTITION_COUNT
FROM (
SELECT P.TABLE_NAME, P.PARTITIONING_TYPE AS PART_TYPE, K.COLUMN_NAME AS PART_COLUMN_NAME,
P.SUBPARTITIONING_TYPE AS SUBPART_TYPE, SK.COLUMN_NAME AS SUBPART_COLUMN_NAME, P.PARTITION_COUNT AS PARTITION_COUNT
--,P.OWNER
FROM USER_PART_TABLES P INNER JOIN USER_PART_KEY_COLUMNS K ON P.TABLE_NAME = K.NAME
LEFT JOIN USER_SUBPART_KEY_COLUMNS SK ON K.NAME = SK.NAME
) X where 1=1 -- AND X.OWNER= V_OWNER -- DBA_TABLES 需要
AND x.TABLE_NAME not like 'BIN%';
COMMIT ;
--select * from ZXC_QUERYCOUNT_TC WHERE PART_TYPE IS NOT NULL;
-- 初始化分区表分区信息
INSERT INTO ZXC_QUERYCOUNT_LIST_TC(TABLE_NAME, PART_NAME, SUBPART_COUNT, SUBPART_NAME)
SELECT T.TABLE_NAME, T.PARTITION_NAME, T.SUBPARTITION_COUNT, S.SUBPARTITION_NAME
FROM USER_TAB_PARTITIONS T LEFT JOIN USER_TAB_SUBPARTITIONS S ON T.TABLE_NAME=S.TABLE_NAME AND T.PARTITION_NAME=S.PARTITION_NAME
WHERE 1=1 --AND T.TABLE_OWNER= V_OWNER
AND T.TABLE_NAME not like 'BIN%';
COMMIT ;
--select * from ZXC_QUERYCOUNT_LIST_TC ;
END;
PROCEDURE QUERY_TABLE_COUNT IS
QUERY_CUR CURS_TYPE ;
QUERYCOUNT_CUR CURS_TYPE ;
V_QUERY_SQL VARCHAR2(2000);
V_TABLE_NAME VARCHAR2(40);
V_COUNT_SQL VARCHAR2(2000);
V_TABLE_COUNT NUMBER(20);
BEGIN
V_QUERY_SQL := 'SELECT TABLE_NAME FROM ZXC_QUERYCOUNT_TC WHERE TOTAL_COUNT IS NULL and ROWNUM < 1000 ';
OPEN QUERY_CUR FOR V_QUERY_SQL;
LOOP
FETCH QUERY_CUR INTO V_TABLE_NAME ;
EXIT WHEN QUERY_CUR%NOTFOUND;
V_COUNT_SQL := 'SELECT COUNT(*) FROM '|| V_TABLE_NAME ;
OPEN QUERYCOUNT_CUR FOR V_COUNT_SQL ;
LOOP
FETCH QUERYCOUNT_CUR INTO V_TABLE_COUNT ;
EXIT WHEN QUERYCOUNT_CUR%NOTFOUND;
UPDATE ZXC_QUERYCOUNT_TC Z SET Z.TOTAL_COUNT = V_TABLE_COUNT||'' WHERE TABLE_NAME = V_TABLE_NAME ;
COMMIT ;
END LOOP;
close QUERYCOUNT_CUR;
END LOOP;
CLOSE QUERY_CUR;
END QUERY_TABLE_COUNT;
PROCEDURE QUERY_TABLE_SIZE(V_OWNER IN VARCHAR2, DBA_VIEW NUMBER DEFAULT 0) IS
QUERY_CUR CURS_TYPE ;
V_QUERY_SQL VARCHAR2(2000);
V_TABLE_NAME VARCHAR2(40);
V_SEGMENT_TYPE VARCHAR2(2000);
V_SIZE NUMBER(20,15);
BEGIN
V_QUERY_SQL := 'SELECT O.TABLE_NAME, O.SEGMENT_TYPE, ROUND(SUM(GB), 4) AS OGB FROM (
SELECT CASE WHEN INSTR(T.SEGMENT_TYPE, ''TABLE'') >0 THEN T.SEGMENT_NAME
ELSE ( SELECT I.TABLE_NAME FROM DBA_INDEXES I WHERE i.OWNER = '''||V_OWNER||''' and I.INDEX_NAME = T.SEGMENT_NAME)
END AS TABLE_NAME,
CASE WHEN INSTR(T.SEGMENT_TYPE, ''TABLE'') >0 THEN ''TABLE'' ELSE ''INDEX'' END AS SEGMENT_TYPE,
T.BYTES/1024/1024/1024 AS GB';
IF DBA_VIEW = 0 THEN
V_QUERY_SQL := V_QUERY_SQL || ' FROM USER_SEGMENTS T
WHERE 1=1 ' ;
ELSE
V_QUERY_SQL := V_QUERY_SQL || ' FROM DBA_SEGMENTS T
WHERE T.OWNER = '''||V_OWNER||''' ' ;
END IF ;
V_QUERY_SQL := V_QUERY_SQL || 'AND T.SEGMENT_TYPE IN (''TABLE'',''TABLE PARTITION'',''TABLE SUBPARTITION'',
''INDEX'',''INDEX PARTITION'',''INDEX SUBPARTITION'')
) O
WHERE 1=1 AND O.TABLE_NAME IN (SELECT TABLE_NAME FROM ZXC_QUERYCOUNT_TC WHERE TBL_SIZE IS NULL)
GROUP BY O.TABLE_NAME, O.SEGMENT_TYPE
ORDER BY O.TABLE_NAME, O.SEGMENT_TYPE DESC ';
DBMS_OUTPUT.put_line('V_QUERY_SQL IS >> ' || V_QUERY_SQL);
OPEN QUERY_CUR FOR V_QUERY_SQL;
LOOP
FETCH QUERY_CUR INTO V_TABLE_NAME, V_SEGMENT_TYPE, V_SIZE ;
EXIT WHEN QUERY_CUR%NOTFOUND;
IF INSTR(V_SEGMENT_TYPE, 'TABLE')>0 THEN
UPDATE ZXC_QUERYCOUNT_TC Z SET Z.TBL_SIZE = V_SIZE||'' WHERE TABLE_NAME = V_TABLE_NAME ;
COMMIT ;
ELSIF INSTR(V_SEGMENT_TYPE, 'INDEX')>0 THEN
UPDATE ZXC_QUERYCOUNT_TC Z SET Z.TBL_IDX_SIZE = V_SIZE||'' WHERE TABLE_NAME = V_TABLE_NAME ;
COMMIT ;
END IF;
END LOOP;
CLOSE QUERY_CUR;
END QUERY_TABLE_SIZE;
PROCEDURE QUERY_PART_COUNT IS
V_QUERYCOUNT_SQL VARCHAR2(2000);
V_DATA_COUNT NUMBER(20);
BEGIN
/* 只统计 分区表的分区count */
FOR CUR IN (SELECT T.TABLE_NAME, T.PART_NAME,T.SUBPART_NAME,T.SUBPART_COUNT FROM ZXC_QUERYCOUNT_LIST_TC T WHERE PART_DATA_COUNT IS NULL )
LOOP
V_DATA_COUNT := 0 ;
IF CUR.SUBPART_COUNT = 0 THEN
V_QUERYCOUNT_SQL := 'SELECT COUNT(*) FROM '||CUR.TABLE_NAME||' PARTITION('||CUR.PART_NAME||') ';
DBMS_OUTPUT.put_line('V_QUERYCOUNT_SQL IS >> ' || V_QUERYCOUNT_SQL);
EXECUTE IMMEDIATE V_QUERYCOUNT_SQL INTO V_DATA_COUNT ;
DBMS_OUTPUT.put_line('V_DATA_COUNT IS >> ' || V_DATA_COUNT);
UPDATE ZXC_QUERYCOUNT_LIST_TC Q SET Q.PART_DATA_COUNT = V_DATA_COUNT ||''
WHERE Q.TABLE_NAME = CUR.TABLE_NAME AND Q.PART_NAME = CUR.PART_NAME ;
COMMIT ;
ELSIF CUR.SUBPART_COUNT > 0 THEN
V_QUERYCOUNT_SQL := 'SELECT COUNT(*) FROM '||CUR.TABLE_NAME||' SUBPARTITION('||CUR.SUBPART_NAME||') ';
DBMS_OUTPUT.put_line('SUBPART COUNT >> V_QUERYCOUNT_SQL IS >> ' || V_QUERYCOUNT_SQL);
EXECUTE IMMEDIATE V_QUERYCOUNT_SQL INTO V_DATA_COUNT ;
DBMS_OUTPUT.put_line('SUBPART COUNT >> V_DATA_COUNT IS >> ' || V_DATA_COUNT);
UPDATE ZXC_QUERYCOUNT_LIST_TC Q SET Q.PART_DATA_COUNT = V_DATA_COUNT ||''
WHERE Q.TABLE_NAME = CUR.TABLE_NAME AND Q.PART_NAME = CUR.PART_NAME AND Q.SUBPART_NAME = CUR.SUBPART_NAME ;
COMMIT ;
END IF;
END LOOP;
END QUERY_PART_COUNT;
PROCEDURE QUERY_PART_SIZE(V_OWNER IN VARCHAR2, DBA_VIEW NUMBER DEFAULT 0) IS
QUERYSIZE_CUR CURS_TYPE ;
V_QUERYSIZE_SQL VARCHAR2(2000);
V_TABLE_NAME NUMBER(20);
V_SEGMENT_TYPE NUMBER(20,15);
V_SIZE NUMBER(20,15);
V_TABLE_SIZE NUMBER(20,15) := 0;
V_INDEX_SIZE NUMBER(20,15) := 0 ;
BEGIN
/* 只统计 分区表的分区存储 */
FOR CUR IN (SELECT T.TABLE_NAME, T.PART_NAME,T.SUBPART_NAME,T.SUBPART_COUNT FROM ZXC_QUERYCOUNT_LIST_TC T
WHERE PART_TBL_SIZE IS NULL OR PART_IDX_SIZE IS NULL )
LOOP
V_QUERYSIZE_SQL := 'SELECT O.TABLE_NAME, O.SEGMENT_TYPE, ROUND(SUM(GB), 4) AS OGB FROM (
SELECT CASE WHEN INSTR(T.SEGMENT_TYPE, ''TABLE'') >0 THEN T.SEGMENT_NAME
ELSE ( SELECT I.TABLE_NAME FROM DBA_INDEXES I WHERE I.INDEX_NAME = T.SEGMENT_NAME)
END AS TABLE_NAME,
CASE WHEN INSTR(T.SEGMENT_TYPE, ''TABLE'') >0 THEN ''TABLE'' ELSE ''INDEX'' END AS SEGMENT_TYPE,
T.PARTITION_NAME,
T.BYTES/1024/1024/1024 AS GB ' ;
IF DBA_VIEW = 0 THEN
V_QUERYSIZE_SQL := V_QUERYSIZE_SQL || ' FROM USER_SEGMENTS T
WHERE 1=1 ' ;
ELSE
V_QUERYSIZE_SQL := V_QUERYSIZE_SQL || ' FROM DBA_SEGMENTS T
WHERE T.OWNER = '''||V_OWNER||''' ' ;
END IF ;
V_QUERYSIZE_SQL := V_QUERYSIZE_SQL || '
AND ( t.SEGMENT_NAME = '''||CUR.TABLE_NAME||''' OR T.SEGMENT_NAME IN (
SELECT D.INDEX_NAME FROM DBA_INDEXES D WHERE D.TABLE_NAME = '''||CUR.TABLE_NAME||''' )
)
AND PARTITION_NAME = '''||CUR.PART_NAME||'''
AND T.SEGMENT_NAME NOT LIKE ''BIN%''
AND T.SEGMENT_TYPE IN (''TABLE'',''TABLE PARTITION'',''TABLE SUBPARTITION'',''INDEX'',''INDEX PARTITION'',''INDEX SUBPARTITION'')
) O
GROUP BY O.TABLE_NAME, O.SEGMENT_TYPE, O.PARTITION_NAME
ORDER BY O.TABLE_NAME, O.SEGMENT_TYPE DESC, O.PARTITION_NAME';
DBMS_OUTPUT.put_line('V_QUERYSIZE_SQL IS >> ' || V_QUERYSIZE_SQL);
OPEN QUERYSIZE_CUR FOR V_QUERYSIZE_SQL ;
LOOP
FETCH QUERYSIZE_CUR INTO V_TABLE_NAME, V_SEGMENT_TYPE, V_SIZE ;
EXIT WHEN QUERYSIZE_CUR%NOTFOUND;
IF INSTR(V_SEGMENT_TYPE, 'TABLE')>0 THEN
V_TABLE_SIZE := V_SIZE ;
ELSIF INSTR(V_SEGMENT_TYPE, 'INDEX')>0 THEN
V_INDEX_SIZE := V_SIZE ;
END IF;
END LOOP;
CLOSE QUERYSIZE_CUR ;
UPDATE ZXC_QUERYCOUNT_LIST_TC Q SET Q.PART_TBL_SIZE = V_TABLE_SIZE, Q.PART_IDX_SIZE = V_INDEX_SIZE
WHERE Q.TABLE_NAME = CUR.TABLE_NAME AND Q.PART_NAME = CUR.PART_NAME ;
COMMIT ;
END LOOP;
END QUERY_PART_SIZE;
BEGIN
NULL;
END;
/
工具函数
（1）字符串分割成集合，可用来循环使用
CREATE OR REPLACE TYPE TY_STR_SPLIT AS TABLE OF VARCHAR(4000);
-- 字符串分割返回集合
CREATE FUNCTION FN_SPLIT(P_STR IN VARCHAR2, P_DELIMITER IN VARCHAR2) RETURN TY_STR_SPLIT IS
L_RESULT TY_STR_SPLIT := TY_STR_SPLIT();
L_START INTEGER := 1 ;
L_DELIM_LENGHT INTEGER := length(P_DELIMITER) ;
L_SUBSTR INTEGER := 0 ;
BEGIN
IF P_STR IS NULL OR P_DELIMITER IS NULL THEN
RETURN L_RESULT;
end if;
WHILE L_START < LENGTH(P_STR) LOOP
L_SUBSTR := INSTR(P_STR, P_DELIMITER, L_START);
IF L_SUBSTR = 0 THEN
L_RESULT.extend(1);
L_RESULT(L_RESULT.LAST) := SUBSTR(P_STR, L_START);
L_START := LENGTH(P_STR) +1;
ELSE
L_RESULT.extend(1);
L_RESULT(L_RESULT.LAST) := SUBSTR(P_STR, L_START, L_SUBSTR - L_START);
L_START:= L_SUBSTR + L_DELIM_LENGHT;
END IF;
END LOOP;
RETURN L_RESULT;
END FN_SPLIT;
/
使用示例
-- 测试
SELECT FN_SPLIT('1-2-3-4','-')
FROM DUAL;
（3）休眠函数
-- 休眠函数
CREATE OR REPLACE FUNCTION SLEEP(SECONDS IN NUMBER) RETURN NUMBER IS
START_TIME NUMBER;
ELASPED_TIME NUMBER;
BEGIN
START_TIME := DBMS_UTILITY.GET_TIME();
ELASPED_TIME := 0;
LOOP
ELASPED_TIME := (DBMS_UTILITY.GET_TIME() - START_TIME) / 100;
EXIT WHEN ELASPED_TIME >= SECONDS;
END LOOP;
RETURN 0;
END;
/
使用案例
方式一: 在SELECT查询中使用
DECLARE
V_TIME NUMBER;
BEGIN
SELECT SLEEP(3) INTO V_TIME FROM DUAL;
END;
方式二：直接当成函数调用，使用返回值接收结果
DECLARE
V_TIME NUMBER;
BEGIN
V_TIME := SLEEP(3);
END;
（3）LONG 转 CHAR
-- ORACLE LONG 转 CHAR 函数
CREATE OR REPLACE FUNCTION LONG_TO_CHAR(
IN_COLUMN_NAME VARCHAR2,
IN_OWNER VARCHAR2,
IN_INDEX_NAME VARCHAR2,
IN_CONDITION VARCHAR2
)
RETURN VARCHAR AS
TEXT_STR VARCHAR2(32767);
SQL_STR VARCHAR2(2000);
BEGIN
SQL_STR := 'SELECT ' || IN_COLUMN_NAME || ' FROM ' || IN_OWNER || '.' || IN_INDEX_NAME ||
' T WHERE 1=1 ' || IN_CONDITION;
EXECUTE IMMEDIATE SQL_STR INTO TEXT_STR;
TEXT_STR := SUBSTR(TEXT_STR, 1, 32767);
RETURN TEXT_STR;
END;
/
CREATE OR REPLACE FUNCTION INDEX_COLUMN_EXPRESSION(
IN_TABLE_NAME VARCHAR2,
IN_INDEX_NAME VARCHAR2,
IN_COLUMN_POSITION VARCHAR2
)
RETURN VARCHAR AS
TEXT_STR VARCHAR2(32767);
SQL_STR VARCHAR2(2000);
BEGIN
SQL_STR := 'SELECT COLUMN_EXPRESSION
FROM USER_IND_EXPRESSIONS T WHERE T.TABLE_NAME =''' || IN_TABLE_NAME || '''
AND T.INDEX_NAME = ''' || IN_INDEX_NAME || '''
AND T.COLUMN_POSITION = ''' || IN_COLUMN_POSITION || ''' ';
EXECUTE IMMEDIATE SQL_STR INTO TEXT_STR;
TEXT_STR := SUBSTR(TEXT_STR, 1, 4000);
RETURN TEXT_STR;
END;
/
LONG_TO_CHAR函数使用案例
SELECT E.TABLE_NAME,
E.INDEX_NAME,
INDEX_COLUMN_EXPRESSION(E.TABLE_NAME, E.INDEX_NAME, E.COLUMN_POSITION) AS COLUMN_EXPRESSION
FROM USER_IND_EXPRESSIONS E;
INDEX_COLUMN_EXPRESSION函数使用案例
SELECT E.TABLE_NAME,
E.INDEX_NAME,
INDEX_COLUMN_EXPRESSION(E.TABLE_NAME, E.INDEX_NAME, E.COLUMN_POSITION) AS COLUMN_EXPRESSION
FROM USER_IND_EXPRESSIONS E;
（3）下划线转驼峰
CREATE OR REPLACE FUNCTION FN_CAMELCASE(P_FIELD_NAME IN VARCHAR2) RETURN VARCHAR2 IS
V_CAMELCASE VARCHAR2(32767);
V_NEXT_UPPERCASE BOOLEAN := FALSE;
BEGIN
V_CAMELCASE := LOWER(P_FIELD_NAME);
FOR I IN 1..LENGTH(V_CAMELCASE) LOOP
IF SUBSTR(V_CAMELCASE, I, 1) = '_' THEN
V_NEXT_UPPERCASE := TRUE;
ELSIF V_NEXT_UPPERCASE THEN
V_CAMELCASE := SUBSTR(V_CAMELCASE, 1, I - 2) || INITCAP(SUBSTR(V_CAMELCASE, I, 1)) || SUBSTR(V_CAMELCASE, I + 1);
V_NEXT_UPPERCASE := FALSE;
END IF;
END LOOP;
RETURN V_CAMELCASE;
END;
/
FN_CAMELCASE函数使用案例
SELECT FN_CAMELCASE('_hello_word_hi_camel_case') FROM DUAL;
结果是 HelloWordHiCamelCase
你