MYSQL网页编程之oracle里的经常使用命令
MySQL的支持者们却认为，MySQL所保留的功能都是精华，并且是你部署、MySQL学习教程配置和维护这个数据库所必不可少的一些功能。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;
DBaaS向客户提供了许多与其他云服务相类似的优势：一个灵活的、可扩展的MySQL学习教程、按需服务的平台，它以自助服务和便捷管理为导向，可以对环境中的资源进行调配。 入门没那么困难，精通没那么容易 至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。 而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~ varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。 XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！） 我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力 在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。
页:
[1]