oracle系統檢視表SQL語句整理

標籤：字元   asc   日誌組   標準   pfile   current   check   資料庫系統   efault   

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX開頭的絕大部分都是視圖-- DBA_TABLES意為DBA擁有的或可以訪問的所有的關係表。-- ALL_TABLES意為某一使用者擁有的或可以訪問的所有的關係表。-- USER_TABLES意為某一使用者所擁有的所有的關係表。-- 當某一使用者本身就為資料庫DBA時，DBA_TABLES與ALL_TABLES等價。-- DBA_TABLES >= ALL_TABLES >= USER_TABLES-- 需要注意的是在ORACLE資料庫中大小寫是敏感的，而此三表中資料預設都是大寫的，所以在進行查詢的時候注意小寫資料可能會造成資料無法查到。SELECT * FROM dba_views WHERE view_name LIKE ‘DBA%‘;SELECT * FROM dba_views WHERE view_name LIKE ‘ALL%‘;SELECT * FROM dba_views WHERE view_name LIKE ‘USER%‘;SELECT * FROM dba_views WHERE view_name LIKE ‘V_$%‘; -- 針對某個執行個體的視圖SELECT * FROM dba_views WHERE view_name LIKE ‘GV_$%‘; -- 全域視圖，針對多個執行個體環境SELECT * FROM dba_views WHERE view_name LIKE ‘SESSION%‘;SELECT * FROM dba_views WHERE view_name LIKE ‘INDEX%‘;SELECT count(1) FROM dba_tables;SELECT count(1) FROM all_tables;SELECT count(1) FROM user_tables;-- V$/GV$開頭的絕大部分都是V_$/GV_$表的別名SELECT * FROM dba_synonyms WHERE synonym_name LIKE ‘V$%‘;SELECT * FROM dba_synonyms WHERE synonym_name LIKE ‘GV$%‘;-- X$沒有對應的X_$SELECT * FROM dba_synonyms WHERE synonym_name LIKE ‘X$%‘;-- 比較常用的DBA開頭的視圖有select * from dba_users; --資料庫使用者資訊select * from dba_roles; --角色資訊select * from dba_segments; --表段資訊select * from dba_extents; --資料區資訊select * from dba_objects; --資料庫物件資訊select * from dba_lobs; --lob資料資訊select * from dba_tablespaces; --資料庫資料表空間資訊select * from dba_data_files; --資料檔案設定資訊select * from dba_temp_files; --臨時資料檔案資訊select * from dba_rollback_segs; --復原段資訊select * from dba_ts_quotas; --使用者資料表空間配額資訊select * from dba_free_space; --資料庫空閑空間資訊select * from dba_profiles; --資料庫使用者資源限制資訊select * from dba_sys_privs; --使用者的系統許可權資訊select * from dba_tab_privs; --使用者具有的對象許可權資訊select * from dba_col_privs; --使用者具有的列對象許可權資訊select * from dba_role_privs; --使用者具有的角色資訊select * from dba_audit_trail; --審計追蹤記錄資訊select * from dba_stmt_audit_opts; --審計設定資訊select * from dba_audit_object; --對象審計結果資訊select * from dba_audit_session; --會話審計結果資訊select * from dba_indexes; --使用者模式的索引資訊-- 比較常用的ALL開頭的視圖有select * from all_users; --資料庫所有使用者的資訊select * from all_objects; --資料庫所有的對象的資訊select * from all_def_audit_opts; --所有預設的審計設定資訊select * from all_tables; --所有的表對象資訊select * from all_indexes; --所有的資料庫物件索引的資訊select * from all_tab_comments; --查詢所有使用者的表,視圖等select * from all_col_comments; --查詢所有使用者的表的列名和注釋.select * from all_tab_columns; --查詢所有使用者的表的列名等資訊(詳細但是沒有備忘)-- 比較常用的ALL開頭的視圖有select * from user_objects; --使用者物件資訊select * from user_source; --資料庫使用者的所有資來源物件資訊select * from user_segments; --使用者的表段資訊select * from user_tables; --使用者的表對象資訊select * from user_tab_columns; --使用者的表列資訊select * from user_constraints; --使用者的對象約束資訊select * from user_sys_privs; --目前使用者的系統許可權資訊select * from user_tab_privs; --目前使用者的對象許可權資訊select * from user_col_privs; --目前使用者的表列許可權資訊select * from user_col_comments; -- 查詢本使用者的表的列名和注釋select * from user_role_privs; --目前使用者的角色許可權資訊select * from user_indexes; --使用者的索引資訊select * from user_ind_columns; --使用者的索引對應的表列資訊select * from user_cons_columns; --使用者的約束對應的表列資訊select * from user_clusters; --使用者的所有簇資訊select * from user_clu_columns; --使用者的簇所包含的內容資訊select * from user_cluster_hash_expressions; --散列簇的資訊-- 比較常用的V$開頭的別名有select * from v$database; --資料庫資訊select * from v$datafile; --資料檔案資訊select * from v$controlfile; --控制檔案資訊select * from v$logfile; --重做日誌資訊select * from v$instance; --資料庫執行個體資訊select * from v$log; --日誌組資訊select * from v$loghist; --日誌曆史資訊select * from v$sga; --資料庫SGA資訊select * from v$parameter; --初始化參數資訊select * from v$process; --資料庫伺服器進程資訊select * from v$bgprocess; --資料庫後台進程資訊select * from v$controlfile_record_section; --控制檔案記載的各部分資訊select * from v$thread; --線程資訊select * from v$datafile_header; --資料檔案頭所記載的資訊select * from v$archived_log; --歸檔日誌資訊select * from v$archive_dest; --歸檔日誌的設定資訊select * from v$logmnr_contents; --歸檔日誌分析的DML DDL結果資訊select * from v$logmnr_dictionary; --日誌分析的字典檔案資訊select * from v$logmnr_logs; --日誌分析的日誌列表資訊select * from v$tablespace; --資料表空間資訊select * from v$tempfile; --臨時檔案資訊select * from v$filestat; --資料檔案的I/O統計資訊select * from v$undostat; --Undo資料資訊select * from v$rollname; --線上復原段資訊select * from v$session; --會話資訊select * from v$transaction; --事務資訊select * from v$rollstat; --復原段統計資訊select * from v$pwfile_users; --特權使用者資訊select * from v$sqlarea; --當前查詢過的sql語句訪問過的資源及相關的資訊select * from v$sql; --與v$sqlarea基本相同的相關資訊select * from v$sysstat; --資料庫系統狀態資訊-- 比較常用的SESSION開頭的視圖有select * from session_roles; --會話的角色資訊select * from session_privs; --會話的許可權資訊-- 比較常用的INDEX開頭的視圖有select * from index_stats; --索引的設定和儲存資訊-- 偽表，參考oracle 中 dual 詳解：http://blog.csdn.net/ozhouhui/article/details/7935196select * from dual; --系統偽列表資訊select sysdate from dual; --可將Sysdate視為一個其結果為當前日期和時間的函數，在任何可以使用Oracle函數的地方都可以使用Sysdate。也可以將它視為每個表的一個隱藏的列或偽列。select current_date from dual; --報告會話的時區中的系統日期。註：可以設定自己的時區，以區別於資料庫的時區。select SYSTIMESTAMP from dual; --報告TIMESTAMP資料類型格式的系統日期。-- 系統許可權-- GRANTEE 接受該許可權的使用者名稱 -- OWNER 對象的擁有者 -- GRANTOR 賦予許可權的使用者SELECT * FROM dba_sys_privs WHERE grantee = ‘SYS‘;SELECT * FROM dba_sys_privs WHERE grantee = ‘CONNECT‘;SELECT * FROM dba_sys_privs WHERE grantee = ‘RESOURCE‘;-- 角色許可權-- 查看某個使用者有哪些角色select * from dba_role_privs where grantee=‘SYS‘;-- 查看某個角色被賦予了哪些使用者SELECT * FROM dba_role_privs WHERE granted_role = ‘DBA‘;-- 對象許可權SELECT * FROM dba_tab_privs;-- 授予某個使用者某些角色GRANT connect,resource TO ‘USER‘;GRANT dba to ‘USER‘; --給普通使用者授予dba角色時，要重新串連才會生效REVOKE dba to ‘USER‘;-- 直接授予某個使用者某些許可權GRANT CREATE VIEW TO ‘USER‘;-- 查看某個系統使用者是否有SYSDBA或者SYSOPER許可權-- oracle:DBA,SYSDBA,SYSOPER三者的區別：http://blog.chinaunix.net/uid-22457844-id-3045741.htmlselect * from V$PWFILE_USERS;-- 鎖定、解鎖使用者SELECT * FROM dba_users WHERE username = ‘SCOTT‘;ALTER USER SCOTT account LOCK; --鎖定使用者ALTER USER SCOTT account UNLOCK; --解鎖使用者COMMIT;-- oracle10g 修改使用者密碼: http://blog.163.com/benbenfafa_88/blog/static/64930162200972594612972/-- User Default Password Check in Oracle 11g: http://www.dbform.com/html/2009/673.htmlSELECT password FROM dba_users WHERE username = ‘SCOTT‘;alter user SCOTT identified by new_password; --修改使用者密碼-- SERVICE_NAMES: http://docs.oracle.com/database/121/REFRN/GUID-AC956707-D568-4F8A-BF2E-99BA41E0A64F.htm#REFRN10194SELECT * FROM global_name; -- 查看oracle的全域資料庫名SELECT * FROM v$database; -- 查看資料庫名 show parameter db_name;-- 資料庫執行個體名對應著SID-- SID: http://docs.oracle.com/database/121/LADBI/glossary.htm#LADBI8021-- linux下在配置oracle環境變數的情況可以使用 echo $ORACLE_SID,如果沒有可以使用ps -ef |grep oracle 來查詢，結果中的xxxx就是對應的SID。-- oracle    2548     1  0 Aug17 ?        00:00:00 ora_pmon_xxxx-- 在windows環境下,oracle是以後台服務的方式被管理的,所以看"控制台->管理工具->服務 裡面的名稱:"OracleServiceORCL",則ORCL就是sid;SELECT * FROM v$instance; --查看資料庫執行個體名 show parameter instance_name;select instance from v$thread;-- show parameter是oracle的命令，不是標準SQL語句-- 可以在sqlplus或者pl/sql dev的命令視窗執行-- show parameter aaaa;等價於SELECT * FROM v$parameter WHERE name like ‘%aaaa%‘;SELECT * FROM v$parameter WHERE name like ‘%name%‘; --等價於show parameter name;select * from v$parameter where name like ‘%db_domain%‘; --查詢資料庫網域名稱select username from all_users where username like ‘%SCOTT%‘;drop user SCOTT cascade;commit;-- ERROR at line 1:-- ORA-01940: cannot drop a user that is currently connectedselect ‘ALTER SYSTEM KILL SESSION ‘||‘‘‘‘||SID||‘,‘||SERIAL#||‘‘‘‘||‘;‘ as KILLER from v$session where username=‘SCOTT‘;-- KILLER-- ALTER SYSTEM KILL SESSION ‘363,35‘;-- ALTER SYSTEM KILL SESSION ‘364,51‘;commit;select * from dba_roles where role like ‘%CONNECT%‘;drop role CONNECT;commit;select * from dba_tablespaces where tablespace_name like ‘EXAMPLE‘;drop tablespace EXAMPLE including contents and datafiles cascade constraints ;-- including contents 刪除資料表空間中的內容，如果刪除資料表空間之前資料表空間中有內容，而未加此參數，資料表空間刪不掉，所以習慣性的加此參數。-- including datafiles 刪除資料表空間中的資料檔案。-- cascade constraints 同時刪除 tablespace 中表的外鍵參照。-- 如何建立dblink和視圖-- http://docs.oracle.com/database/121/SQLRF/statements_5006.htm#i2061505-- 如果需要建立全域 DBLink，則需要先確定使用者有建立 dblink 的許可權：select * from user_sys_privs where privilege like upper(‘%DATABASE LINK%‘);-- 如果沒有，則需要使用 sysdba 角色給使用者賦權：grant create public database link to dbusername;-- 如果建立全域 dblink，必須使用 systm 或 sys 使用者，在 database 前加 public。create /* public */ database link dblink1connect to dbusername identified by dbpasswordusing ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))‘;-- 建立dblink後，就可以直接在dblink上建立視圖create or replace view cptp as (select SJDH from [email protected]); drop view cptp;-- 鎖表查詢SQLSELECT object_name, machine, s.sid, s.serial#FROM gv$locked_object l, dba_objects o, gv$session sWHERE l.object_id　= o.object_idAND l.session_id = s.sid;-- 解除鎖表alter system kill session ‘sid, serial#‘;-- 備份某個表create table new_table as select * from old_table;-- 查看資料庫是否在rac環境的叢集中的show parameter cluster_database;select * from v$parameter where name = ‘cluster_database‘;-- 列操作-- 增加和修改列不需要加關鍵字COLUMN-- 刪除單列的話，一定要加COLUMN，刪除多列的時候，不能加COLUMN關鍵字-- 增加一列alter table emp4 add test varchar2(10);-- 修改一列alter table emp4 modify test varchar2(20);-- 刪除一列alter table emp4 drop column test;-- 增加多列alter table emp4 add (test varchar2(10),test2 number);-- 修改多列alter table emp4 modify (test varchar2(20),test2 varchar2(20));-- 刪除多列alter table emp4 drop (test,test2);-- Windows下以管理員身份啟動資料庫net start oracleserviceorcl -- 後面的orcl是你安裝的資料庫執行個體名net start oracleoradb11g_home1tnslistener --非必須-- linux下以sysdba使用者登入，然後啟動資料庫sqlplus / as sysdbastartup-- sqlplus登陸方式sqlplus / as sysdba --以作業系統許可權認證的oracle sys管理員登陸sqlplus /nologconn / as sysdba --以作業系統許可權認證的oracle sys管理員登陸sqlplus sys/[email protected] as sysdba --以sys使用者登陸必須使用as sysdbasqlplus /nolog --不在cmd或者teminal當中暴露密碼的登陸方式conn sys/password as sysdbasqlplus --不顯露密碼的方式登陸Enter user-name：sysEnter password：password as sysdba --以sys使用者登陸的話 必須要加上as sysdba子句sqlplus scott/[email protected] --非管理使用者登陸desc v$database; --查詢v$database資料庫的表結構--在sqlplus中執行sql指令碼，下面兩種方式都可以START file_name@file_name--判斷表是否存在，如果存在則刪除declare      num   number;begin      select count(1) into num from all_tables where TABLE_NAME = ‘EMP‘ and OWNER=‘SCOTT‘;      if   num=1   then          execute immediate ‘drop table EMP‘;      end   if;end;/--建立表CREATE TABLE EMP       (EMPNO NUMBER(4) NOT NULL,        ENAME VARCHAR2(10),        JOB VARCHAR2(9),        MGR NUMBER(4),        HIREDATE DATE,        SAL NUMBER(7, 2),        COMM NUMBER(7, 2),        DEPTNO NUMBER(2));可以將上述預存程序載入到每一個create table前面。--ORACLE 判斷序列是否存在,如果存在就刪除declare V_NUM number;BEGIN  ----多次刪除時，每次都將v_num設定成為0    V_NUM := 0;    ----判斷序列 seq_name_1 是否存在（區分大小寫）    select count(0) into V_NUM from user_sequences where sequence_name = ‘SEQ_BUSINESS_PROCESS_INDEX_ID‘;    ----如果存在立即刪除    if V_NUM > 0 then    execute immediate ‘DROP SEQUENCE  SEQ_BUSINESS_PROCESS_INDEX_ID‘;    end if;END;-- 設定sqlplus模式顯示總行數show pagesize; --查看當前的pagesizeset pagesize 300;-- 設定sqlplus模式顯示行寬度show linesize; --查看當前的linesizeset linesize 300;-- 修改安裝目錄glogin.sql檔案才能保證之前的設定永久生效set pagesize 300;set linesize 300;-- 刪除表對象select ‘drop table ‘||segment_name from dba_segments where owner=‘VPMUSER‘ and segment_type=‘TABLE‘;-- 建立表對象select‘create table ‘||segment_name || ‘ as select * from ‘||segment_name ||‘@DBLINK‘from dba_segments where owner=‘VPMUSER‘ and segment_type=‘TABLE‘;-- 檢查表是否完整匯入select segment_name from [email protected] where owner=‘VPMUSER‘ and segment_type=‘TABLE‘and (segment_name not like ‘BIN$%‘and segment_name not like ‘%201%‘)minusselect segment_name from dba_segments where owner=‘VPMUSER‘ and segment_type=‘TABLE‘  and segment_name not like ‘BIN$%‘--查詢使用者所有表的語句1select t.table_name,t.comments from user_tab_comments t--查詢使用者所有表的語句2:select r1, r2, r3, r5from (select a.table_name r1, a.column_name r2, a.comments r3          from user_col_comments a),       (select t.table_name r4, t.comments r5 from user_tab_comments t)where r4 = r1-- 尋找表的所有索引（包括索引名，類型，構成列）：select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查詢的表-- 尋找表的主鍵（包括名稱，構成列）：select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘P‘ and au.table_name = 要查詢的表-- 尋找表的唯一性限制式（包括名稱，構成列）：select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘U‘ and au.table_name = 要查詢的表-- 尋找表的外鍵（包括名稱，參考資料表的表名和對應的鍵名，下面是分成多步查詢）：select * from user_constraints c where c.constraint_type = ‘R‘ and c.table_name = 要查詢的表-- 查詢外鍵約束的列名：select * from user_cons_columns cl where cl.constraint_name = 外鍵名稱-- 查詢參考資料表的鍵的列名：select * from user_cons_columns cl where cl.constraint_name = 外鍵參考資料表的鍵名-- 查詢表的所有列及其屬性select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查詢的表    --備份表資料create table emp as select * from scott.emp--還原表資料insert into emp select * from scott.emp    --查看已經執行過的sql這些是存在共用池中的，使用者名稱需要大寫，必須具有DBA 的許可權select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in (‘使用者名稱‘) order by t.LAST_ACTIVE_TIME desc        --ORACLE11G 字元集更改（這裡更改為AL32UTF8）sqlplus sys as sysdba--執行下面命令，有可能造成資料庫中已有資料混亂的情況，所以在進行操作前，要進行資料庫的備份操作shutdown immediate;STARTUP MOUNT;ALTER SESSION SET SQL_TRACE=TRUE;ALTER SYSTEM ENABLE RESTRICTED SESSION;ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;ALTER SYSTEM SET AQ_TM_PROCESSES=0;ALTER DATABASE OPEN;ALTER DATABASE character set INTERNAL_USE AL32UTF8;ALTER SESSION SET SQL_TRACE=FALSE;shutdown immediate;startup;--察看 NLS_LANG 資訊：SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE ‘%CHARACTERSET‘;UPDATE STAFFSET MODIFY_TIME = TO_DATE(‘2016/04/22 00:01:00‘, ‘yyyy/MM/dd hh24:mi:ss‘)WHERE MODIFY_TIME < TO_DATE(‘2016/04/22 00:01:00‘, ‘yyyy/MM/dd hh24:mi:ss‘);UPDATE STAFFSET MODIFY_TIME = TO_TIMESTAMP(‘19-03-2008 02:36:00.360000‘, ‘dd-MM-yyyy hh24:mi:ss.ff‘)WHERE STAFF_ID = ‘01‘;

　　

效能分析SQL：

SELECT    "SQL_FULLTEXT",    "LAST_ACTIVE_TIME",    "CPU_TIME",    "ELAPSED_TIME",    "EXECUTIONS",    ROUND(ELAPSED_TIME/1000000/EXECUTIONS,3) AS perSQLTimeFROM    v$sqlWHERE    EXECUTIONS !=0AND rownum<500

　　

oracle系統檢視表SQL語句整理