MYSQL网页编程之oracle 经常使用命令
DBaaS系统其实具有更大的市场机遇：像其他云服务一样，DBaaS意味着更短的销售周期，更少的启动费用，持续不断的收入，也意味着比之前更多的客户。oracle第一章：日记办理1.forcinglogswitches
sql>altersystemswitchlogfile;
2.forcingcheckpoints
sql>altersystemcheckpoint;
3.addingonlineredologgroups
sql>alterdatabaseaddlogfile
sql>(/disk3/log4a.rdo,/disk4/log4b.rdo)size1m;
4.addingonlineredologmembers
sql>alterdatabaseaddlogfilemember
sql>/disk3/log1b.rdotogroup1,
sql>/disk4/log2b.rdotogroup2;
5.changesthenameoftheonlineredologfile
sql>alterdatabaserenamefilec:/oracle/oradata/oradb/redo01.log
sql>toc:/oracle/oradata/redo01.log;
6.droponlineredologgroups
sql>alterdatabasedroplogfilegroup3;
7.droponlineredologmembers
sql>alterdatabasedroplogfilememberc:/oracle/oradata/redo01.log;
8.clearingonlineredologfiles
sql>alterdatabaseclearlogfilec:/oracle/log2a.rdo;
9.usinglogmineranalyzingredologfiles
a.intheinit.oraspecifyutl_file_dir=
b.sql>executedbms_logmnr_d.build(oradb.ora,c:oracleoradblog);
c.sql>executedbms_logmnr_add_logfile(c:oracleoradataoradbedo01.log,
sql>dbms_logmnr.new);
d.sql>executedbms_logmnr.add_logfile(c:oracleoradataoradbedo02.log,
sql>dbms_logmnr.addfile);
e.sql>executedbms_logmnr.start_logmnr(dictfilename=>c:oracleoradblogoradb.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_namedatafilec:oracleoradatafile1.dbfsize100m,
sql>c:oracleoradatafile2.dbfsize100mminimumextent550k
sql>defaultstorage(initial500knext500kmaxextents500pctinccease0)
sql>
2.locallymanagedtablespace
sql>createtablespaceuser_datadatafilec:oracleoradatauser_data01.dbf
sql>size500mextentmanagementlocaluniformsize10m;
3.temporarytablespace
sql>createtemporarytablespacetemptempfilec:oracleoradata emp01.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_dataadddatafilec:oracleoradataapp_data01.dbfsize200m
sql>autoextendonnext10mmaxsize500m;
9.changethesizefodatafilesmanually
sql>alterdatabasedatafilec:oracleoradataapp_data.dbfresize200m;
10.Movingdatafiles:altertablespace
sql>altertablespaceapp_datarenamedatafilec:oracleoradataapp_data.dbf
sql>toc:oracleapp_data.dbf;
11.movingdatafiles:alterdatabase
sql>alterdatabaserenamefilec:oracleoradataapp_data.dbf
sql>toc:oracleapp_data.dbf;
第三章：表
1.createatable
sql>createtabletable_name(columndatatype,columndatatype]....)
sql>tablespacetablespace_name
sql>
sql>storage(initial200knext200kpctincrease0maxextents50)
sql>
2.copyanexistingtable
sql>createtabletable_nameassubquery
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(size500kdatafilec:/oracle/data.dbf);
7.movetablespace
sql>altertableemployeemovetablespaceusers;
8.deallocateofunusedspace
sql>altertabletable_namedeallocateunused
9.truncateatable
sql>truncatetabletable_name;
10.dropatable
sql>droptabletable_name;
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>createindexindex_nameontable_name(column,..asc/desc)tablespace
sql>tablespace_name
sql>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(size200kdatafilec:/oracle/index.dbf);
8.alterindexxay_iddeallocateunused;
第五章：束缚
1.defineconstraintsasimmediateordeferred
sql>altersessionsetconstraint=immediate/deferred/default;
setconstraintconstraint_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;
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>;
2.changeuserquotaontablespace
sql>alteruserjunckyquota0onusers;
3.dropauser
sql>dropuserjuncky;
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;
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.dbfto/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:ackupuser.dbfc:oradatauser.dbf
>startupmount
>recoverdatafilec:oradatauser.dbf;
>alterdatabaseopen;
--method2(openeddatabase,initiallyopened,notsystemorrollbackdatafile)
>copyc:ackupuser.dbfc:oradatauser.dbf(altertablespaceoffline)
>recoverdatafilec:oradatauser.dbfor
>recovertablespaceuser_data;
>alterdatabasedatafilec:oradatauser.dbfonlineor
>altertablespaceuser_dataonline;
--method3(openeddatabase,initiallyclosednotsystemorrollbackdatafile)
>startupmount
>alterdatabasedatafilec:oradatauser.dbfoffline;
>alterdatabaseopen
>copyc:ackupuser.dbfd:oradatauser.dbf
>alterdatabaserenamefilec:oradatauser.dbftod:oradatauser.dbf
>recoverdatafilee:oradatauser.dbforrecovertablespaceuser_data;
>altertablespaceuser_dataonline;
--method4(lossofdatafilewithnobackupandhaveallarchivelog)
>altertablespaceuser_dataofflineimmediate;
>alterdatabasecreatedatafiled:oradatauser.dbfasc:oradatauser.dbf
>recovertablespaceuser_data;
>altertablespaceuser_dataonline
5.performanopendatabasebackup
>altertablespaceuser_databeginbackup;
>copyfiles/backup/
>alterdatabasedatafile/c:/../data.dbfendbackup;
>altersystemswitchlogfile;
6.backupacontrolfile
>alterdatabasebackupcontrolfiletocontrol1.bkp;
>alterdatabasebackupcontrolfiletotrace;
7.recovery(noarchivelogmode)
>shutdownabort
>cpfiles
>startup
8.recoveryoffileinbackupmode
>alterdatabasedatafile2endbackup;
9.clearingredologfile
>alterdatabaseclearunarchivedlogfilegroup1;
>alterdatabaseclearunarchivedlogfilegroup1unrecoverabledatafile;
10.redologrecovery
>alterdatabaseaddlogfilegroup3c:oradataedo03.logsize1000k;
>alterdatabasedroplogfilegroup1;
>alterdatabaseopen;
or>cpc:oradataedo02.logc:oradataedo01.log
>alterdatabaseclearlogfilec:oradatalog01.log;
业界普遍的声音认为：“MySQL是一个可靠的数据库系统，MySQL学习教程无论是在嵌入式或大型群集系统的部署中，还是在基于Web的应用程序领域。 我个人认为就是孜孜不懈的学习 但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右） 是要和操作系统进行Socket通讯的场景。否则建议慎重！ 备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。 从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。 大侠们有推荐的书籍和学习方法写下吧。 你可以简单地认为适合的就是好，不适合就是不好。 其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。
页:
[1]