oracle 模式管理命令手册.doc

表空间创建表空间create tablespace test datafile C:oracleproduct10.2.0oradataora10gtest01.dbf size 100m-extent management local -空间管理（默认local）-autoallocate 100m -区段分配（默认auto）-uniform size 5m -同一区段分配（默认1M）-segment space management auto -段空间管理，只能用于本地管理表空间（默认auto）-blocksize 8k -块大小（默认为DB_BLOCK_SIZE的值）-查看表空间管理信息select initial_extent,next_extent,extent_management,allocation_type,segment_space_managementfrom dba_tablespaceswhere tablespace_name=TEST;增加表空间大小-增加新的数据文件alter tablespace test add datafile C:oracleproduct10.2.0oradataora10gtest02.dbf size 100m;-扩展已有数据文件alter database datafile C:oracleproduct10.2.0oradataora10gtest01.dbf resize 500m;-表空间自动扩展（只有在创建表空间或增加新数据文件时才能加autoextend)alter tablespace testadd datafile C:oracleproduct10.2.0oradataora10gtest02.dbf size 100mautoextend onnext 10Mmaxsize unlimited;删除表空间-删除包含对象的表空间drop tablespcae test including contents;-同时删除操作系统数据文件drop tablespace test including contents and datafiles;-同时删除引用的完整性约束drop tablespace test cascade contraints;重命名表空间和数据文件-重命名表空间（10g新特性）alter tablespace test01 rename to test02;-重命名数据文件1.alter tablespace test01 offilne normal;2.操作系统命令修改数据文件名或移动到另外的位置3.alter tablespace test01 rename datafile C:oracleproduct10.2.0oradataora10gtest01.dbf to C:oracleproduct10.2.0oradataora10gtest02.dbf;或 alter database rename file C:oracleproduct10.2.0oradataora10gtest01.dbf to C:oracleproduct10.2.0oradataora10gtest02.dbf; -mount状态（针对无法脱机的表空间）修改表空间为只读alter tablespace test read only;alter tablespace test read write;修改表空间为脱机/联机alter tablespace test offline drop; -非归档模式下使用alter tablespace test offline; -归档模式下使用alter tablespace test online释放未用的extent-手动释放alter table test deallocate unused;-删除中同时释放extenttruncate table test drop storage;字典管理表空间转换为本地管理表空间-移植所以对象到本地管理表空间alter table test01 move tablespace test02;alter index test01_pk_idx rebuild tablespace test02;-使用DBMS_SPACE_ADMIN转换（可联机转换，system表空间必须最后转换）execute dbms_space_admin.tablespace_migrate_local(TEST01);创建回滚表空间create undo tablespace undo01datafile C:oracleproduct10.2.0oradataora10gundo01.dbf size 500mretention guarantee;-强制撤销保留创建临时表空间create temporary tablespace temp01tempfile C:oracleproduct10.2.0oradataora10gtemp01.dbf size 500mautoextend on;-临时表空间信息v$sort_segmentv$tempseg_usage修改默认的临时表空间alter tablespace default temporary tablespace temp_test;创建临时表空间组（10g新特性）-创建临时表空间时创建临时表空间组create temporary tablespace temp01tempfile C:oracleproduct10.2.0oradataora10gtemp01.dbf size 100mtablespace group tmpgrp1; -默认不属于任何组-修改时创建临时表空间组alter tablespace temp01 tablespace group tmpgrp1;-临时表空间组信息dba_tablespace_groups;将临时表空间组设为数据库默认的临时表空间alter database default temporary tablespace tmpgrp1;设定默认的永久表空间1. 建库时使用default tablespace子句2. alter database default tablespace test;-查看当前的默认永久表空间select property_name,property_value from database_properties;创建大文件表空间（一对一）-建库时设置默认表空间为BFTcreate databaseset default bigfile tablespace-创建表空间时指定BFTcreate bigfile tablespace testdatafile C:oracleproduct10.2.0oradataora10gtest01.dbf size 100G;-改变默认表空间类型atler tablespace set default bigfile tablespace;更改大文件表空间alter tablespace bigtbs resize 120G;alter tablespace bigtbs autoextend on next 20G; -小文件表空间不支持该操作表删除表-删除表drop table test01;-cascade constraints; -将完整性约束一起删除-purge; -同时删除回收站中的信息（10g新特性）-删除表列alter table test01 drop (a);-标记列为无用后删除（对大数据量的列）alter table test01 set unused(a,b);alter table test01 drop unused columns;alter table test01 drop unused columns checkpoint 1000; -减少undo的产生重命名-重命名表alter table test01 rename to test02-重命名列alter table test01 rename column a to b;使用CTAS创建表create table test02as select * from test01parallel degree 4 -使用并行创建，提高创建速度nologging; -不写重做日志，提高创建速度创建临时表create global temporary table temp_tab(a number)-on commit preserve rows; -对整个会话保留数据-on commit delete rows; -对整个事物保留数据（默认）创建索引组织表create table idx_tab(id number,name varchar2(20),constraint pk_id primary key(id)organiztion index pctthreshold 25 -预留空间阀值overflow tablespace overflow_tables; -溢出数据存放的表空间创建分区表-创建范围分区表create table range_tab(id number, year int not null, month int not null)partition by range(year,month)(partition p1 values less than(2008,04) tablespace ts1, partition p2 values less than(2008,07) tablespace ts2, partition p3 values less than(2008,10) tablespace ts3, partition p4 values less than(2009,01) tablespace ts4);-创建散列分区表create table hash_tab(id number, year int not null, month int not null)partition by hash(id)partitions 4store in(ts1,ts2,ts3,ts4);-创建列表分区表create table list_tab(id number, city varchar2(20)partition by list(city)(partition north values(BJ,DL) tablespace ts1,partition south values(SH,HZ) tablespace ts2;-创建范围-散列分区表create table range_hash_tab(x number, y number)partition by range(x) subpartition by hash(y)subpartitions 8 store in (ts1,ts2,ts3,ts4) -总共32个分区(partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (3000), partition p4 values less than (maxvalue);-创建范围-列表分区create table range_list_tab -总共4个分区(id number, day date, city varchar2(20)partition by range(day)subpartition by list(city)(partition p1 values less than (to_date(2008-04-01,yyyy-mm-dd) tablespace ts1 (subpartition p1_north values (BJ,DL), subpartition p1_south values (SH,HZ),partition p2 values less than (to_date(2008-07-01,yyyy-mm-dd) tablespace ts2(subpartition p2_north values (BJ,DL), subpartition p2_south values (SH,HZ);维护分区表-增加分区alter table range_tabadd partition p5 values less than (2009,04)tablespace ts5;-分割分区（散列无法使用）alter table range_tabsplit partition p1 at (2008,02) into -at后跟分割界限(partition p5,partition p6); -必须是新的分区名-合并分区（散列无法使用）alter table range_tabmerge partitions p5,p6 into p1; -可以是新的分区也可以是已存在的分区-重命名分区alter table rename partition p1 to p5;-交换分区alter table range_tab exchange partition p1 with table t1;-删除分区（散列无法使用）alter table rang_tabdrop partition p1-update global indexes; -更新全局索引，否则无效-接合分区（范围和列表无法使用）alter table hash_tab coalesce partition; -减少一个分区快速获取创建对象的DDL语句select dbms_metadata.get_ddl(TABLE,TEST) from dual; -括号中分别为对象类型和对象名，注意大小写创建集群-创建集群create cluster emp_dept(deptno number(3);-HASH IS deptno HASHKEY 200; -散列集群，200表示散列值的数量-将表加入创建的集群中craete table dept(deptno number(3) primary key)cluster emp_dept (deptno);create table emp(empno number(5) primary key, deptno number(3) references dept)cluster emp_dept(deptno);索引创建索引-B-TREE索引create index id_idx on test(id) compress; -加compress压缩减少大小（I/O）-位图索引create bitmap index gender_idx on test(gender);-反向索引create index reverse_idx on test(id) reverse;-创建全局索引（已散列为例，其他类似）create index id_idx on test(id)global partition by hash(id)(partition p1 tablespace ts1, partition p2 tablespace ts2, partition p3 tablespace ts3, partition p4 tablespace ts4);-创建本地索引create index id_idx on testt(id) localtablespace localindex_01;监控索引-打开监控alter index id_idx monitoring usage; -取消监控使用nomonitoriong-查看使用情况select * from v$object_usagewhere index_name=ID_IDX;维护索引-检查索引的有效性analyze index id_idx validate structure;select blocks, pct_used, distinct_keys,lf_rows, del_lf_rowsfrom index_stats;-重建索引alter index id_idx rebuild;-tablespace idx_tbs; -重建的同时更改索引存放的表空间-reverse/noreverse -B-TREE和反向索引转换alter index id_idx rebuild online; -减少重建时的并发限制-合并索引alter index id_idx coalesce;-重命名索引alter index test.id_idx rename to id_idx_bak;完整性约束创建约束-创建表时创建约束create table test (id number primary key);create table test(id number,constraint pk_id primary key(id); -取约束名，如果约束中有多 个列，则必须使用这种方式create table test (id number constraint fk_id references test02(id) -创建外键约束 deferrable initially deferred/immediate); -指定检查时间-修改表时创建约束alter table test add constraint pk_id primary key(id);-disable validate; -确保表中数据满足约束，然后禁用约束-disable novalidate; -所有数据都不检查有效性-enable validate; -所有数据都检查有效性-enable novalidate; -只对新插入数据检查，原有数据不检查-rely disable novalidate -信赖约束alter table test modify id primary key;-修改约束检查时间set constraint pk_id deferred/immediate;视图创建视图Create view my_test asSelect id,name from test where id=1;创建实体化视图-授权grant create materialized view to scott;grant query rewrite to scott;-创建实体化视图日志（FAST刷新）create materialized view log on test;-创建实体化视图create materialized view test_mvbuild deferred -默认为immediate，创建