第一章：日志管理
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:sysoperprivilegeswithadminoption,createdatabase,recoverdatabaseuntil
4.passwordfilemembers:view:=>v$pwfile_users
5.o7_dictionary_accessibility=truerestrictionaccesstoviewortablesinotherschema
6.revokesystemprivilege
sql>revokecreatetablefromkaren;
sql>revokecreatesessionfromscott;
7.grantobjectprivilege
sql>grantexecuteondbms_pipetopublic;
sql>grantupdate(first_name,salary)onemployeetokarenwithgrantoption;
8.displayobjectprivilege:view=>dba_tab_privs,dba_col_privs
9.revokeobjectprivilege
sql>revokeexecuteondbms_pipefromscott[cascadeconstraints];
10.auditrecordview:=>sys.aud$
11.protectingtheaudittrail
sql>auditdeleteonsys.aud$byaccess;
12.statementauditing
sql>audituser;
13.privilegeauditing
sql>auditselectanytablebysummitbyaccess;
14.schemaobjectauditing
sql>auditlockonsummit.employeebyaccesswheneversuccessful;
15.viewauditoption:view=>all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts
16.viewauditresult:view=>dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement
第十一章：managerrole
1.createroles
sql>createrolesales_clerk;
sql>createrolehr_clerkidentifiedbybonus;
sql>createrolehr_manageridentifiedexternally;
2.modifyrole
sql>alterrolesales_clerkidentifiedbycommission;
sql>alterrolehr_clerkidentifiedexternally;
sql>alterrolehr_managernotidentified;
3.assigningroles
sql>grantsales_clerktoscott;
sql>granthr_clerktohr_manager;
sql>granthr_managertoscottwithadminoption;
4.establishdefaultrole
sql>alteruserscottdefaultrolehr_clerk,sales_clerk;
sql>alteruserscottdefaultroleall;
sql>alteruserscottdefaultroleallexcepthr_clerk;
sql>alteruserscottdefaultrolenone;
5.enableanddisableroles
sql>setrolehr_clerk;
sql>setrolesales_clerkidentifiedbycommission;
sql>setroleallexceptsales_clerk;
sql>setrolenone;
6.removerolefromuser
sql>revokesales_clerkfromscott;
sql>revokehr_managerfrompublic;
7.removerole
sql>droprolehr_manager;
8.displayroleinformation
view:=>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles
第十二章:backupandrecovery
1.v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat
2.rmanneedsetdbwr_io_slavesorbackup_tape_io_slavesandlarge_pool_size
3.monitoringparallelrollback
>v$fast_start_servers,v$fast_start_transactions
4.performacloseddatabasebackup(noarchivelog)
>shutdownimmediate
>cpfiles/backup/
>startup
5.restoretoadifferentlocation
>connectsystem/managerassysdba
>startupmount
>alterdatabaserenamefile'/disk1/../user.dbf'to'/disk2/../user.dbf';
>alterdatabaseopen;
6.recoversyntax
--recoveramounteddatabase
>recoverdatabase;
>recoverdatafile'/disk1/data/df2.dbf';
>alterdatabaserecoverdatabase;
--recoveranopeneddatabase
>recovertablespaceuser_data;
>recoverdatafile2;
>alterdatabaserecoverdatafile2;
7.howtoapplyredologfilesautomatically
>setautorecoveryon
>recoverautomaticdatafile4;
8.completerecovery:
--method1(mounteddatabae)
>copyc:\backup\user.dbfc:\oradata\user.dbf
>startupmount
>recoverdatafile'c:\oradata\user.dbf;
>alterdatabaseopen;
--method2(openeddatabase,initiallyopened,notsystemorrollbackdatafile)
>copyc:\backup\user.dbfc:\oradata\user.dbf(altertablespaceoffline)
>recoverdatafile'c:\oradata\user.dbf'or
>recovertablespaceuser_data;
>alterdatabasedatafile'c:\oradata\user.dbf'onlineor
>altertablespaceuser_dataonline;
--method3(openeddatabase,initiallyclosednotsystemorrollbackdatafile)
>startupmount
>alterdatabasedatafile'c:\oradata\user.dbf'offline;
>alterdatabaseopen
>copyc:\backup\user.dbfd:\oradata\user.dbf
>alterdatabaserenamefile'c:\oradata\user.dbf'to'd:\oradata\user.dbf'
>recoverdatafile'e:\oradata\user.dbf'orrecovertablespaceuser_data;
>altertablespaceuser_dataonline;
--method4(lossofdatafilewithnobackupandhaveallarchivelog)
>altertablespaceuser_dataofflineimmediate;
>alterdatabasecreatedatafile'd:\oradata\user.dbf'as'c:\oradata\user.dbf''
>recovertablespaceuser_data;
>altertablespaceuser_dataonline
5.performanopendatabasebackup
>altertablespaceuser_databeginbackup;
>copyfiles/backup/
>alterdatabasedatafile'/c:/../data.dbf'endbackup;
>altersystemswitchlogfile;
6.backupacontrolfile
>alterdatabasebackupcontrolfileto'control1.bkp';
>alterdatabasebackupcontrolfiletotrace;
7.recovery(noarchivelogmode)
>shutdownabort
>cpfiles
>startup
8.recoveryoffileinbackupmode
>alterdatabasedatafile2endbackup;
9.clearingredologfile
>alterdatabaseclearunarchivedlogfilegroup1;
>alterdatabaseclearunarchivedlogfilegroup1unrecoverabledatafile;
10.redologrecovery
>alterdatabaseaddlogfilegroup3'c:\oradata\redo03.log'size1000k;
>alterdatabasedroplogfilegroup1;
>alterdatabaseopen;
or>cpc:\oradata\redo02.log'c:\oradata\redo01.log
>alterdatabaseclearlogfile'c:\oradata\log01.log'
|
