Oracle常用命令

 

记不清楚的时候可以过来查查~~~~~~~~~~

 

（一）Alter:

（1）、alter cluster

alter cluster clu_name size 4k;

alter cluster clu_name deallocate unused keep 1m;

（2）、alter database（修改数据文件）

Alter database datafile 4 offline;

Alter database datafile ‘url’ offline;

Alter database datafile ‘url’ resize 100m;

Alter database datafile ‘url’ autoextend on next 100mmaxsize

1g;

Alter database datafile 4 begin/end backup;

（3）、alter database（修改临时文件）

Alter database tempfile 4 resize 100m;

Alter database tempfile 4 autoextend on next 100 maxsize1g;

Alter database tempfile 4 drop including datafiles;

Alter database tempfile 4 offline;

（4）、alter database（归档日志模式命令）

Alter database archivelog;

Alter database noarchivelog;

Alter database force logging;

Alter database clear logfile ‘url’;

Alter database clear unarchived logfile ‘url’;

Alter database add supplemental log data;

Alter database add supplemental log data (primary

key,unique);

Alter database drop supplemental log data;

（5）、alter database（控制文件操作）

Alter database backup controlfile to trace;

Alter database backup controlfile to trace as ‘url’reuse

resetlogs;

Alter database backup controlfile to ‘url’;

（6）、alter database（创建数据文件）

Alter database create datafile ‘url’ as ‘url’;

Alter database create datafile 4 as ‘url’;

Alter database create datafile ‘url’ as new;

（7）、alter database（日志文件命令）

Alter database add logfile group 2 (‘url1’,’url2’,…)size

300m reuse;

Alter database add logfile member ‘url’ to group 2;

Alter database add logfile thread 3 group 2(‘url1,’url2’,…)

size 30m reuse;

Alter database drop logfile group 3;

Alter database drop logfile member ‘url’;

（8）、alter database（安装打开数据库）

Alter database mount;

Alter database open;

Alter database open read only;

Alter database open resetlogs;

（9）、alter database（移动、重命名数据文件或联机日志）

Alter database rename file ‘url’ to ‘url’;

（10）、alter function（重新编译函数）

Alter function fun_name compile;

（11）、alter index（分配和释放区）

Alter index idx_name allocate extent;

Alter index idx_name allocate extent datafile ‘url’;

Alter index idx_name deallocate unused;

Alter index idx_name deallocate unused keep 100m;

（12）、Alter index（杂项的维护操作）

Alter index idx_name parallel 3/noparallel;

Alter index idx_name compress/nocompress;

Alter index idx_name logging/nologging;

（13）、alter index（修改存储和物理属性）

Alter index idx_name pctfree 10 pctused 40 initrans 5storage

(next 100k maxextents unlimited freelists 10 buffer_pool

keep);

（14）、alter index（分区—-添加）

Alter index idx_name add partition tablespace tbs_name;

（15）、alter index（分区—-合并）

Alter index idx_name coalesce partition;

（16）、alter index（分区—-删除）

Alter index idx_name drop partition par_name;

（17）、alter index（分区—-修改）

Alter index idx_name modify default attributes forpartition

idx_name pctfree 10 pctused 40 tablespace tbs_namenologging

compress;

Alter index idx_name modify partition par_namedeallocate

unused keep 100m;

Alter index idx_name modify partition par_name allocate

extent size 100m;

Alter index idx_name modify partition par_name pctused40

storage(next 50m) nologging;

Alter index idx_name modify subpartition spar_namepctused

40 storage(next 50m) nologging;

Alter index idx_name modify subpartition spar_name

deallocate unused keep 100m;

Alter index idx_name modify subpartition spar_nameallocate

extent size 100m;

（18）、alter index（分区—-重命名）Alter index idx_name rename partitionpar_name to par_name;

Alter index idx_name rename subpartition spar_name to

spar_name;

（19）、alter index（分区—-分割）

Alter index idx_name split partition par_name at

(’15-JAN-05’) into partition par_name tablespacetbs_name

storage(initial 100m next 50m freelists 5);

（20）、alter index（重建无分区索引）

Alter index idx_name rebuild online;

Alter index idx_name rebuild online tablespace tbs_name

pctfree 1 storage (initial 50m next 50m freelists 5)compute

statistics parallel 0;

（21）、alter index（重建分区）

Alter index idx_name rebuild partition par_name online;

Alter index idx_name rebuild subpartition spar_nameonline

pctfree 1 storage (initial 50m next 50m freelists 5)compute

statistics parallel 0;

（22）、alter index（重命名）

Alter index idx_name rename to idx_name;

（23）、alter index（缩小操作）

Alter index idx_name shrink space;

Alter index idx_name shrink compact cascade;

（24）、alter materialized view（分配和释放区）

