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;

 

posted @   每天进步多一点  阅读(104)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示