Oracle中常用的系统表

1、如何快速修改用户中涉及多个表中某个字段类型

1）方法一：

SELECT 'alter table '||TABLE_NAME||' modify '||COLUMN_NAME||' VARCHAR2('||DATA_LENGTH||');' --要alter的sql
      ,T.* 
  FROM dba_tab_cols T 
 WHERE OWNER = '所属库的用户' 
   AND DATA_TYPE='字段类型' and COLUMN_NAME IN ('字段');

2）方法二：

BEGIN
  FOR r IN (
    select TABLE_NAME from user_tables WHERE OWNER = '所属库的用户' AND DATA_TYPE='字段类型' and COLUMN_NAME IN ('字段')
  ) LOOP
    execute immediate 'alter table '||r.TABLE_NAME||' modify '||r.COLUMN_NAME||' VARCHAR2('||r.DATA_LENGTH||')';
  END LOOP;
END;

 2、系统表

序号 表名 说明
1 
SELECT * FROM USER_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM DBA_USERS;
 用户表，可以查询对应的用户信息
2 
SELECT * FROM USER_TABLES;
SELECT * FROM USER_ALL_TABLES;
SELECT * FROM USER_TAB_COMMENTS;

SELECT * FROM DBA_TABLES;
SELECT * FROM DBA_ALL_TABLES;
SELECT * FROM DBA_TAB_COMMENTS;
 数据表，可以查询表名，表空间，表类型
3 
SELECT * FROM USER_TABLESPACES;
SELECT * FROM DBA_TABLESPACES;
 表空间
4 
SELECT * FROM USER_TAB_COLUMNS;
SELECT * FROM USER_TAB_COLS;
SELECT * FROM USER_COL_COMMENTS;

SELECT * FROM USER_TAB_COLUMNS;
SELECT * FROM USER_TAB_COLS;
SELECT * FROM USER_COL_COMMENTS;
 表结构，可以查询表列详细信息
5 
SELECT * FROM USER_CONS_COLUMNS;
SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM USER_CONS_COLUMNS;
SELECT * FROM USER_CONSTRAINTS;
 约束
6  
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;
 索引
 7 
SELECT * FROM USER_VIEWS;
SELECT * FROM USER_MVIEWS;
 视图
8 
SELECT * FROM USER_PROCEDURES;
SELECT * FROM USER_STORED_SETTINGS;

存储过程、存储函数

SELECT OBJECT_NAME AS "对象名[过程名|方法名]"
, PROCEDURE_NAME AS "过程名"
, OBJECT_TYPE AS "对象类型[PROCEDURE|FUNCTION]"
FROM SYS.USER_PROCEDURES t
WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION');

SELECT OBJECT_NAME AS "对象名[过程名|方法名]"
, PARAM_VALUE AS "参数值"
, OBJECT_NAME AS "对象名[存储函数]" 
FROM SYS.USER_STORED_SETTINGS t;
9 
SELECT * FROM USER_TRIGGERS;
SELECT * FROM USER_TRIGGER_COLS;
SELECT * FROM USER_TRIGGER_ORDERING;

触发器

SELECT TRIGGER_NAME AS "触发器名"
, TRIGGER_TYPE AS "触发类型"
, TRIGGERING_EVENT AS "触发事件"
, TABLE_OWNER AS "表的主人"
, BASE_OBJECT_TYPE AS "基本对象类型"
, TABLE_NAME AS "表名"
, COLUMN_NAME AS "列名" 
FROM SYS.USER_TRIGGERS t;
posted @ 2023-04-25 15:17  Faith_zhang  阅读(381)  评论(0)  编辑  收藏  举报