alter materialized view allocate extent;

alter materialized view mv_name deallocate unused;

（25）、alter materialized view（杂项）

Alter materialized view mv_name compress/nocompress;

Alter materialized view mv_name parallel 2;

Alter materialized view mv_name logging/nologging;

Alter materialized view mv_name consider fresh;

Alter materialized view mv_name enable query rewrite;

（26）、alter materialized view（物理属性和存储）

Alter materialized view mv_name pctfree 5 pcrused 60

Storage (next 100m freelists 5);

（27）、alter materialized view（更新）

Alter materialized view mv_name refresh fast;

Alter materialized view mv_name refresh complete;

Alter materialized view mv_name refresh fast on demand;

Alter materialized view mv_name refresh fast on commit;

Alter materialized view mv_name refresh complete startwith

sysdate;

Alter materialized view mv_name refresh complete startwith

sysdate next sysdate+1/24;

（28）、alter materialized view（缩小空间）

Alter materialized view mv_name shrink space;

Alter materialized view mv_name shrink apce compactcascade;

（29）、alter materialized view log（添加组件）

alter materialized view log on my_tab add primary key;

alter materialized view log on my_tab add (col1,col2,…)

including new values;

alter materialized view log on my_tab add

(col1,col2,…),rowed,sequence including new values;

（30）、alter materialized view log（分配释放区）

alter materialized view log on my_tab parallel 3;

alter materialized view log on my_tab nologging;

alter materialized view log on my_tab shrink space;

（31）、alter materialized view log（物理属性和存储）

alter materialized view log on my_tab pctfree 5 pctused60

storage (next 100m freelists 5);

（32）、alter package（编译）

Alter package pg_name compile;

Alter package pg_name compile specification;

Alter package pg_name compile body;

（33）、alter procedure（编译）

Alter procedure pc_name compile;

（34）、alter profile（杂项）

Alter role my_role identified by password;

Alter role my_role not identified;

（35）、alter profile（修改限制）

Alter profile pf_name limit failed_login_attempts=3

Alter profile pf_name limit password_lock_time=2/24;

Alter profile pf_name limit password_grace_time=5;

Alter profile pf_name limit password_lifetime=60;

Alter profile pf_name limit password_reuse_time=365

password_reuse_max=3;

Alter profile pf_name limit session_per_cpu=10;

Alter profile pf_name limit connect_time=1000;

Alter profile pf_name limit idle_time=60;

Alter profile pf_name limit private_sge=1000000;

（36）、alter rollback segment（联机/脱机）

Alter rollback segment tbs01 offline/online;

（37）、alter rollback segment（缩小操作）

Alter rollback segment rbs01 shrink;

Alter rollback segment rbs01 shrink to 100m;

（38）、alter rollback segment（存储子句）

Alter rollback segment rbs01 storage(next 50m optimal100m);

（39）、alter sequence（杂项）

Alter sequence my_seq increment by -5;

Alter sequence my_seq increment by 1 maxvalue 100 rycle;

Alter sequence my_seq nomaxvalue;

Alter sequence my_seq cache order;

Alter sequence my_seq increment by 1 minvalue 1 maxvalue500

rycle;

（40）、alter session（启动终止并行操作）

Alter session enable parallel dml parallel 3;

Alter session enable parallel ddl;

Alter session disable parallel query;

（41）、alter session（可恢复的空间管理）

Alter session enable resumable timeout 3600;

Alter session disable resumable;

（42）、alter session（设置会话参数）

Alter session set nls_date_format=’MM/DD/YYYYHH24:MI:SS’;

Alter session set sort_area_size=10000000;

Alter session set query_rewrite_enable=true;

Alter session set resumable_timeout=3600;

Alter session set skip_unusable_indexes=true;

Alter session set sql_trace=true;

（43）、alter system（日志文件和归档文件管理）

Alter system switch logfile;

Alter system archive log start/stop/all;

Alter system archive log thread 1 all;

Alter system archive log all to ‘url’;

（44）、alter system（设置系统参数）

Alter system set db_cache_size=325m comment=’this is …’

scope=both;

Alter system set compatible=10.0.0 comment=’xxx’

scope=spfile;

（45）、alter system（系统管理）

Alter system checkpoint global;

Alter system kill session ‘145,334’;

Alter system enable restricted session;

Alter system disable restricted session;

Alter system suspend;

Alter system quiesce restricted;

Alter system unquiesce;

Alter system resume;

Alter system flush shared_pool;

Alter system flush buffer_cache;

（46）、alter table（外部表操作）

Alter table ext_tb reject limit 500;

Alter table ext_tb default directory dr_name;

Alter table ext_tb access parameters (fields terminatedby

‘,’);

Alter table ext_tb location (‘tb01.txt’,’tb02.txt’,…);

