1、表字段注释、类型、大小select
ut.COLUMN_NAME,--字段名称
uc.comments,--字段注释
ut.DATA_TYPE,--字典类型
ut.DATA_LENGTH,--字典长度
ut.NULLABLE--是否为空
from user_tab_columns ut
inner JOIN user_col_comments uc
on ut.TABLE_NAME = uc.table_name and ut.COLUMN_NAME = uc.column_name
where ut.Table_Name='JX_TZS_PROJECT_LIST'
order by ut.column_name
2、获取表的索引创建语句SELECT DISTINCT T1.INDEX_NAME,T2.TABLE_NAME,REPLACE(dbms_lob.substr(dbms_metadata.get_ddl('INDEX', T1.INDEX_NAME))||';','"','') SQL_CONTENT
from user_indexes T1
INNER JOIN USER_IND_COLUMNS T2 ON T1.INDEX_NAME = T2.INDEX_NAME
WHERE T2.TABLE_NAME= 'BB'
3、创建、删除表索引的存储过程,用于datax抽取数据前删除索引,数据抽取完成后创建索引(有效提高数据抽取速度)CREATE OR REPLACE
PROCEDURE "PRO_RUN_DATAX_INDEXS"(TABLENAME VARCHAR2,OPEATE VARCHAR2)
authid current_user
AS
delsql VARCHAR2(500);
cresql VARCHAR2(2500);
sqlContent VARCHAR2(500);
num NUMBER;
BEGIN
-- routine body goes here, e.g.
-- CREATE TABLE RUN_DATAX_INDEXS (TABLE_NAME VARCHAR2(255 BYTE) NULL ,SQL_CONTENT VARCHAR2(2550 BYTE) NULL )
--表名不存在直接返回
SELECT COUNT(1) INTO num FROM USER_TABLES WHERE TABLE_NAME = ''||TABLENAME||'';
IF num = 0 THEN
DBMS_OUTPUT.PUT_LINE('NOT FOUND TABLENAME');
RETURN;
END IF;
IF OPEATE = 'DELETE' THEN
FOR ENTITY IN (SELECT DISTINCT T1.INDEX_NAME,T2.TABLE_NAME,T2.COLUMN_NAME,dbms_lob.substr(dbms_metadata.get_ddl('INDEX', T1.INDEX_NAME))SQL_CONTENT from user_indexes T1 INNER JOIN USER_IND_COLUMNS T2 ON T1.INDEX_NAME = T2.INDEX_NAME WHERE T2.TABLE_NAME = ''||TABLENAME||'')
LOOP
--添加已经删除了的索引记录(后续根据记录添加回来)
IF ENTITY.INDEX_NAME LIKE 'SYS_%' THEN
--主键索引
INSERT INTO RUN_DATAX_INDEXS(TABLE_NAME,SQL_CONTENT)VALUES(''||TABLENAME||'','alter table '||TABLENAME||' add primary key ('||ENTITY.COLUMN_NAME||')');
COMMIT;
delsql:=' alter table '||TABLENAME||' drop constraint '|| ENTITY.INDEX_NAME;
ELSE
--普通索引
INSERT INTO RUN_DATAX_INDEXS(TABLE_NAME,SQL_CONTENT)VALUES(''||TABLENAME||'',''||ENTITY.SQL_CONTENT||'');
COMMIT;
delsql:='drop index '|| ENTITY.INDEX_NAME;
END IF;
EXECUTE IMMEDIATE delsql;
COMMIT;
END LOOP;
ELSIF OPEATE = 'CREATE' THEN
FOR ENTITY IN(SELECT DISTINCT * FROM RUN_DATAX_INDEXS WHERE TABLE_NAME=''||TABLENAME||'')
LOOP
--添加索引或者主键
EXECUTE IMMEDIATE ENTITY.SQL_CONTENT;
COMMIT;
END LOOP;
--删除记录
DELETE FROM RUN_DATAX_INDEXS WHERE TABLE_NAME = ''||TABLENAME||'';
COMMIT;
END IF;
END PRO_RUN_DATAX_INDEXS;
CREATE OR REPLACE
PROCEDURE "PRO_RUN_DATAX_INDEXS"(TABLENAME VARCHAR2,OPEATE VARCHAR2)
authid current_user
AS
delsql VARCHAR2(500);
cresql VARCHAR2(2500);
sqlContent VARCHAR2(500);
num NUMBER;
contraintName VARCHAR2(200);
BEGIN
-- routine body goes here, e.g.
-- CREATE TABLE RUN_DATAX_INDEXS (TABLE_NAME VARCHAR2(255 BYTE) NULL ,SQL_CONTENT VARCHAR2(2550 BYTE) NULL )
--表名不存在直接返回
SELECT COUNT(1) INTO num FROM USER_TABLES WHERE TABLE_NAME = ''||TABLENAME||'';
IF num = 0 THEN
DBMS_OUTPUT.PUT_LINE('NOT FOUND TABLENAME');
RETURN;
END IF;
IF OPEATE = 'DELETE' THEN
FOR ENTITY IN (SELECT DISTINCT *
FROM RUN_DATAX_INDEXS_TABLE
WHERE TABLE_NAME = ''||TABLENAME||'')
LOOP
--索引是否存在
SELECT COUNT(1) INTO num
FROM user_indexes
WHERE INDEX_NAME = ''||ENTITY.INDEX_NAME||'';
IF num > 0 THEN
--添加已经删除了的索引记录(后续根据记录添加回来)
SELECT count(1) INTO num
FROM USER_CONS_COLUMNS T1
INNER JOIN USER_CONSTRAINTS T2 ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
WHERE T2.CONSTRAINT_TYPE = 'P'
AND T2.TABLE_NAME = ''||TABLENAME||''
AND T1.COLUMN_NAME = ''||ENTITY.COLUMN_NAME||'';
IF num > 0 THEN
SELECT T2.CONSTRAINT_NAME INTO contraintName
FROM USER_CONS_COLUMNS T1
INNER JOIN USER_CONSTRAINTS T2 ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
WHERE T2.CONSTRAINT_TYPE = 'P'
AND T2.TABLE_NAME = ''||TABLENAME||''
AND T1.COLUMN_NAME = ''||ENTITY.COLUMN_NAME||'';
--主键索引
INSERT INTO RUN_DATAX_INDEXS(TABLE_NAME,SQL_CONTENT)VALUES(''||TABLENAME||'','alter table '||TABLENAME||' add primary key ('||ENTITY.COLUMN_NAME||')');
COMMIT;
delsql:=' alter table '||TABLENAME||' drop constraint '|| contraintName;
IF ENTITY.INDEX_NAME NOT LIKE 'SYS_%' THEN
delsql:='drop index '||ENTITY.INDEX_NAME;
END IF;
ELSE
--普通索引
INSERT INTO RUN_DATAX_INDEXS(TABLE_NAME,SQL_CONTENT)VALUES(''||TABLENAME||'',''||ENTITY.SQL_CONTENT||'');
COMMIT;
delsql:='drop index '||ENTITY.INDEX_NAME;
END IF;
EXECUTE IMMEDIATE delsql;
DBMS_OUTPUT.PUT_LINE(delsql);
COMMIT;
END IF;
END LOOP;
ELSIF OPEATE = 'CREATE' THEN
FOR ENTITY IN(SELECT DISTINCT *
FROM RUN_DATAX_INDEXS
WHERE TABLE_NAME=''||TABLENAME||'')
LOOP
--添加索引或者主键
EXECUTE IMMEDIATE ENTITY.SQL_CONTENT;
COMMIT;
END LOOP;
--删除记录
DELETE FROM RUN_DATAX_INDEXS WHERE TABLE_NAME = ''||TABLENAME||'';
COMMIT;
END IF;
END PRO_RUN_DATAX_INDEXS;
--DROP TABLE RUN_DATAX_INDEXS_TABLE;
--CREATE TABLE RUN_DATAX_INDEXS_TABLE AS
--SELECT INDEX_NAME,TABLE_NAME,COLUMN_NAME,SQL_CONTENT
--FROM(
-- SELECT DISTINCT T1.INDEX_NAME,T2.TABLE_NAME,T2.COLUMN_NAME,dbms_lob.substr(dbms_metadata.get_ddl('INDEX', T1.INDEX_NAME))||';' SQL_CONTENT,ROW_NUMBER() OVER(PARTITION BY T1.INDEX_NAME ORDER BY T2.COLUMN_NAME DESC)RN
-- from user_indexes T1
-- INNER JOIN USER_IND_COLUMNS T2 ON T1.INDEX_NAME = T2.INDEX_NAME
--)
--WHERE RN = 1
4、查看表空间使用情况SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
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