oracle常用命令大全.docx

oracle常用命令大全.docx

《oracle常用命令大全.docx》由会员分享，可在线阅读，更多相关《oracle常用命令大全.docx（15页珍藏版）》请在冰豆网上搜索。

oracle常用命令大全

oracle里的常用命令

第一章：

日志管理

1.forcinglogswitches

sql>altersystemswitchlogfile;

2.forcingcheckpoints

sql>altersystemcheckpoint;

3.addingonlineredologgroups

sql>alterdatabaseaddlogfile[group4]

sql>（'/disk3/log4a.rdo','/disk4/log4b.rdo'）size1m;

4.addingonlineredologmembers

sql>alterdatabaseaddlogfilemember

sql>'/disk3/log1b.rdo'togroup1,

sql>'/disk4/log2b.rdo'togroup2;

5.changesthenameoftheonlineredologfile

sql>alterdatabaserenamefile'c:

/oracle/oradata/oradb/redo01.log'

sql>to'c:

/oracle/oradata/redo01.log';

6.droponlineredologgroups

sql>alterdatabasedroplogfilegroup3;

7.droponlineredologmembers

sql>alterdatabasedroplogfilemember'c:

/oracle/oradata/redo01.log';

8.clearingonlineredologfiles

sql>alterdatabaseclear[unarchived]logfile'c:

/oracle/log2a.rdo';

9.usinglogmineranalyzingredologfiles

a.intheinit.oraspecifyutl_file_dir=''

b.sql>executedbms_logmnr_d.build（'oradb.ora','c:

\oracle\oradb\log'）;

c.sql>executedbms_logmnr_add_logfile（'c:

\oracle\oradata\oradb\redo01.log',

sql>dbms_logmnr.new）;

d.sql>executedbms_logmnr.add_logfile（'c:

\oracle\oradata\oradb\redo02.log',

sql>dbms_logmnr.addfile）;

e.sql>executedbms_logmnr.start_logmnr（dictfilename=>'c:

\oracle\oradb\log\oradb.ora'）;

f.sql>select*fromv$logmnr_contents（v$logmnr_dictionary,v$logmnr_parameters

sql>v$logmnr_logs）;

g.sql>executedbms_logmnr.end_logmnr;

第二章：

表空间管理

1.createtablespaces

sql>createtablespacetablespace_namedatafile'c:

\oracle\oradata\file1.dbf'size100m,

sql>'c:

\oracle\oradata\file2.dbf'size100mminimumextent550k[logging/nologging]

sql>defaultstorage（initial500knext500kmaxextents500pctinccease0）

sql>[online/offline][permanent/temporary][extent_management_clause]

2.locallymanagedtablespace

sql>createtablespaceuser_datadatafile'c:

\oracle\oradata\user_data01.dbf'

sql>size500mextentmanagementlocaluniformsize10m;

3.temporarytablespace

sql>createtemporarytablespacetemptempfile'c:

\oracle\oradata\temp01.dbf'

sql>size500mextentmanagementlocaluniformsize10m;

4.changethestoragesetting

sql>altertablespaceapp_dataminimumextent2m;

sql>altertablespaceapp_datadefaultstorage（initial2mnext2mmaxextents999）;

5.takingtablespaceofflineoronline

sql>altertablespaceapp_dataoffline;

sql>altertablespaceapp_dataonline;

6.read_onlytablespace

sql>altertablespaceapp_datareadonly|write;

7.dropingtablespace

sql>droptablespaceapp_dataincludingcontents;

8.enableingautomaticextensionofdatafiles

sql>altertablespaceapp_dataadddatafile'c:

\oracle\oradata\app_data01.dbf'size200m

sql>autoextendonnext10mmaxsize500m;

9.changethesizefodatafilesmanually

sql>alterdatabasedatafile'c:

\oracle\oradata\app_data.dbf'resize200m;

10.Movingdatafiles:

altertablespace

sql>altertablespaceapp_datarenamedatafile'c:

\oracle\oradata\app_data.dbf'

sql>to'c:

\oracle\app_data.dbf';

11.movingdatafiles:

alterdatabase

sql>alterdatabaserenamefile'c:

\oracle\oradata\app_data.dbf'

sql>to'c:

\oracle\app_data.dbf';

第三章：

表

1.createatable

sql>createtabletable_name（columndatatype,columndatatype]....）

sql>tablespacetablespace_name[pctfreeinteger][pctusedinteger]

sql>[initransinteger][maxtransinteger]

sql>storage（initial200knext200kpctincrease0maxextents50）

sql>[logging|nologging][cache|nocache]

2.copyanexistingtable

sql>createtabletable_name[logging|nologging]assubquery

3.createtemporarytable

sql>createglobaltemporarytablexay_tempasselect*fromxay;

oncommitpreserverows/oncommitdeleterows

4.pctfree=（averagerowsize-initialrowsize）*100/averagerowsize

pctused=100-pctfree-（averagerowsize*100/availabledataspace）

5.changestorageandblockutilizationparameter

sql>altertabletable_namepctfree=30pctused=50storage（next500k

sql>minextents2maxextents100）;

6.manuallyallocatingextents

sql>altertabletable_nameallocateextent（size500kdatafile'c:

/oracle/data.dbf'）;

7.movetablespace

sql>altertableemployeemovetablespaceusers;

8.deallocateofunusedspace

sql>altertabletable_namedeallocateunused[keepinteger]

9.truncateatable

sql>truncatetabletable_name;

10.dropatable

sql>droptabletable_name[cascadeconstraints];

11.dropacolumn

sql>altertabletable_namedropcolumncommentscascadeconstraintscheckpoint1000;

altertabletable_namedropcolumnscontinue;

12.markacolumnasunused

sql>altertabletable_namesetunusedcolumncommentscascadeconstraints;

