ORACLE系統表總結
ORACLE系統表處理
1.取得指定使用者的所有表名：
2.取得指定使用者的所有視圖名稱：
oracle系統表查詢
1.使用者：
select username from dba_users;
select username from dba_users;
2.改密碼
alter user spgroup identified by spgtest;
alter user spgroup identified by spgtest;
3.表空間:
select * from dba_data_files;
/*表空間*/
select * from dba_tablespaces;
/*空閑表空間*/
select tablespace_name,sum(bytes), sum(blocks) from dba_free_space group by tablespace_name;
/*表空間對應的資料檔案*/
select * from dba_data_files where tablespace_name='RBS';
select * from dba_segments where tablespace_name='INDEXS';
select * from dba_data_files;
/*表空間*/
select * from dba_tablespaces;
/*空閑表空間*/
select tablespace_name,sum(bytes), sum(blocks) from dba_free_space group by tablespace_name;
/*表空間對應的資料檔案*/
select * from dba_data_files where tablespace_name='RBS';
select * from dba_segments where tablespace_name='INDEXS';
4.資料庫對象
select * from dba_objects;
CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
select * from dba_objects;
CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
5.表
select * from dba_tables;
/*表使用的extent的資訊。segment_type='ROLLBACK'檢視復原段的空間配置設定資訊*/
select extent_id,bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE' order by extent_id;
select distinct table_name from user_tab_columns where column_name='SO_TYPE_ID';
select * from dba_tables;
/*表使用的extent的資訊。segment_type='ROLLBACK'檢視復原段的空間配置設定資訊*/
select extent_id,bytes from dba_extents where segment_name='CUSTOMERS' and segment_type='TABLE' order by extent_id;
select distinct table_name from user_tab_columns where column_name='SO_TYPE_ID';
6.索引
/*索引，包括主鍵索引*/
select * from dba_indexes;
/*索引列 */
select * from dba_ind_columns;
/*聯接使用*/
select i.index_name,i.uniqueness,c.column_name
from user_indexes i,user_ind_columns c
where i.index_name=c.index_name
and i.table_name ='ACC_NBR';
/*索引，包括主鍵索引*/
select * from dba_indexes;
/*索引列*/
select * from dba_ind_columns;
/*聯接使用*/
select i.index_name,i.uniqueness,c.column_name
from user_indexes i,user_ind_columns c
where i.index_name=c.index_name
and i.table_name ='ACC_NBR';
7.序列
select * from dba_sequences;
select * from dba_sequences;
8.視圖
select * from dba_views
select * from all_views
/*text字段 可用于查詢視圖生成的腳本*/
select * from dba_views
select * from all_views
/*text字段 可用于查詢視圖生成的腳本*/
9.聚簇
select * from dba_clusters
select * from dba_clusters
`0.快照
select * from dba_snapshots
select * from dba_snapshots
/*快照、分區應存在相應的表空間*/
11.同義詞
select * from dba_synonyms
select * from dba_synonyms
//if owner is PUBLIC,then the synonyms is a public synonym.
//if owner is one of users,then the synonyms is a private synonym
12.資料庫鍊
select * from dba_db_links
select * from dba_db_links
/*在spbase下建資料庫鍊:*/
create database link dbl_spnew
connect to spnew identified by spnew using 'jhhx';
insert into select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';
create database link dbl_spnew
connect to spnew identified by spnew using 'jhhx';
insert into select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';
13.觸發器
select * from dba_trigers;
select * from dba_trigers;
/*
存儲過程，函數從dba_objects查找
其文本：select text from user_source where name='BOOK_SP_EXAMPLE';
建立出錯：select * from user_errors
oracle總是将存儲過程，函數等軟體放在SYSTEM表空間。
*/
14.限制
（1）限制是和表關聯的，可在create table或alter table table_name add/drop/modify來建立、修改、删除限制.
/*可以臨時禁止限制，如：*/
alter table book_example disable constraint book_example_1;
alter table book_example enable constraint book_example_1;
alter table book_example disable constraint book_example_1;
alter table book_example enable constraint book_example_1;
（2）主鍵和外鍵被稱為表限制，而not null和unique之類的限制被稱為列限制。通常将主鍵和外鍵作為單獨的命名限制放在字段清單下面，而列限制可放在列定義的同一行，這樣更具有可讀性
（3）列限制可從表定義看出，即describe;表限制即主鍵和外鍵，可從dba_constraints和dba_cons_columns 查。
select * from user_constraints where table_name='BOOK_EXAMPLE';
select owner,CONSTRAINT_NAME,TABLE_NAME from user_constraints where constraint_type='R' order by table_name;
select * from user_constraints where table_name='BOOK_EXAMPLE';
select owner,CONSTRAINT_NAME,TABLE_NAME from user_constraints where constraint_type='R' order by table_name;
（4）定義限制可以無名（系統自動生成限制名）和自己定義限制名（特别是主鍵、外鍵） 如：
create table book_example (identifier number not null);
create table book_example (identifier number constranit book_example_1 not null);
create table book_example (identifier number not null);
create table book_example (identifier number constranit book_example_1 not null);
15、復原段：
在所有的修改結果存入磁盤前，復原段中保持恢複該事務所需的全部資訊，必須以資料庫發生的事務來相應确定其大小（DML語句才可復原，create,drop,truncate等DDL不能復原）。
/*復原段數量=并發事務/4，但不能超過50；使每個復原段大小足夠處理一個完整的事務；*/
create rollback segment r05 tablespace rbs;
create rollback segment rbs_cvt tablespace rbs storage(initial M next k);
create rollback segment r05 tablespace rbs;
create rollback segment rbs_cvt tablespace rbs storage(initial M next k);
/*使復原段線上*/
alter rollback segment r04 online;
alter rollback segment r04 online;
/*用dba_extents,v$rollback_segs監測復原段的大小和動态增長。*/
/*復原段的區間資訊*/
select * from dba_extents where segment_type='ROLLBACK' and segment_name='RB1';
select * from dba_extents where segment_type='ROLLBACK' and segment_name='RB1';
/*復原段的段資訊,其中bytes顯示目前復原段的位元組數*/
select * from dba_segments where segment_type='ROLLBACK' and segment_name='RB1';
select * from dba_segments where segment_type='ROLLBACK' and segment_name='RB1';
/*為事物指定回歸段*/
set transaction use rollback segment rbs_cvt
set transaction use rollback segment rbs_cvt
/*針對bytes可以使用復原段回縮。*/
alter rollback segment rbs_cvt shrink;
select bytes,extents,max_extents from dba_segments where segment_type='ROLLBACK' and segment_name='RBS_CVT';
alter rollback segment rbs_cvt shrink;
select bytes,extents,max_extents from dba_segments where segment_type='ROLLBACK' and segment_name='RBS_CVT';
/*復原段的目前狀态資訊*/
select * from dba_rollback_segs where segment_name='RB1';
select * from dba_rollback_segs where segment_name='RB1';
/*比多復原段狀态status，復原段所屬執行個體instance_num查優化值optimal*/
select n.name,s.optsize from v$rollname n,v$rollstat s where n.usn=s.usn;
select n.name,s.optsize from v$rollname n,v$rollstat s where n.usn=s.usn;
/*復原段中的資料*/
set transaction use rollback segment rb1;/*復原段名*/
select n.name,s.writes from v$rollname n,v$rollstat s where n.usn=s.usn;
set transaction use rollback segment rb1;/*復原段名*/
select n.name,s.writes from v$rollname n,v$rollstat s where n.usn=s.usn;
/*
當事務處理完畢，再次查詢$rollstat，比較writes(復原段條目位元組數)內插補點，可确定事務的大小。
*/
/*查詢復原段中的事務*/
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'Os User' format a10
column te heading 'Terminal' format a10
select r.name rr,nvl(s.username,'no transaction') us,s.osuser os,s.terminal te from v$lock l,v$session s,v$rollname r
where l.sid=s.sid(+)
and trunc(l.id1/)=R.USN and l.type='TX' and l.lmode= order by r.name;
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'Os User' format a10
column te heading 'Terminal' format a10
select r.name rr,nvl(s.username,'no transaction') us,s.osuser os,s.terminal te from v$lock l,v$session s,v$rollname r
where l.sid=s.sid(+)
and trunc(l.id1/)=R.USN and l.type='TX' and l.lmode= order by r.name;
16、作業
查詢作業資訊
select job,broken,next_date,interval,what from user_jobs;
select job,broken,next_date,interval,what from dba_jobs;
select job,broken,next_date,interval,what from user_jobs;
select job,broken,next_date,interval,what from dba_jobs;
查詢正在運作的作業
select * from dba_jobs_running;
select * from dba_jobs_running;
使用包
/*加入作業。間隔10秒鐘 */
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (10/(24*60*60))')
/*加入作業。間隔11分鐘使用包exec dbms_job.remove(21)删除21号作業。*/
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (11/(24*60))')
/*加入作業。間隔10秒鐘*/
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (10/(24*60*60))')
/*加入作業。間隔11分鐘使用包exec dbms_job.remove(21)删除21号作業。*/
exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (11/(24*60))')
17.批注:
ALL_COL_COMMENTS
![MySQL安裝時常見問題和解決方案【詳解】[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)