oracle系统表大全整理

1、oracle系统表查询http:/T 2005-4-29 网络 数据字典dict总是属于Oracle用户sys的。 *1、用户： select username from dba_users; 创建用户CREATE USER jzmis PROFILE DEFAULT IDENTIFIED BY jzmis DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;GRANT DBA TO jzmis WITH ADMIN OPTION;commit;改口令 alter user spgroup

2、 identified by spgtest; 2、表空间： 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 table

3、space_name=INDEXS; 删除表空间drop TABLESPACE XNMIS INCLUDING CONTENTS;创建表空间CREATE TABLESPACE XNMIS LOGGING DATAFILE E:oracleproduct10.2.0oradatajzmisxnmis.dbf SIZE 2048M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED CREATE TABLESPACE XNMIS LOGGING DATAFILE E

4、:oracleproduct10.2.0oradatajzmisXNMIS.dbf SIZE 2048M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED ;E:oracleadmincmidJTAIS.ora （path)CTAIS (表空间名）3、数据库对象： select * from dba_objects; CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、 PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFI

5、NED、VIEW。 4、表： select * from dba_tables; analyze my_table compute statistics;-dba_tables后6列 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_co

6、lumns where column_name=SO_TYPE_ID; 5、索引： 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;/联接使用 6、序列： select * from dba_sequences; 7

7、、视图： select * from dba_views; select * from all_views; text 可用于查询视图生成的脚本 8、聚簇： select * from dba_clusters; 9、快照： select * from dba_snapshots; 快照、分区应存在相应的表空间。 10、同义词： select * from dba_synonyms where table_owner=SPGROUP; /if owner is PUBLIC,then the synonyms is a public synonym. if owner is one of us

8、ers,then the synonyms is a private synonym. 11、数据库链： select * from dba_db_links; 在spbase下建数据库链 create database link dbl_spnew connect to spnew identified by spnew using jhhx; insert into acc_nbrdbl_spnew select * from acc_nbr where nxx_nbr=237 and line_nbr=8888; 12、触发器： select * from dba_trigers; 存储

9、过程，函数从dba_objects查找。 其文本：select text from user_source where name=BOOK_SP_EXAMPLE; 建立出错：select * from user_errors; oracle总是将存储过程，函数等软件放在SYSTEM表空间。 13、约束： （1）约束是和表关联的，可在create table或alter table table_name add/drop/modify来建立、修改、删除约束。 可以临时禁止约束，如： alter table book_example disable constraint book_example_

10、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

11、,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); 14、回滚段： 在所有的修改结果存入磁盘前，回滚

12、段中保持恢复该事务所需的全部信息，必须以数据库发生的事务来相应确定其大小（DML语句才可回滚，create,drop,truncate等DDL不能回滚）。 回滚段数量=并发事务/4，但不能超过50；使每个回滚段大小足够处理一个完整的事务； create rollback segment r05 tablespace rbs; create rollback segment rbs_cvt tablespace rbs storage(initial 1M next 500k); 使回滚段在线 alter rollback segment r04 online; 用dba_extents,v$r

13、ollback_segs监测回滚段的大小和动态增长。 回滚段的区间信息 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; 为事物指定回归段 set transaction use rollback segment rbs_cvt 针对bytes可以使用回滚段回缩。 alter rollba

14、ck 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; 比多回滚段状态status，回滚段所属实例instance_num 查优化值optimal select ,s.optsize from v$rollname n,v$rollstat s

15、 where n.usn=s.usn; 回滚段中的数据 set transaction use rollback segment rb1;/*回滚段名*/ select ,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 o

16、s heading Os User format a10 column te heading Terminal format a10 select 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/65536)=R.USN and l.type=TX and l.lmode=6 order by ; 15、作业 查询作业信息 select jo

