oracle系统表格大全整理.docx
《oracle系统表格大全整理.docx》由会员分享，可在线阅读，更多相关《oracle系统表格大全整理.docx（50页珍藏版）》请在冰豆网上搜索。
oracle系统表格大全整理
oracle系统表查询
T2005-4-29网络
数据字典dict总是属于Oracle用户sys的。
**************************************************************************************************************************
1、用户：
selectusernamefromdba_users;
创建用户
CREATEUSERjzmisPROFILEDEFAULTIDENTIFIEDBYjzmis
DEFAULT
TABLESPACEUSERSTEMPORARY
TABLESPACETEMPACCOUNTUNLOCK;
GRANTDBATOjzmisWITHADMINOPTION;
commit;
改口令
alteruserspgroupidentifiedbyspgtest;
2、表空间：
select*fromdba_data_files;
select*fromdba_tablespaces;//表空间
selecttablespace_name,sum（bytes）,sum（blocks）
fromdba_free_spacegroupbytablespace_name;//空闲表空间
select*fromdba_data_files
wheretablespace_name='RBS';//表空间对应的数据文件
select*fromdba_segments
wheretablespace_name='INDEXS';
删除表空间
dropTABLESPACEXNMISINCLUDINGCONTENTS;
创建表空间
CREATETABLESPACE"XNMIS"
LOGGING
DATAFILE‘E:
\oracle\product\10.2.0\oradata\jzmis\xnmis.dbf’SIZE2048MEXTENT
MANAGEMENTLOCALSEGMENTSPACEMANAGEMENT AUTO
AUTOEXTENDONNEXT5MMAXSIZEUNLIMITED
CREATETABLESPACE"XNMIS"
LOGGING
DATAFILE'E:
\oracle\product\10.2.0\oradata\jzmis\XNMIS.dbf'SIZE2048MREUSE
AUTOEXTENDONNEXT5MMAXSIZEUNLIMITED
;
'E:
\oracle\admin\cmid\JTAIS.ora'（path）
"CTAIS"（表空间名）
3、数据库对象：
select*fromdba_objects;
CLUSTER、DATABASELINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGEBODY、
PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
4、表：
select*fromdba_tables;
analyzemy_tablecomputestatistics;->dba_tables后6列
selectextent_id,bytesfromdba_extents
wheresegment_name='CUSTOMERS'andsegment_type='TABLE'
orderbyextent_id;//表使用的extent的信息。
segment_type='ROLLBACK'查看回滚段的空间分配信息
列信息：
selectdistincttable_name
fromuser_tab_columns
wherecolumn_name='SO_TYPE_ID';
5、索引：
select*fromdba_indexes;//索引，包括主键索引
select*fromdba_ind_columns;//索引列
selecti.index_name,i.uniqueness,c.column_name
fromuser_indexesi,user_ind_columnsc
wherei.index_name=c.index_name
andi.table_name='ACC_NBR';//联接使用
6、序列：
select*fromdba_sequences;
7、视图：
select*fromdba_views;
select*fromall_views;
text可用于查询视图生成的脚本
8、聚簇：
select*fromdba_clusters;
9、快照：
select*fromdba_snapshots;
快照、分区应存在相应的表空间。
10、同义词：
select*fromdba_synonyms
wheretable_owner='SPGROUP';
//ifownerisPUBLIC,thenthesynonymsisapublicsynonym.
ifownerisoneofusers,thenthesynonymsisaprivatesynonym.
11、数据库链：
select*fromdba_db_links;
在spbase下建数据库链
createdatabaselinkdbl_spnew
connecttospnewidentifiedbyspnewusing'jhhx';
insertintoacc_nbrdbl_spnew
select*fromacc_nbrwherenxx_nbr='237'andline_nbr='8888';
12、触发器：
select*fromdba_trigers;
存储过程，函数从dba_objects查找。
其文本：
selecttextfromuser_sourcewherename='BOOK_SP_EXAMPLE';
建立出错：
select*fromuser_errors;
oracle总是将存储过程，函数等软件放在SYSTEM表空间。
13、约束：
（1）约束是和表关联的，可在createtable或altertabletable_nameadd/drop/modify来建立、修改、删除约束。
可以临时禁止约束，如：
altertablebook_example
disableconstraintbook_example_1;
altertablebook_example
enableconstraintbook_example_1;
（2）主键和外键被称为表约束，而notnull和unique之类的约束被称为列约束。
通常将主键和外键作为单独的命名约束放在字段列表下面，而列约束可放在列定义的同一行，这样更具有可读性。
（3）列约束可从表定义看出，即describe;表约束即主键和外键，可从dba_constraints和dba_cons_columns查
select*fromuser_constraints
wheretable_name='BOOK_EXAMPLE';
selectowner,CONSTRAINT_NAME,TABLE_NAME
fromuser_constraints
whereconstraint_type='R'
orderbytable_name;
（4）定义约束可以无名（系统自动生成约束名）和自己定义约束名（特别是主键、外键）
如：
createtablebook_example
（identifiernumbernotnull）;
createtablebook_example
（identifiernumberconstranitbook_example_1notnull）;
14、回滚段：
在所有的修改结果存入磁盘前，回滚段中保持恢复该事务所需的全部信息，必须以数据库发生的事务来相应确定其大小（DML语句才可回滚，create,drop,truncate等DDL不能回滚）。
回滚段数量=并发事务/4，但不能超过50；使每个回滚段大小足够处理一个完整的事务；
createrollbacksegmentr05
tablespacerbs;
createrollbacksegmentrbs_cvt
tablespacerbs
storage（initial1Mnext500k）;
使回滚段在线
alterrollbacksegmentr04online;
用dba_extents,v$rollback_segs监测回滚段的大小和动态增长。
回滚段的区间信息
select*fromdba_extents
wheresegment_type='ROLLBACK'andsegment_name='RB1';
回滚段的段信息,其中bytes显示目前回滚段的字节数
select*fromdba_segments
wheresegment_type='ROLLBACK'andsegment_name='RB1';
为事物指定回归段
settransactionuserollbacksegmentrbs_cvt
针对bytes可以使用回滚段回缩。
alterrollbacksegmentrbs_cvtshrink;
selectbytes,extents,max_extentsfromdba_segments
wheresegment_type='ROLLBACK'andsegment_name='RBS_CVT';
回滚段的当前状态信息：
select*fromdba_rollback_segs
wheresegment_name='RB1';
比多回滚段状态status，回滚段所属实例instance_num
查优化值optimal
selectn.name,s.optsize
fromv$rollnamen,v$rollstats
wheren.usn=s.usn;
回滚段中的数据
settransactionuserollbacksegmentrb1;/*回滚段名*/
selectn.name,s.writes
fromv$rollnamen,v$rollstats
wheren.usn=s.usn;
当事务处理完毕，再次查询$rollstat，比较writes（回滚段条目字节数）差值，可确定事务的大小。
查询回滚段中的事务
columnrrheading'RBSegment'formata18
columnusheading'Username'formata15
columnosheading'OsUser'formata10
columnteheading'Terminal'formata10
selectr.namerr,nvl（s.username,'notransaction'）us,s.osuseros,s.terminalte
fromv$lockl,v$sessions,v$rollnamer
wherel.sid=s.sid（+）
andtrunc（l.id1/65536）=R.USN
andl.type='TX'
andl.lmode=6
orderbyr.name;
15、作业
查询作业信息
selectjob,broken,next_date,interval,whatfromuser_jobs;
selectjob,broken,next_date,interval,whatfromdba_jobs;
查询正在运行的作业
select*fromdba_jobs_running;
使用包execdbms_job.submit（:
v_num,'a;',sysdate,'sysdate+（10/（24*60*60））'）加入作业。
间隔10秒钟
execdbms_job.submit（:
v_num,'a;',sysdate,'sysdate+（11/（24*60））'）加入作业。
间隔11分钟使用包execdbms_job.remove（21）删除21号作业。
安装Oracle后，经常使用的修改表空间的SQL代码
（1）
配置：
WindowsNT4.0中文版
5块10.2GBSCSI硬盘
分：
C:
盘、D:
盘、E:
盘、F:
盘、G:
盘
Oracle8.0.4forWindowsNT
NT安装在C:
\WINNT，Oracle安装在C:
\ORANT
目标：
因系统的回滚段太小，现打算生成新的回滚段，
建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）
建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用，
如：
现有10个应用用户，每个用户是一个独立子系统（如：
商业进销存MIS系统中的财务、收款、库存、人事、总经理等）
尤其大型商场中收款机众多，同时访问进程很多，经常达到50-100个进程同时访问，
这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放在不同的物理磁盘上），
减少了用户之间的I/O竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）
规划：
C:
盘、NT系统，Oracle系统
D:
盘、数据表空间1（3GB、自动扩展）、回滚表空间1（1GB、自动扩展）
E:
盘、数据表空间2（3GB、自动扩展）、回滚表空间2（1GB、自动扩展）
F:
盘、索引表空间1（2GB、自动扩展）、临时表空间1（0.5GB、不自动扩展）
G:
盘、索引表空间2（2GB、自动扩展）、临时表空间2（0.5GB、不自动扩展）
注：
这只是一个简单的规划，实际规划要依系统需求来定，尽量减少I/O竞争
实现：
1、首先查看系统有哪些回滚段及其状态。
SQL>colownerformata20
SQL>colstatusformata10
SQL>colsegment_nameformata20
SQL>coltablespace_nameformata20
SQL>SELECTOWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM（BYTES）/1024/1024M
2 FROMDBA_SEGMENTS
3 WHERESEGMENT_TYPE='ROLLBACK'
4 GROUPBYOWNER,SEGMENT_NAME,TABLESPACE_NAME
5 /
OWNER SEGMENT_NAME TABLESPACE_NAME M
---------------------------------------------------------------------
SYS RB1 ROLLBACK_DATA .09765625
SYS RB10 ROLLBACK_DATA .09765625
<>
安装Oracle后，经常使用的修改表空间的SQL代码
（2）SYS RB11 ROLLBACK_DATA .09765625
SYS RB12 ROLLBACK_DATA .09765625
SYS RB13 ROLLBACK_DATA .09765625
SYS RB14 ROLLBACK_DATA .09765625
SYS RB15 ROLLBACK_DATA .09765625
SYS RB16 ROLLBACK_DATA .09765625
SYS RB2 ROLLBACK_DATA .09765625
SYS RB3 ROLLBACK_DATA .09765625
<>
安装Oracle后，经常使用的修改表空间的SQL代码（3）SYS RB4 ROLLBACK_DATA .09765625
SYS RB5 ROLLBACK_DATA .09765625
SYS RB6 ROLLBACK_DATA .09765625
SYS RB7 ROLLBACK_DATA .09765625
SYS RB8 ROLLBACK_DATA .09765625
SYS RB9 ROLLBACK_DATA .09765625
SYS RB_TEMP SYSTEM .24414063
SYS SYSTEM SYSTEM .1953125
查询到18记录.
SQL>SELECTSEGMENT_NAME,OWNER,
<>
安装Oracle后，经常使用的修改表空间的SQL代码（4） 2 TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS
3 FROMDBA_ROLLBACK_SEGS
4 /
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_IDSTATUS
-----------------------------------------------------------------------------------------
SYSTEM SYS SYSTEM 0 1ONLINE
RB_TEMP SYS SYSTEM 1 1OFFLINE
RB1 PUBLIC ROLLBACK_DATA 2 3ONLINE
RB2 PUBLIC ROLLBACK_DATA 3 3ONLINE
安装Oracle后，经常使用的修改表空间的SQL代码（5）RB3 PUBLIC ROLLBACK_DATA 4 3ONLINE
RB4 PUBLIC ROLLBACK_DATA 5 3ONLINE
RB5 PUBLIC ROLLBACK_DATA 6 3ONLINE
RB6 PUBLIC ROLLBACK_DATA 7 3OFFLINE
RB7 PUBLIC ROLLBACK_DATA 8 3OFFLINE
RB8 PUBLIC ROLL
升级会员 