oracle系统表大全整理.pdf

《oracle系统表大全整理.pdf》由会员分享，可在线阅读，更多相关《oracle系统表大全整理.pdf（40页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、oracleoracle 系统表查询系统表查询http：/Tech。acnow。net 2005-429 网络数据字典 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

2、user spgroup 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_fileswhere tablespace_name=RBS;/表空间对应的数据文件select from dba_segmentswhere tablespace_

3、name=INDEXS;删除表空间drop TABLESPACE XNMIS INCLUDING CONTENTS;创建表空间CREATE TABLESPACE”XNMIS”LOGGINGDATAFILE E:oracleproduct10。2.0oradatajzmisxnmis。dbf SIZE 2048M EXTENTMANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTOAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDCREATE TABLESPACE XNMISLOGGINGDATAFILE E：oracleprodu

4、ct10。2.0oradatajzmisXNMIS。dbf SIZE 2048M REUSEAUTOEXTEND ONNEXT 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、UNDEFINED、VIEW。4、表：select f

5、rom dba_tables;analyze my_table compute statistics；dba_tables 后 6 列select extent_id，bytes from dba_extentswhere segment_name=CUSTOMERS and segment_type=TABLEorder by extent_id;/表使用的 extent 的信息.segment_type=ROLLBACK查看回滚段的空间分配信息列信息:select distinct table_namefrom user_tab_columnswhere column_name=SO_TY

6、PE_ID;5、索引：select from dba_indexes;/索引,包括主键索引select from dba_ind_columns;/索引列select i。index_name,i。uniqueness,c.column_namefrom user_indexes i,user_ind_columns cwhere i.index_name=c。index_nameand i。table_name=ACC_NBR；/联接使用6、序列:select*from dba_sequences；7、视图：select from dba_views；select*from all_view

7、s;text 可用于查询视图生成的脚本8、聚簇：select*from dba_clusters；9、快照：select from dba_snapshots;快照、分区应存在相应的表空间。10、同义词：select*from dba_synonymswhere table_owner=SPGROUP;/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、数据库链：select*from dba_db

8、_links；在 spbase 下建数据库链create database link dbl_spnewconnect to spnew identified by spnew using jhhx;insert into acc_nbrdbl_spnewselect from acc_nbr where nxx_nbr=237 and line_nbr=8888;12、触发器：select*from dba_trigers;存储过程，函数从 dba_objects 查找。其文本：select text from user_source where name=BOOK_SP_EXAMPLE;建