17、b,broken,next_date,interval,what from user_jobs; select job,broken,next_date,interval,what from dba_jobs; 查询正在运行的作业 select * from dba_jobs_running; 使用包exec dbms_job.submit(:v_num,a;,sysdate,sysdate + (10/(24*60*60)加入作业。间隔10秒钟 exec dbms_job.submit(:v_num,a;,sysdate,sysdate + (11/(24*60)加入作业。间隔11分钟使用包

18、exec dbms_job.remove(21)删除21号作业。安装Oracle后，经常使用的修改表空间的SQL代码(1) 配置：Windows NT 4.0 中文版5块10.2GB SCSI硬盘分：C:盘、D:盘、E:盘、F:盘、G:盘Oracle 8.0.4 for Windows NTNT安装在C:WINNT，Oracle安装在C:ORANT目标：因系统的回滚段太小，现打算生成新的回滚段，建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用，如：现有10个应用用户，每个用户是一个独立子系统（如：商业进销存MI

19、S系统中的财务、收款、库存、人事、总经理等）尤其大型商场中收款机众多，同时访问进程很多，经常达到50-100个进程同时访问，这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放在不同的物理磁盘上），减少了用户之间的I/O竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）规划：C:盘、NT系统，Oracle系统D:盘、数据表空间1（3GB、自动扩展）、回滚表空间1（1GB、自动扩展）E:盘、数据表空间2（3GB、自动扩展）、回滚表空间2（1GB、自动扩展）F:盘、索引表空间1（2GB、自动扩展）、临时表空间1（0.5GB、不

20、自动扩展）G:盘、索引表空间2（2GB、自动扩展）、临时表空间2（0.5GB、不自动扩展）注：这只是一个简单的规划，实际规划要依系统需求来定，尽量减少I/O竞争实现：1、首先查看系统有哪些回滚段及其状态。SQL col owner format a20SQL col status format a10SQL col segment_name format a20SQL col tablespace_name format a20SQL SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M 2 FROM DBA_SEGM

21、ENTS 3 WHERE SEGMENT_TYPE=ROLLBACK 4 GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME 5 /OWNER SEGMENT_NAME TABLESPACE_NAME M- - - -SYS RB1 ROLLBACK_DATA .09765625SYS RB10 ROLLBACK_DATA .09765625安装Oracle后，经常使用的修改表空间的SQL代码(2) SYS RB11 ROLLBACK_DATA .09765625SYS RB12 ROLLBACK_DATA .09765625SYS RB13 ROLLBAC

22、K_DATA .09765625SYS RB14 ROLLBACK_DATA .09765625SYS RB15 ROLLBACK_DATA .09765625SYS RB16 ROLLBACK_DATA .09765625SYS RB2 ROLLBACK_DATA .09765625SYS RB3 ROLLBACK_DATA .09765625安装Oracle后，经常使用的修改表空间的SQL代码(3) SYS RB4 ROLLBACK_DATA .09765625SYS RB5 ROLLBACK_DATA .09765625SYS RB6 ROLLBACK_DATA .09765625SYS

23、 RB7 ROLLBACK_DATA .09765625SYS RB8 ROLLBACK_DATA .09765625SYS RB9 ROLLBACK_DATA .09765625SYS RB_TEMP SYSTEM .24414063SYS SYSTEM SYSTEM .1953125查询到18记录.SQL SELECT SEGMENT_NAME,OWNER,安装Oracle后，经常使用的修改表空间的SQL代码(4) 2 TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS 3 FROM DBA_ROLLBACK_SEGS 4 /SEGMENT_NAME OWN

24、ER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS- - - - - -SYSTEM SYS SYSTEM 0 1 ONLINERB_TEMP SYS SYSTEM 1 1 OFFLINERB1 PUBLIC ROLLBACK_DATA 2 3 ONLINERB2 PUBLIC ROLLBACK_DATA 3 3 ONLINE安装Oracle后，经常使用的修改表空间的SQL代码(5) RB3 PUBLIC ROLLBACK_DATA 4 3 ONLINERB4 PUBLIC ROLLBACK_DATA 5 3 ONLINERB5 PUBLIC ROLLBA

25、CK_DATA 6 3 ONLINERB6 PUBLIC ROLLBACK_DATA 7 3 OFFLINERB7 PUBLIC ROLLBACK_DATA 8 3 OFFLINERB8 PUBLIC ROLLBACK_DATA 9 3 OFFLINE安装Oracle后，经常使用的修改表空间的SQL代码(6) RB9 PUBLIC ROLLBACK_DATA 10 3 OFFLINERB10 PUBLIC ROLLBACK_DATA 11 3 OFFLINERB11 PUBLIC ROLLBACK_DATA 12 3 OFFLINERB12 PUBLIC ROLLBACK_DATA 13 3

26、OFFLINERB13 PUBLIC ROLLBACK_DATA 14 3 OFFLINERB14 PUBLIC ROLLBACK_DATA 15 3 OFFLINE安装Oracle后，经常使用的修改表空间的SQL代码(7) RB15 PUBLIC ROLLBACK_DATA 16 3 OFFLINERB16 PUBLIC ROLLBACK_DATA 17 3 OFFLINE查询到18记录.2、修改代码如下，可把以下代码存入一.sql文件，如cg_sys.sql，然后以SQL cg_sys.sql调用执行。-注意：各个硬盘上要事先建好oradata目录-修改现有回滚段，使之失效，下线alter

27、 rollback segment rb1 offline;alter rollback segment rb2 offline;alter rollback segment rb3 offline;alter rollback segment rb4 offline;alter rollback segment rb5 offline;alter rollback segment rb6 offline;alter rollback segment rb7 offline;alter rollback segment rb8 offline;alter rollback segment rb

28、9 offline;alter rollback segment rb10 offline;alter rollback segment rb11 offline;alter rollback segment rb12 offline;alter rollback segment rb13 offline;alter rollback segment rb14 offline;alter rollback segment rb15 offline;alter rollback segment rb16 offline;-删除原有回滚段drop rollback segment rb1;drop

29、 rollback segment rb2;drop rollback segment rb3;drop rollback segment rb4;drop rollback segment rb5;drop rollback segment rb6;drop rollback segment rb7;drop rollback segment rb8;drop rollback segment rb9;drop rollback segment rb10;drop rollback segment rb11;drop rollback segment rb12;drop rollback s

30、egment rb13;drop rollback segment rb14;drop rollback segment rb15;drop rollback segment rb16;-建数据表空间1安装Oracle后，经常使用的修改表空间的SQL代码(8) -收款、库存、订货、远程通信create tablespace USER_DATA1 datafiled:oradatauser1_1.ora size 512M,d:oradatauser1_2.ora size 512M,d:oradatauser1_3.ora size 512M,d:oradatauser1_4.ora size

31、 512M,d:oradatauser1_5.ora size 512M,d:oradatauser1_6.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-initial 128K，因为，用户建在表空间上，而表建在用户里，为用户所拥有，-用户继承数据表空间的存储参数，表继承用户的存储参数-如果initial设的过大，如：5M，则每建一个空表就要占用5M的空间，即使一条记录也没有-AUTOEXTEND ON NEXT 5M MAXSI

32、ZE UNLIMITED，设置数据文件自动扩展，每一次扩展增加5M，最大空间不受限-建数据表空间2-物价、人事、结算、财务、总经理、合同、统计create tablespace USER_DATA2 datafilee:oradatauser2_1.ora size 512M,e:oradatauser2_2.ora size 512M,e:oradatauser2_3.ora size 512M,e:oradatauser2_4.ora size 512M,e:oradatauser2_5.ora size 512M,e:oradatauser2_6.ora size 512MAUTOEXT

33、END ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建索引表空间1create tablespace INDEX_DATA1 datafilef:oradataindex1_1.ora size 512M,f:oradataindex1_2.ora size 512M,f:oradataindex1_3.ora size 512M,f:oradataindex1_4.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdef

34、ault storage (initial 128K next 2M pctincrease 0);-建索引表空间2create tablespace INDEX_DATA2 datafileg:oradataindex2_1.ora size 512M,g:oradataindex2_2.ora size 512M,g:oradataindex2_3.ora size 512M,g:oradataindex2_4.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M

35、pctincrease 0);-建回滚表空间1-设置初始值40M（initial 40M），则每在这个表空间中建一个回滚段，-此回滚段自动继承此回滚表空间的存储参数，也即默认文件为40Mcreate tablespace ROLLBACK_DATA1 datafiled:oradataroll1_1.ora size 512M，d:oradataroll1_2.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);-建回滚表空间2create

36、 tablespace ROLLBACK_DATA2 datafile安装Oracle后，经常使用的修改表空间的SQL代码(9) e:oradataroll2_1.ora size 512M,e:oradataroll2_2.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);-建临时表空间1create tablespace TEMPORARY_DATA1 datafilef:oradatatemp1_1.ora size 512Mdef

37、ault storage (initial 10M next 3M pctincrease 0);-建临时表空间2create tablespace TEMPORARY_DATA2 datafileg:oradatatemp2_1.ora size 512Mdefault storage (initial 10M next 3M pctincrease 0);-使其真正成为临时的alter tablespace TEMPORARY_DATA1 temporary;alter tablespace TEMPORARY_DATA2 temporary;-建立新的回滚段，每个都一样大，不同大小的回滚

38、段没有什么意义，系统是随机选择的。-建多少个，根据并发访问用户的多少，-如果你们公司每天有50-100个人员使用Oracle系统开发的管理软件，应该20个以上create public rollback segment rb01 tablespace rollback_data1;create public rollback segment rb02 tablespace rollback_data1;create public rollback segment rb03 tablespace rollback_data1;create public rollback segment rb04

39、 tablespace rollback_data1;create public rollback segment rb05 tablespace rollback_data1;create public rollback segment rb06 tablespace rollback_data1;create public rollback segment rb07 tablespace rollback_data1;create public rollback segment rb08 tablespace rollback_data1;create public rollback se

40、gment rb09 tablespace rollback_data2;create public rollback segment rb10 tablespace rollback_data2;-前8个建在回滚表空间1中，后8个在回滚表空间2create public rollback segment rb11 tablespace rollback_data2;create public rollback segment rb12 tablespace rollback_data2;create public rollback segment rb13 tablespace rollba

41、ck_data2;create public rollback segment rb14 tablespace rollback_data2;create public rollback segment rb15 tablespace rollback_data2;create public rollback segment rb16 tablespace rollback_data2;create public rollback segment rb17 tablespace rollback_data2;create public rollback segment rb18 tablesp

42、ace rollback_data2;create public rollback segment rb19 tablespace rollback_data2;安装Oracle后，经常使用的修改表空间的SQL代码(10) create public rollback segment rb20 tablespace rollback_data2;-使回滚段online，即有效alter rollback segment rb01 online;alter rollback segment rb02 online;alter rollback segment rb03 online;alter

43、rollback segment rb04 online;alter rollback segment rb05 online;alter rollback segment rb06 online;alter rollback segment rb07 online;alter rollback segment rb08 online;alter rollback segment rb09 online;alter rollback segment rb10 online;alter rollback segment rb11 online;alter rollback segment rb1

44、2 online;alter rollback segment rb13 online;alter rollback segment rb14 online;alter rollback segment rb15 online;alter rollback segment rb16 online;alter rollback segment rb17 online;alter rollback segment rb18 online;alter rollback segment rb19 online;alter rollback segment rb20 online;-查看现有回滚段及其状

45、态col segment format a30SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;-查看数据文件及其所在表空间、大小、状态col file_name format a40col tablespace_name format a20select file_name,file_id,tablespace_name,bytes,status from dba_data_files;至此，表空间重新规划完毕，这里讲的比较通俗，还有好多参数值得设置，能够把Or

46、acle设置到最优的境界，表空间设置完了，下面，就该好好的整理一下Oracle的内存区了，Oracle很有意思，内存越大，效果越明显，所以有必要好好调整一下SGA区，也就是主要配置ininorcl.ora参数文件。. ORACLE体系结构组成 Oracle server=Oracle instance+Oracle databaseOracle instance=Memory(SGA+PGA)+Background ProcessSGA: startup nomount时就被分配，写在参数文件里。SGA_MAX_Size -SGA区最大大小 颗粒：连续的虚拟内存分配。颗粒大小取决于SGA_MA

47、X_SIZE的大小，若SGA_MAX_SIZE128M，颗粒为4M，否则颗粒为16M. SGA启动时，每个组成部分会要求尽可能多的颗粒。固定的SGA至少会要求3个颗粒 (包括redo buffers1个颗粒，Database Buffer Cache 1个，Share poor1个颗粒）Shared Pool :用于存放最近执行的SQl 代码和最近的数据定义，包含Library Cache和Data Dictionary Cache，大小由shared_pool_size决定，Library Cache（存放statement text, SQl 解析代码和解析计划，以避免表述被重解析） St

48、ores information about the most recently used SQL and PL/SQL statements Enables the sharing of commonly used statements Is managed by a least recently used (LRU) algorithm Consists of two structures: Shared SQL area Shared PL/SQL area Size determined by the Shared Pool sizingData Dictionary Cache：包含从数据字典表对表，列，权限的定义.Database Buffer Cache:包含3个独立的子cache，统计信息由v$DB_CACHE_ADVICEDB_CACHE_SIZE: 定义标准Block默认的Buffer Cache; 至少一个颗粒。buffer cache分为defaul