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

MySQL的支持者们却认为，MySQL所保留的功能都是精华，并且是你部署、MySQL学习教程配置和维护这个数据库所必不可少的一些功能。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;
DBaaS向客户提供了许多与其他云服务相类似的优势：一个灵活的、可扩展的MySQL学习教程、按需服务的平台，它以自助服务和便捷管理为导向，可以对环境中的资源进行调配。

入门没那么困难，精通没那么容易

至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。

而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~

varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。