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; |
用户表，可以查询对应的用户信息 |
| 2 |
SELECT * FROM USER_TABLES; SELECT * FROM USER_ALL_TABLES; SELECT * FROM USER_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; |
表结构，可以查询表列详细信息 |
| 5 |
SELECT * FROM USER_CONS_COLUMNS; SELECT * FROM USER_CONS_COLUMNS; |
约束 |
| 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 "对象名[过程名|方法名]" SELECT OBJECT_NAME AS "对象名[过程名|方法名]" |
| 9 |
SELECT * FROM USER_TRIGGERS; SELECT * FROM USER_TRIGGER_COLS; SELECT * FROM USER_TRIGGER_ORDERING; |
触发器
SELECT TRIGGER_NAME AS "触发器名"
|