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;
约束
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;

 

热门相关:超武穿梭   刺客之王   网游之逆天飞扬   重生之至尊千金   名门天后:重生国民千金