altertabletable_namedropunusedcolumnscheckpoint1000;

altertableordersdropcolumnscontinuecheckpoint1000

data_dictionary:

dba_unused_col_tabs

第四章：

索引

1.creatingfunction-basedindexes

sql>createindexsummit.item_quantityonsummit.item（quantity-quantity_shipped）;

2.createaB-treeindex

sql>create[unique]indexindex_nameontable_name（column,..asc/desc）tablespace

sql>tablespace_name[pctfreeinteger][initransinteger][maxtransinteger]

sql>[logging|nologging][nosort]storage（initial200knext200kpctincrease0

sql>maxextents50）;

3.pctfree（index）=（maximumnumberofrows-initialnumberofrows）*100/maximumnumberofrows

4.creatingreversekeyindexes

sql>createuniqueindexxay_idonxay（a）reversepctfree30storage（initial200k

sql>next200kpctincrease0maxextents50）tablespaceindx;

5.createbitmapindex

sql>createbitmapindexxay_idonxay（a）pctfree30storage（initial200knext200k

sql>pctincrease0maxextents50）tablespaceindx;

6.changestorageparameterofindex

sql>alterindexxay_idstorage（next400kmaxextents100）;

7.allocatingindexspace

sql>alterindexxay_idallocateextent（size200kdatafile'c:

/oracle/index.dbf'）;

8.alterindexxay_iddeallocateunused;

第五章：

约束

1.defineconstraintsasimmediateordeferred

sql>altersessionsetconstraint[s]=immediate/deferred/default;

setconstraint[s]constraint_name/allimmediate/deferred;

2.sql>droptabletable_namecascadeconstraints

sql>droptablespacetablespace_nameincludingcontentscascadeconstraints

3.defineconstraintswhilecreateatable

sql>createtablexay（idnumber（7）constraintxay_idprimarykeydeferrable

sql>usingindexstorage（initial100knext100k）tablespaceindx）;

primarykey/unique/referencestable（column）/check

4.enableconstraints

sql>altertablexayenablenovalidateconstraintxay_id;

5.enableconstraints

sql>altertablexayenablevalidateconstraintxay_id;

第六章：

LOAD数据

1.loadingdatausingdirect_loadinsert

sql>insert/*+append*/intoempnologging

sql>select*fromemp_old;

2.paralleldirect-loadinsert

sql>altersessionenableparalleldml;

sql>insert/*+parallel（emp,2）*/intoempnologging

sql>select*fromemp_old;

3.usingsql*loader

sql>sqlldrscott/tiger\

sql>control=ulcase6.ctl\

sql>log=ulcase6.logdirect=true

第七章：

reorganizingdata

1.usingexpoty

$expscott/tigertables（dept,emp）file=c:

\emp.dmplog=exp.logcompress=ndirect=y

2.usingimport

$impscott/tigertables（dept,emp）file=emp.dmplog=imp.logignore=y

3.transportingatablespace

sql>altertablespacesales_tsreadonly;

$expsys/..file=xay.dmptransport_tablespace=ytablespace=sales_ts

triggers=nconstraints=n

$copydatafile

$impsys/..file=xay.dmptransport_tablespace=ydatafiles=（/disk1/sles01.dbf,/disk2

/sles02.dbf）

sql>altertablespacesales_tsreadwrite;

4.checkingtransportset

sql>DBMS_tts.transport_set_check（ts_list=>'sales_ts'..,incl_constraints=>true）;

在表transport_set_violations中查看

sql>dbms_tts.isselfcontained为true是，表示自包含

第八章：

managingpasswordsecurityandresources

1.controllingaccountlockandpassword

sql>alteruserjunckyidentifiedbyoracleaccountunlock;

2.user_providedpasswordfunction

sql>function_name（useridinvarchar2（30）,passwordinvarchar2（30）,

old_passwordinvarchar2（30））returnboolean

3.createaprofile:

passwordsetting

sql>createprofilegrace_5limitfailed_login_attempts3

sql>password_lock_timeunlimitedpassword_life_time30

sql>password_reuse_time30password_verify_functionverify_function

sql>password_grace_time5;

4.alteringaprofile

sql>alterprofiledefaultfailed_login_attempts3

sql>password_life_time60password_grace_time10;

5.dropaprofile

sql>dropprofilegrace_5[cascade];

6.createaprofile:

resourcelimit

sql>createprofiledeveloper_proflimitsessions_per_user2

sql>cpu_per_session10000idle_time60connect_time480;

7.view=>resource_cost:

alterresourcecost

dba_Users,dba_profiles

8.enableresourcelimits

sql>altersystemsetresource_limit=true;

第九章：

Managingusers

1.createauser:

databaseauthentication

sql>createuserjunckyidentifiedbyoracledefaulttablespaceusers

sql>temporarytablespacetempquota10mondatapasswordexpire

sql>[accountlock|unlock][profileprofilename|default];

2.changeuserquotaontablespace

sql>alteruserjunckyquota0onusers;

3.dropauser

sql>dropuserjuncky[cascade];

4.monitoruser

view:

dba_users,dba_ts_quotas

第十章：

managingprivileges

1.systemprivileges:

view=>system_privilege_map,dba_sys_privs,session_privs

2.grantsystemprivilege

sql>grantcreatesession,createtabletomanagers;

sql>grantcreatesessiontoscottwithadminoption;

withadminoptioncangrantorrevokeprivilegefromanyuserorrole;

3.sysdbaandsysoperprivileges:

sysoper:

startup,shutdown,alterdatabaseopen|mount,alterdatabasebackupcontrolfile,

altertablespacebegin/endbackup,recoverdatabase

alterdatabasearchivelog,restrictedsession

sysdba:

sysoperprivilegeswith