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、系統表


SELECT * FROM USER_USERS;
SELECT * FROM ALL_USERS;SELECT * FROM DBA_USERS;
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;
SELECT * FROM USER_TABLESPACES;
SELECT * FROM DBA_TABLESPACES;
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;
SELECT * FROM USER_CONS_COLUMNS;SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;
SELECT * FROM USER_VIEWS;
SELECT * FROM USER_MVIEWS;
SELECT * FROM USER_PROCEDURES;
SELECT * FROM USER_STORED_SETTINGS;
SELECT * FROM USER_TRIGGERS;
SELECT * FROM USER_TRIGGER_COLS;
SELECT * FROM USER_TRIGGER_ORDERING;

不管在什么地方上班，請記住：作業不養閑人，團隊不養懶人，入一行先別惦記著賺錢，先學著讓自己值錢，賺不到錢賺知識；賺不到知識賺經歷；賺不到經歷賺閱歷；以上都賺到了就不可能賺不到錢，只有先改變自己的態度，才能改變人生的高度，讓一個人迷茫的原因只有一個，那就是本該拼搏的年紀，卻想的太多，做的太少，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551257.html

標籤：其他

上一篇：數倉實踐丨主動預防-DWS關鍵工具安裝確認

下一篇：返回列表

序號	表名	說明
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;