oracle查表字段长度,ORACLE表字段注释、类型、大小

最新推荐文章于 2025-06-18 15:46:25 发布

转载最新推荐文章于 2025-06-18 15:46:25 发布 · 2.1k 阅读

文章标签：

#oracle查表字段长度

本文介绍Oracle数据库中如何通过SQL语句查询表字段信息及索引详情，并提供了一种利用存储过程来实现表索引的批量删除与重建的方法，以优化数据抽取过程。此外，还展示了如何监控表空间的使用情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

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 "使用率 %"