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.change
升级会员 