Oracle常用命令Word文档格式.docx
《Oracle常用命令Word文档格式.docx》由会员分享,可在线阅读,更多相关《Oracle常用命令Word文档格式.docx(18页珍藏版)》请在冰豆网上搜索。
/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:
sysoperprivilegeswithad