Alter table ext_tb add column (ssn number);

（47）、alter table（移动表格）

Alter table tb_name move tablesapce tbs_name pctfree 10

pctused 60;

（48）、alter table（添加列）

Alter table tb_name add(col_name type,…);

Alter table tb_name add(col_name blob) lob (photo)storage

as xxx (tablespace tbs_name);

（49）、alter table（更改列）

Alter table tb_name modify (col_name type,…);

Alter table tb_name modify (col_name not null/null);

Alter table tb_name modify lob (photo)(storage(freelists

2));

Alter table tb_name modify lob (photo) (pctversion 50);

（50）、alter table（删除列）

Alter table tb_name drop (col_name,…);

（51）、alter table（重命名）

Alter table tb_name rename column col_name to col_name;

（52）、alter table（添加约束）

Alter table tb_name add (constraint con_name check(id>0));

Alter table tb_name add (col_name number default 0);

Alter table tb_name modify (col_name default ‘aaa’/null)

Alter table tb_name add constraint con_name foreign key

(col_name) refrences col_name;

（53）、alter table（添加唯一性主键）

Alter table tb_name add constraint con_name primary

key/unique (id) using index tablespace idx_name storage

(initial 100k next 100k pctincrease 0)

（54）、alter table（表约束更改）

Alter table tb_name disable/enable unique/primary key

(col_name);

Alter table tb_name disable/enable constraint con_name;

Alter table tb_name disable constraint con_name keepindex;

Alter table tb_name disable constraint fk_col_namedisable

primary key keep index;

Alter table tb_name enable novalidate constraintcon_name;

Alter table tb_name enable novalidate primary

key/unique(col_name);

（55）、alter table（删除表约束）

Alter table tb_name drop constraint con_name;

Alter table tb_name drop primary key [cascade];

Alter table tb_name drop unique(col_name);

（56）、alter table（添加表分区）

Alter table tb_name add partition par_name values lessthan

(to_char(’01-APR-2004’,’DD-MON-yyyy’)) tablespacetbs_name

update global indexes;

（57）、alter table（合并表分区）

Alter table tb_name merge partitions par_name,par_nameinto

partition par_name;

（58）、alter table（移动表分区）

Alter table tb_name move partition par_name tablespace

tbs_name storage (initial 100m next 100m pctincrease 0)

update global indexes;

（59）、alter table（删除表分区）

Alter table tb_name drop partition par_name updateglobal

indexes;

（60）、alter table（重命名表分区）

Alter table tb_name rename partition par_name topar_name;

（61）、alter table（分割表分区）

Alter table tb_name split partition par_name at

(to_date(’01-APR-2004’,’DD-MON-yyyy’)) into (partition

par_name [subpartition 4 store in (1,2,3,4)],partition

par_name [subpartition 4 store in (1,2,3,4)]) updateglobal

indexes;

（62）、alter table（截断表分区）

Alter table tb_name truncate partition par_name update

global indexes;

（63）、alter table（表属性）

Alter table tb_name pctfree 10 pctused 60;

Alter table tb_name storage (next 1m);

Alter table tb_name parallel 4;

（64）、alter table（触发器状态）

Alter table tb_name disable/enable all triggers;

（65）、alter tablespace（备份）

Alter tablesapce my_tbs begin/end backup;

（66）、alter tablespace（数据文件和临时文件）

Alter tablespace tbs_name add datafile/tempfile ‘url’;

Alter tablespace tbs_name aautoextend off/on next 100m

maxsize 1g;

（67）、alter tablespace（重命名）

Alter tablespace xxx rename to xxx;

（68）、alter tablespace（表空间管理）

Alter tablespace xxx default storage (initial 100m next100m

freelists 3);

Alter tablespace xxx minimum extent 500k;

Alter tablespace xxx resize 100m;

Alter tablespace xxx coalesce/offline/online;

Alter tablespace xxx read only/write;

Alter tablespace xxx force logging/nologging;

Alter tablespace xxx flashback on/off;

Alter tablespace xxx retention guarantee/noguarantee;

（69）、alter trigger

Alter trigger xxx disable/enable/compile;

Alter trigger xxx rename to xxx;

（70）、alter user

Alter user xxx identified by password;

Alter user xxx identified externally;

Alter user xxx password expire;

Alter user xxx account lock/unlock;

Alter user xxx profile xxx;

Alter user xxx quota xxx/unlimited on xxx;

Alter user xxx default role xxx/null/all except xxx;

Alter user xxx temporary/default tablespace xxx;

（71）、alter view

Alter view xxx add constraint xxx unique(xxx) relydisable

novalidate;

Alter view xxx modify constraint rely/norely;

Alter view xxx drop constraint xxx;

Alter view xxx recompile;