oracle里的常用命令编辑过的Word文档下载推荐.docx
《oracle里的常用命令编辑过的Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《oracle里的常用命令编辑过的Word文档下载推荐.docx（12页珍藏版）》请在冰豆网上搜索。
/oracle/oradata/oradb/redo01.log'
to'
/oracle/oradata/redo01.log'
;
6.droponlineredologgroups
alterdatabasedroplogfilegroup3;
7.droponlineredologmembers
alterdatabasedroplogfilemember'
8.clearingonlineredologfiles
alterdatabaseclear[unarchived]logfile'
/oracle/log2a.rdo'
9.usinglogmineranalyzingredologfiles
a.intheinit.oraspecifyutl_file_dir='
b.sql>
executedbms_logmnr_d.build（'
oradb.ora'
\oracle\oradb\log'
）;
c.sql>
executedbms_logmnr_add_logfile（'
\oracle\oradata\oradb\redo01.log'
dbms_logmnr.new）;
d.sql>
executedbms_logmnr.add_logfile（'
\oracle\oradata\oradb\redo02.log'
dbms_logmnr.addfile）;
e.sql>
executedbms_logmnr.start_logmnr（dictfilename=>
'
\oracle\oradb\log\oradb.ora'
f.sql>
select*fromv$logmnr_contents（v$logmnr_dictionary,v$logmnr_parameters
v$logmnr_logs）;
g.sql>
executedbms_logmnr.end_logmnr;
第二章：
表空间管理
1.createtablespaces
createtablespacetablespace_namedatafile'
\oracle\oradata\file1.dbf'
size100m,
\oracle\oradata\file2.dbf'
size100mminimumextent550k[logging/nologging]
defaultstorage（initial500knext500kmaxextents500pctinccease0）
[online/offline][permanent/temporary][extent_management_clause]
2.locallymanagedtablespace
createtablespaceuser_datadatafile'
\oracle\oradata\user_data01.dbf'
size500mextentmanagementlocaluniformsize10m;
3.temporarytablespace
createtemporarytablespacetemptempfile'
\oracle\oradata\temp01.dbf'
4.changethestoragesetting
altertablespaceapp_dataminimumextent2m;
altertablespaceapp_datadefaultstorage（initial2mnext2mmaxextents999）;
5.takingtablespaceofflineoronline
altertablespaceapp_dataoffline;
altertablespaceapp_dataonline;
6.read_onlytablespace
altertablespaceapp_datareadonly|write;
7.dropingtablespace
droptablespaceapp_dataincludingcontents;
8.enableingautomaticextensionofdatafiles
altertablespaceapp_dataadddatafile'
\oracle\oradata\app_data01.dbf'
size200m
autoextendonnext10mmaxsize500m;
9.changethesizefodatafilesmanually
alterdatabasedatafile'
\oracle\oradata\app_data.dbf'
resize200m;
10.Movingdatafiles:
altertablespace
altertablespaceapp_datarenamedatafile'
\oracle\app_data.dbf'
11.movingdatafiles:
alterdatabase
第三章：
表
1.createatable
createtabletable_name（columndatatype,columndatatype]....）
tablespacetablespace_name[pctfreeinteger][pctusedinteger]
[initransinteger][maxtransinteger]
storage（initial200knext200kpctincrease0maxextents50）
[logging|nologging][cache|nocache]
2.copyanexistingtable
createtabletable_name[logging|nologging]assubquery
3.createtemporarytable
createglobaltemporarytablexay_tempasselect*fromxay;
oncommitpreserverows/oncommitdeleterows
4.pctfree=（averagerowsize-initialrowsize）*100/averagerowsize
pctused=100-pctfree-（averagerowsize*100/availabledataspace）
5.changestorageandblockutilizationparameter
altertabletable_namepctfree=30pctused=50storage（next500k
minextents2maxextents100）;
6.manuallyallocatingextents
altertabletable_nameallocateextent（size500kdatafile'
/oracle/data.dbf'
7.movetablespace
altertableemployeemovetablespaceusers;
8.deallocateofunusedspace
altertabletable_namedeallocateunused[keepinteger]
9.truncateatable
truncatetabletable_name;
10.dropatable
droptabletable_name[cascadeconstraints];
11.dropacolumn
altertabletable_namedropcolumncommentscascadeconstraintscheckpoint1000;
altertabletable_namedropcolumnscontinue;
12.markacolumnasunused
altertabletable_namesetunusedcolumncommentscascadeconstraints;
altertabletable_namedropunusedcolumnscheckpoint1000;
altertableordersdropcolumnscontinuecheckpoint1000
data_dictionary:
dba_unused_col_tabs
第四章：
索引
1.creatingfunction-basedindexes
createindexsummit.item_quantityonsummit.item（quantity-quantity_shipped）;
2.createaB-treeindex
create[unique]indexindex_nameontable_name（column,..asc/desc）tablespace
tablespace_name[pctfreeinteger][initransinteger][maxtransinteger]
[logging|nologging][nosort]storage（initial200knext200kpctincrease0
maxextents50）;
3.pctfree（index）=（maximumnumberofrows-initialnumberofrows）*100/maximumnumberofrows
4.creatingreversekeyindexes
createuniqueindexxay_idonxay（a）reversepctfree30storage（initial200k
next200kpctincrease0maxextents50）tablespaceindx;
5.createbitmapindex
createbitmapindexxay_idonxay（a）pctfree30storage（initial200knext200k
pctincrease0maxextents50）tablespaceindx;
6.changestorageparameterofindex
alterindexxay_idstorage（next400kmaxextents100）;
7.allocatingindexspace
alterindexxay_idallocateextent（size200kdatafile'
/oracle/index.dbf'
8.alterindexxay_iddeallocateunused;
第五章：
约束
1.defineconstraintsasimmediateordeferred
altersessionsetconstraint[s]=immediate/deferred/default;
setconstraint[s]constraint_name/allimmediate/deferred;
2.sql>
droptabletable_namecascadeconstraints
droptablespacetablespace_nameincludingcontentscascadeconstraints
3.defineconstraintswhilecreateatable
createtablexay（idnumber（7）constraintxay_idprimarykeydeferrable
usingindexstorage（initial100knext100k）tablespaceindx）;
primarykey/unique/referencestable（column）/check
4.enableconstraints
altertablexayenablenovalidateconstraintxay_id;
5.enableconstraints
altertablexayenablevalidateconstraintxay_id;
第六章：
LOAD数据
1.loadingdatausingdirect_loadinsert
insert/*+append*/intoempnologging
select*fromemp_old;
2.paralleldirect-loadinsert
altersessionenableparalleldml;
insert/*+parallel（emp,2）*/intoempnologging
3.usingsql*loader
sqlldrscott/tiger\
control=ulcase6.ctl\
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
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）
altertablespacesales_tsreadwrite;
4.checkingtransportset
DBMS_tts.transport_set_check（ts_list=>
sales_ts'
..,incl_constraints=>
true）;
在表transport_set_violations中查看
dbms_tts.isselfcontained为true是，表示自包含
第八章：
managingpasswordsecurityandresources
1.controllingaccountlockandpassword
alteruserjunckyidentifiedbyoracleaccountunlock;
2.user_providedpasswordfunction
function_name（useridinvarchar2（30）,passwordinvarchar2（30）,
old_passwordinvarchar2（30））returnboolean
3.createaprofile:
passwordsetting
createprofilegrace_5limitfailed_login_attempts3
password_lock_timeunlimitedpassword_life_time30
password_reuse_time30password_verify_functionverify_function
password_grace_time5;
4.alteringaprofile
alterprofiledefaultfailed_login_attempts3
password_life_time60password_grace_time10;
5.dropaprofile
dropprofilegrace_5[cascade];
6.createaprofile:
resourcelimit
createprofiledeveloper_proflimitsessions_per_user2
cpu_per_session10000idle_time60connect_time480;
7.view=>
resource_cost:
alterresourcecost
dba_Users,dba_profiles
8.enableresourcelimits
altersystemsetresource_limit=true;
第九章：
Managingusers
1.createauser:
databaseauthentication
createuserjunckyidentifiedbyoracledefaulttablespaceusers
temporarytablespacetempquota10mondatapasswordexpire
[accountlock|unlock][profileprofilename|default];
2.changeuserquotaontablespace
alteruserjunckyquota0onusers;
3.dropauser
dropuserjuncky[cascade];
4.monitoruser
view:
dba_users,dba_ts_quotas
第十章：
managingprivileges
1.systemprivileges:
view=>
system_privilege_map,dba_sys_privs,session_privs
2.grantsystemprivilege
grantcreatesession,createtabletomanagers;
grantcreatesessiontoscottwithadminoption;
withadminoptioncangrantorrevokeprivilegefromanyuserorrole;
3.sysdbaandsysoperprivileges:
sysoper:
startup,shutdown,alterdatabaseopen|mount,alterdatabasebackupcontrolfile,
altertablespacebegin/endbackup,recoverdatabase
alterdatabasearchivelog,restrictedsession
sysdba:
sysoperprivilegesw
升级会员 