9、立出错：select*from user_errors；oracle 总是将存储过程,函数等软件放在 SYSTEM 表空间。13、约束：（1）约束是和表关联的，可在create table或alter table table_name add/drop/modify来建立、修改、删除约束。可以临时禁止约束，如：alter table book_exampledisable constraint book_example_1;alter table book_exampleenable constraint book_example_1；(2）主键和外键被称为表约束,而 not null 和 u

10、nique 之类的约束被称为列约束。通常将主键和外键作为单独的命名约束放在字段列表下面,而列约束可放在列定义的同一行，这样更具有可读性。(3）列约束可从表定义看出，即 describe；表约束即主键和外键,可从 dba_constraints和 dba_cons_columns 查select*from user_constraintswhere table_name=BOOK_EXAMPLE；select owner，CONSTRAINT_NAME,TABLE_NAMEfrom user_constraintswhere constraint_type=Rorder by table_nam

11、e；（4）定义约束可以无名（系统自动生成约束名)和自己定义约束名(特别是主键、外键）如:create table book_example（identifier number not null)；create table book_example（identifier number constranit book_example_1 not null）；14、回滚段：在所有的修改结果存入磁盘前，回滚段中保持恢复该事务所需的全部信息，必须以数据库发生的事务来相应确定其大小（DML 语句才可回滚，create,drop，truncate 等 DDL 不能回滚）.回滚段数量=并发事务/4,但不能超过

12、 50;使每个回滚段大小足够处理一个完整的事务；create rollback segment r05tablespace rbs；create rollback segment rbs_cvttablespace rbsstorage(initial 1M next 500k)；使回滚段在线alter rollback segment r04 online；用 dba_extents,v$rollback_segs监测回滚段的大小和动态增长。回滚段的区间信息select*from dba_extentswhere segment_type=ROLLBACK and segment_name=

13、RB1;回滚段的段信息，其中bytes 显示目前回滚段的字节数select from dba_segmentswhere segment_type=ROLLBACK and segment_name=RB1;为事物指定回归段set transaction use rollback segment rbs_cvt针对 bytes 可以使用回滚段回缩。alter rollback segment rbs_cvt shrink；select bytes,extents，max_extents from dba_segmentswhere segment_type=ROLLBACK and segme

14、nt_name=RBS_CVT;回滚段的当前状态信息:select from dba_rollback_segswhere segment_name=RB1；比多回滚段状态 status,回滚段所属实例 instance_num查优化值 optimalselect n。name,s。optsizefrom vrollname n,v$rollstat swhere n.usn=s。usn;回滚段中的数据set transaction use rollback segment rb1；/*回滚段名*/select n.name,s。writesfrom vrollname n，vrollstat

15、 swhere n。usn=s。usn;当事务处理完毕,再次查询$rollstat，比较 writes（回滚段条目字节数）差值,可确定事务的大小。查询回滚段中的事务column rr heading RB Segment format a18column us heading Username format a15column os heading Os User format a10column te heading Terminal format a10select r。name rr，nvl(s。username，no transaction）us,s。osuser os,s.termi

16、nal tefrom v$lock l，v$session s,v$rollname rwhere l.sid=s。sid(+）and trunc（l。id1/65536）=R.USNand l.type=TXand l.lmode=6order by r。name；15、作业查询作业信息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;使用包

17、exec dbms_job。submit（：v_num，a；，sysdate,sysdate+(10/(246060）)加入作业.间隔 10 秒钟exec dbms_job.submit(：v_num,a;,sysdate，sysdate+(11/（2460)）)加入作业。间隔 11分钟使用包 exec dbms_job.remove(21）删除 21 号作业。安装安装 OracleOracle 后，经常使用的修改表空间的后，经常使用的修改表空间的 SQLSQL 代码（代码（1)1)配置:Windows NT 4.0 中文版5 块 10。2GB SCSI 硬盘分：C：盘、D：盘、E：盘、F：盘

18、、G：盘Oracle 8.0.4 for Windows NTNT 安装在 C:WINNT,Oracle 安装在 C:ORANT目标：因系统的回滚段太小，现打算生成新的回滚段，建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用,如：现有 10 个应用用户，每个用户是一个独立子系统（如:商业进销存 MIS 系统中的财务、收款、库存、人事、总经理等）尤其大型商场中收款机众多，同时访问进程很多，经常达到 50100 个进程同时访问，这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空

19、间放在不同的物理磁盘上），减少了用户之间的 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、首先查看系统有

20、哪些回滚段及其状态。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 M2 FROM DBA_SEGMENTS3 WHERE SEGMENT_TYPE=ROLLBACK4 GROUP BY OWNER，SEGMENT_NAME,TABLESPACE_NAME5/OWNERSEGMENT_

21、NAMETABLESPACE_NAMEM-SYSRB1ROLLBACK_DATA.09765625SYSRB10ROLLBACK_DATA。09765625-安安装装OracleOracle后后,经经常常使使用用的的修修改改表表空空间间的的SQLSQL代代码码(2(2）SYSRB11ROLLBACK_DATA.09765625SYSRB12ROLLBACK_DATA。09765625SYSRB13ROLLBACK_DATA.09765625SYSRB14ROLLBACK_DATA.09765625SYSRB15ROLLBACK_DATA.09765625SYSRB16ROLLBACK_DATA

22、.09765625SYSRB2ROLLBACK_DATA。09765625SYSRB3ROLLBACK_DATA。09765625安安装装OracleOracle后后，经经常常使使用用的的修修改改表表空空间间的的SQLSQL代代码码（3 3）SYSRB4ROLLBACK_DATA.09765625SYSRB5ROLLBACK_DATA.09765625SYSRB6ROLLBACK_DATA。09765625SYSRB7ROLLBACK_DATA。09765625SYSRB8ROLLBACK_DATA。09765625SYSRB9ROLLBACK_DATA。09765625SYSRB_TEMPS

23、YSTEM。24414063SYSSYSTEMSYSTEM.1953125查询到 18 记录.SQL SELECT SEGMENT_NAME，OWNER,安安装装 OracleOracle 后后，经经常常使使用用的的修修改改表表空空间间的的 SQLSQL 代代码码（4 4）2TABLESPACE_NAME，SEGMENT_ID,FILE_ID，STATUS3FROM DBA_ROLLBACK_SEGS4/SEGMENT_NAMEOWNERTABLESPACE_NAMESEGMENT_IDFILE_ID STATUS-SYSTEMSYSSYSTEM01 ONLINERB_TEMPSYSSYSTE

24、M11 OFFLINERB1PUBLICROLLBACK_DATA23 ONLINERB2PUBLICROLLBACK_DATA33 ONLINE安安装装OracleOracle后后，经经常常使使用用的的修修改改表表空空间间的的SQLSQL代代码码（5)5)RB3PUBLICROLLBACK_DATA43 ONLINERB4PUBLICROLLBACK_DATA53 ONLINERB5PUBLICROLLBACK_DATA63 ONLINERB6PUBLICROLLBACK_DATA73 OFFLINERB7PUBLICROLLBACK_DATA83 OFFLINERB8PUBLICROLLB

25、ACK_DATA93 OFFLINE安安装装OracleOracle后后，经经常常使使用用的的修修改改表表空空间间的的SQLSQL代代码码(6(6）RB9PUBLICROLLBACK_DATA103 OFFLINERB10PUBLICROLLBACK_DATA113 OFFLINERB11PUBLICROLLBACK_DATA123 OFFLINERB12PUBLICROLLBACK_DATA133 OFFLINERB13PUBLICROLLBACK_DATA143 OFFLINERB14PUBLICROLLBACK_DATA153 OFFLINE安安装装OracleOracle后后，经经常常

26、使使用用的的修修改改表表空空间间的的SQLSQL代代码码（7 7）RB15PUBLICROLLBACK_DATA163 OFFLINERB16PUBLICROLLBACK_DATA173 OFFLINE查询到 18 记录.2、修改代码如下，可把以下代码存入一。sql 文件，如 cg_sys.sql，然后以 SQL cg_sys。sql 调用执行。-注意：各个硬盘上要事先建好 oradata 目录修改现有回滚段，使之失效,下线alter rollback segment rb1 offline;alter rollback segment rb2 offline；alter rollback s

27、egment 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 rb9 offline;alter rollback segment rb10 offline;alter rollback segment rb11 offline

28、；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 rollback segment rb2；drop rollback segment rb3；drop rollback segment rb4；drop ro

29、llback 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 segment rb13；drop rollback segment rb14；drop rollback segment rb15;drop rollback s

30、egment rb16；建数据表空间 1安装安装 OracleOracle 后后,经常使用的修改表空间的经常使用的修改表空间的 SQLSQL 代码（代码（8)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 512M,d:oradatauser1_5.ora size 512M，d:oradatauser

31、1_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 MAXSIZE UNLIMITED，设置数据文件自动扩展,每一次扩展增加 5M，最大空间不受限-建数据表

32、空间 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 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault stora

33、ge（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 UNLIMITEDdefault storage(initial 128K next 2M pctincrease

34、 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 pctincrease 0);建回滚表空间 1设置初始值 40M（initial 40M），

35、则每在这个表空间中建一个回滚段，-此回滚段自动继承此回滚表空间的存储参数，也即默认文件为 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 tablespace ROLLBACK_DATA2 datafile安装安装 Oracl

36、eOracle 后，经常使用的修改表空间的后，经常使用的修改表空间的 SQLSQL 代码代码(9(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 512Mdefault storage(i

37、nitial 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、。-建多少个，根据并发访问用户的多少，-如果你们公司每天有 50100 个人员使用 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 tablespace

39、 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 segment rb09

40、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 rollback_d

41、ata2；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 tablespace

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

43、ck segment rb03 online;alter 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 onli

44、ne;alter rollback segment rb12 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 se

45、gment rb20 online；-查看现有回滚段及其状态col segment format a30SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSDBA_ROLLBACK_SEGS;-查看数据文件及其所在表空间、大小、状态col file_name format a40col tablespace_name format a20select file_name,file_id,tablespace_name,bytes，status from dba_data_files；FROM至此，表空间重新规划完毕

46、，这里讲的比较通俗,还有好多参数值得设置,能够把 Oracle 设置到最优的境界,表空间设置完了,下面，就该好好的整理一下 Oracle 的内存区了，Oracle 很有意思，内存越大，效果越明显，所以有必要好好调整一下SGA 区,也就是主要配置 ininorcl。ora参数文件。ORACLEORACLE 体系结构组成体系结构组成Oracle server=Oracle instance+Oracle databaseOracle server=Oracle instance+Oracle databaseOracle instance=MemoryOracle instance=Memory（

47、SGA+PGASGA+PGA）+Background Process+Background ProcessSGASGA：startup nomountstartup nomount时就被分配，写在参数文件里时就被分配，写在参数文件里.SGA_MAX_Size-SGA 区最大大小颗颗 粒粒:连连 续续 的的 虚虚 拟拟 内内 存存 分分 配配。颗颗 粒粒 大大 小小 取取 决决 于于 SGA_MAX_SIZESGA_MAX_SIZE 的的 大大 小小,若若SGA_MAX_SIZE128M,SGA_MAX_SIZEinitSID.ora Default PFILEDefault PFILEDB 启

48、动的 3 个步骤1）nomount(读 spfile/pfile，初始化 SGA)2）mount（读控制文件)3)open（读控制文件描述的各个文件并检查一致性）session:一个用户的连接transaction：事务，一次修改或 select。Alert_SID.logAlert_SID.log(对应 background_dump_dest）实例的监控调试文件User Trace FilesUser Trace Files(对应 USER_DUMP_DEST)，大小由 MAX_DUMP_FILE_SIZEEnabling or Disabling User TracingEnabling

49、 or Disabling User Tracing 会话级：会话级：ALTER SESSION SET SQL_TRACE=TRUEdbms_system。SET_SQL_TRACE_IN_SESSION事例级事例级在参数文件里设置：SQL_TRACE=TRUE加深加深(有价值的）文章有价值的）文章:pfile:pfile 和和 spfilespfile 文件（文件（by eygleby eygle）4 4。创建数据库创建数据库数据库管理的认证方式数据库管理的认证方式(需要查找文章仔细看）需要查找文章仔细看）：1 1）OSOS 认证认证2 2）passwordpassword 文件文件(参数

50、文件只保存参数文件只保存 syssys 用户的口令，用户的口令，其他用户包括其他用户包括 systemsystem 用户的口用户的口令都保存在数据字典中。令都保存在数据字典中。语法语法 orapwd file=/opt/dbs/ora password=abc123 entries=5orapwd file=/opt/dbs/ora password=abc123 entries=5entriesentries：最多有：最多有 syssys 用户权限的用户数量用户权限的用户数量初始化文件里的参数初始化文件里的参数 remote_login_password_file=exclusiveremo