MYSQL网页编程之oracle 经常使用命令

DBaaS系统其实具有更大的市场机遇：像其他云服务一样，DBaaS意味着更短的销售周期，更少的启动费用，持续不断的收入，也意味着比之前更多的客户。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.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>alterdatabaseclear[unarchived]logfilec:/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[logging/nologging]
sql>defaultstorage(initial500knext500kmaxextents500pctinccease0)
sql>[online/offline][permanent/temporary][extent_management_clause]

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[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(size500kdatafilec:/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(size200kdatafilec:/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.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那样还要用子查询进行封装。