oracle系统表大全整理优质资料.doc

oracle系统表大全整理优质资料
(可以直接使用，可编辑 优质资料，欢迎下载）
oracle系统表查询
://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 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 tablespace_name='INDEXS';

删除表空间
drop TABLESPACE XNMIS INCLUDING CONTENTS;
创建表空间
 CREATE TABLESPACE "XNMIS"
    LOGGING
    DATAFILE ‘E:\oracle\product\\oradata\jzmis\xnmis.dbf’ SIZE 2048M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

CREATE TABLESPACE "XNMIS"
LOGGING
DATAFILE 'E:\oracle\product\\oradata\jzmis\XNMIS.dbf' SIZE 2048M REUSE
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
;

'E:\oracle\admin\cmid\JTAIS.ora' （path)
"CTAIS" (表空间名）

　　3、数据库对象：
　　　select * from dba_objects;
　　　CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGEPACKAGE BODY、
　　　PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、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_columns
　　　　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、视图：
　　　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 users,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_nbr@dbl_spnew
　　　select * 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';
　　建立出错：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_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;
　　（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，但不能超过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$rollback_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 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';
　　比多回滚段状态status，回滚段所属实例instance_num
　　查优化值optimal
　　　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;
　　当事务处理完毕，再次查询$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/65536)=R.USN
　　　　　and l.type='TX'
　　　　　and l.lmode=6
　　　order 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;
　　使用包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分钟使用包exec dbms_job.remove(21)删除21号作业。

安装Oracle后，经常使用的修改表空间的SQL代码(1)
配置：
Windows NT 4.0 中文版
5块10.2GB SCSI硬盘
分：C:盘、D:盘、E:盘、F:盘、G:盘
Oracle for Windows NT
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> col owner format a20
SQL> col status format a10
SQL> col segment_name format a20
SQL> col tablespace_name format a20
SQL> SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M
  2  FROM DBA_SEGMENTS
  3  WHERE SEGMENT_TYPE='ROLLBACK'
  4  GROUP BY OWNER,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> SELECT SEGMENT_NAME,OWNER,
<>
安装Oracle后，经常使用的修改表空间的SQL代码(4)   2         TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS
  3         FROM DBA_ROLLBACK_SEGS
  4  /
SEGMENT_NAME         OWNER                TABLESPACE_NAME      SEGMENT_ID   FILE_ID STATUS
-------------------- -------------------- -------------------- ---------- --------- ----------
SYSTEM               SYS                  SYSTEM                        0         1 ONLINE
RB_TEMP              SYS                  SYSTEM                        1         1 OFFLINE
RB1                  PUBLIC               ROLLBACK_DATA                 2         3 ONLINE
RB2                  PUBLIC               ROLLBACK_DATA                 3         3 ONLINE
安装Oracle后，经常使用的修改表空间的SQL代码(5) RB3                  PUBLIC               ROLLBACK_DATA                 4         3 ONLINE
RB4                  PUBLIC               ROLLBACK_DATA                 5         3 ONLINE
RB5                  PUBLIC               ROLLBACK_DATA                 6         3 ONLINE
RB6                  PUBLIC               ROLLBACK_DATA                 7         3 OFFLINE
RB7                  PUBLIC               ROLLBACK_DATA                 8         3 OFFLINE
RB8                  PUBLIC               ROLLBACK_DATA                 9         3 OFFLINE
<>
安装Oracle后，经常使用的修改表空间的SQL代码(6) RB9                  PUBLIC               ROLLBACK_DATA                10         3 OFFLINE
RB10                 PUBLIC               ROLLBACK_DATA                11         3 OFFLINE
RB11                 PUBLIC               ROLLBACK_DATA                12         3 OFFLINE
RB12                 PUBLIC               ROLLBACK_DATA                13         3 OFFLINE
RB13                 PUBLIC               ROLLBACK_DATA                14         3 OFFLINE
RB14                 PUBLIC               ROLLBACK_DATA                15         3 OFFLINE
<>
安装Oracle后，经常使用的修改表空间的SQL代码(7) RB15                 PUBLIC               ROLLBACK_DATA                16         3 OFFLINE
RB16                 PUBLIC               ROLLBACK_DATA                17         3 OFFLINE
查询到18记录.
2、修改代码如下，可把以下代码存入一.sql文件，如cg_sys.sql，然后以SQL> @cg_sys.sql调用执行。
--注意：各个硬盘上要事先建好oradata目录
--修改现有回滚段，使之失效，下线
alter 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 rb9 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 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 segment rb13;
drop rollback segment rb14;
drop rollback segment rb15;
drop rollback segment rb16;
--建数据表空间1
<>
安装Oracle后，经常使用的修改表空间的SQL代码(8) --收款、库存、订货、远程通信
create tablespace USER_DATA1 datafile
'd:\oradata\user1_1.ora' size 512M,
'd:\oradata\user1_2.ora' size 512M,
'd:\oradata\user1_3.ora' size 512M,
'd:\oradata\user1_4.ora' size 512M,
'd:\oradata\user1_5.ora' size 512M,
'd:\oradata\user1_6.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--initial 128K，因为，用户建在表空间上，而表建在用户里，为用户所拥有，
--用户继承数据表空间的存储参数，表继承用户的存储参数
--如果initial设的过大，如：5M，则每建一个空表就要占用5M的空间，即使一条记录也没有
--AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED，设置数据文件自动扩展，每一次扩展增加5M，最大空间不受限
--建数据表空间2
--物价、人事、结算、财务、总经理、合同、统计
create tablespace USER_DATA2 datafile
'e:\oradata\user2_1.ora' size 512M,
'e:\oradata\user2_2.ora' size 512M,
'e:\oradata\user2_3.ora' size 512M,
'e:\oradata\user2_4.ora' size 512M,
'e:\oradata\user2_5.ora' size 512M,
'e:\oradata\user2_6.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建索引表空间1
create tablespace INDEX_DATA1 datafile
'f:\oradata\index1_1.ora' size 512M,
'f:\oradata\index1_2.ora' size 512M,
'f:\oradata\index1_3.ora' size 512M,
'f:\oradata\index1_4.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建索引表空间2
create tablespace INDEX_DATA2 datafile
'g:\oradata\index2_1.ora' size 512M,
'g:\oradata\index2_2.ora' size 512M,
'g:\oradata\index2_3.ora' size 512M,
'g:\oradata\index2_4.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
--建回滚表空间1
--设置初始值40M（initial 40M），则每在这个表空间中建一个回滚段，
--此回滚段自动继承此回滚表空间的存储参数，也即默认文件为40M
create tablespace ROLLBACK_DATA1 datafile
'd:\oradata\roll1_1.ora' size 512M，
'd:\oradata\roll1_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);
--建回滚表空间2
create tablespace ROLLBACK_DATA2 datafile
<>
安装Oracle后，经常使用的修改表空间的SQL代码(9) 'e:\oradata\roll2_1.ora' size 512M,
'e:\oradata\roll2_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);
--建临时表空间1
create tablespace TEMPORARY_DATA1 datafile
'f:\oradata\temp1_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);
--建临时表空间2
create tablespace TEMPORARY_DATA2 datafile
'g:\oradata\temp2_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);
--使其真正成为临时的
alter tablespace TEMPORARY_DATA1 temporary;
alter tablespace TEMPORARY_DATA2 temporary;
--建立新的回滚段，每个都一样大，不同大小的回滚段没有什么意义，系统是随机选择的。
--建多少个，根据并发访问用户的多少，
--如果你们公司每天有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 tablespace rollback_da