oracle系統表處理
1.取得指定使用者的所有表名：
範例**
1. selectowneras"物件所有者",object_nameas"表名",object_idas"物件編號"fromdba_objectswhereowner='raxnyb'andobject_type='table'orderbyowner,object_type;
2. 或
3. owneras"物件所有者",table_nameas"表名"fromdba_tableswhereowner='raxnyb'orderbyowner,table_name;
select owner as "物件所有者",object_name as "表名",object_id as "物件編號" from dba_objects where owner = 'raxnyb' and object_type = 'table' order by owner,object_type;
或owner as "物件所有者",table_name as "表名" from dba_tables where owner = 'raxnyb' order by owner,table_name;
2.取得指定使用者的所有檢視名稱：
範例**
1. selectowneras"物件所有者",view_nameas"檢視名稱"fromdba_viewswhereowner='raxnyb'orderbyowner,view_name;
select owner as "物件所有者",view_name as "檢視名稱" from dba_views where owner = 'raxnyb' order by owner,view_name;
oracle系統表查詢
1.使用者：
範例**
1. selectusernamefromdba_users;
select username from dba_users;
改口令範例**
1. alteruserspgroupidentifiedbyspgtest;
alter user spgroup identified by spgtest;
2.表空間:
範例**
1. select*fromdba_data_files;
2. select*fromdba_tablespaces;//表空間
3. selecttablespace_name,sum(bytes),sum(blocks)fromdba_free_spacegroupbytablespace_name;//空閒表空間
4. select*fromdba_data_fileswheretablespace_name='rbs';//表空間對應的資料檔案
5. select*fromdba_segmentswheretablespace_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';
3.資料庫物件
範例**
1. select*fromdba_objects;
2. 3. cluster、databaselink、function、index、library、package、packagebody、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。
4.表範例**
1. select*fromdba_tables;
2. 3. selectextent_id,bytesfromdba_extentswheresegment_name='customers'andsegment_type='table'orderbyextent_id;//表使用的extent的資訊。
segment_type='rollback'檢視回滾段的空間分配資訊
4. 5. selectdistincttable_namefromuser_tab_columnswherecolumn_name='so_type_id';
select * from dba_tables;
select extent_id,bytes from dba_extents where segment_name='customers' and segment_type='table' order by extent_id;//表使用的extent的資訊。segment_type='rollback'檢視回滾段的空間分配資訊
select distinct table_name from user_tab_columns where column_name='so_type_id';
5.索引
範例**
1. select*fromdba_indexes;//索引，包括主鍵索引
2. select*fromdba_ind_columns;//索引列
3.4. fromuser_indexesi,user_ind_columnsc
5.6. 'acc_nbr';//聯接使用
select * from dba_indexes;//索引，包括主鍵索引
select * from dba_ind_columns;//索引列
select
from user_indexes i,user_ind_columns c
where
and ='acc_nbr';//聯接使用
6.序列
範例**
1. select*fromdba_sequences;
select * from dba_sequences;
7.檢視
範例**
1. select*fromdba_views
2. select*fromall_views
3. text欄位可用於查詢檢視生成的指令碼
select * from dba_views
select * from all_views
text欄位可用於查詢檢視生成的指令碼
8.聚簇
範例**
1. select*fromdba_clusters
select * from dba_clusters
9.快照
範例**
1. select*fromdba_snapshots
select * from dba_snapshots
快照、分割槽應存在相應的表空間
10.同義詞
範例**
1. select*fromdba_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
11.資料庫鏈
範例**
1. select*fromdba_db_links
select * from dba_db_links
在spbase下建資料庫鏈:
範例**
1. createdatabaselinkdbl_spnew
2. connecttospnewidentifiedbyspnewusing'jhhx';
3. 4. insertintoacc_nbr@dbl_spnewselect*fromacc_nbrwherenxx_nbr='237'andline_nbr='8888';
create database link dbl_spnew
connect to spnew identified by spnew using 'jhhx';
insert into acc_nbr@dbl_spnew select * from acc_nbr where nxx_nbr='237' and line_nbr='8888';
12.觸發器
範例**
1. select*fromdba_trigers;
select * from dba_trigers;
Oracle分割槽表總結
create table emp empno number 4 ename varchar2 30 location varchar2 30 partition by list location partition p1 values 北京 partition p2 values 上海 天津 重慶 ...
oracle建表命令總結
對錶中插入記錄的限制 主鍵 primary key 實現表中記錄的乙個唯一性唯一不能為空,乙個表中只有乙個主鍵 外來鍵 foreign key 參照於主鍵的值可以重複,可以插入空值 唯一性 unique 設定除主鍵外的唯一性可以插入空值 自定義 check 使用者自定義限制 不為空 not null...
Oracle分割槽表詳解
範圍和列表技術的組合，首先對錶進行範圍分割槽，然後用列表技術對每個範圍分割槽再次分割槽。與組合範圍 雜湊分割槽不同的是，每個子分割槽的所有內容表示資料的邏輯子集，由適當的範圍和列表分割槽設定來描述。注意 先一定要進行範圍分割槽 select from v option s order by s.pa...