oracle期末考试试题3

《oracle期末考试试题3》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1.᝞
ஹ᝞
ᦪ᝞
ᡭᦪ஺StartupnomountalterdatabasemountalterdatabaseopenStartupmountStartup2.᝞
ἭᵨᡝḄἭ!"#ᦋSQLNET.orants3.᝞
%ᑴᵨᡝ'()*+▮஺Altersystemsetremote_login_passwordfile=exclusivenoneComment='2009-12-14'Scope=spfile;4.sys/sias@orclassysdba-orclḄ.᪆5.#ᦋ12ᘤ4orclḄ5647஺6.89:ṹᦻ=஺$orapwdfile='e:\orapwd.ora,Passwd=nzgentries=30;7.᪵?spfile@ᡂpfileCreatepfile=,d:\pfileorcl.ora,fromspfile;Createspfile=,e:\spfileorcl.ora,formpfile=,d:\pfileorcl.ora,;8.BCDᦪᦻ=EF%ᑴᦻ=ḄGH

1Showparametercontrol_file;selectnamefromv$controlfile;(ᙠ+KLᦪMount(᡻N)o(BC)PQR᝱ᦪTᐺVW-XYᨵᐵ%ᑴᦻ=Ḅ\]V$controlfilev$databaseV$controlfile_record_sectionSelectnamefromv$database;^_ᦪ`a஺9.b%ᑴᦻ=c@dRefᦻ=Alterdatabasebackupcontrolfiletotrace;gᦻ=ᙠD:\oracle\product\l0.2.0\admin\orcl\udump(BCshowparameteruser_dump_dest;EFh!஺10.89⊤jknew,lḄmnopDᦪbinitialb500k,nextb100k,minextentsb1,maxextentsb50,pctincreaseb0CreatetablespacenewDatafile'd:

2ew.dbfsize10mDefaultstorage(initial500kNext100kMinextents1Maxextents50Pctincrease0);

311.89⊤jknewtbs,qᦪḄl⊤jkᦋbnewtbsCreatetablespacenewtbsDatafile4d:

4ewths.dbfsize10m;Alterdatabasedefaulttablespacenewtbs;12.89rs⊤jktemptbs,qᦪḄlrs⊤jkᦋbtemptbsCreatetemporarytablespacetemptbstempfile6e:\temp.dbfsize10m;Alterdatabasedefaulttemporarytablespacetemptbs;13.89⊤jktuvwxᙠ89ὶz{#ᦋbῃzCreatetablespacettDatafile4d:\tt.dbfsize10monline;Altertablespacettoffline;14.89}ᙢᳮḄ⊤jkaa,9⊤testuq⊤ᐭ⊤jkaa-ᯠᑤ◀g⊤jk)mḄᦪᦻ=CreatetablespaceaaDatafile'd:\aa.dbfsize10m

5Extentmanagementlocalautoallocate;Createtabletest(aaint,bbint)tablespaceaa;Droptablespaceaaincludingcontents(cascadeconstraints)15.89⊤jkbb,mḄᦪᦻ=b2RCreatetablespacebbDatafile'd:\bbl.dbfsize10m,'e:\bb2.dbFsize5m;'16.89⊤jkcc,ᯠqᐸᦪᦻ=ᑮ᜜dRᦻ=ᜳ-1.CreatetablespaceccDatafile€d:\cc.dbfsize10m;2.altertablespaceccoffline;3.$copyd:\cc.dbfe:\cc.dbf4.altertablespaceccRenamedatafile'd:\cc.dbfto'e:\cc.dbf;5.altertablespacecconline;

6######ᦪᦻ=ῃz1.Selectlog_modeFromv$database;\\ᦪᦻ=ᙠnoarchivelogPῃz2.AlterdatabaseDatafile'e:\cc.dbf9offlinedrop;3.Recoverdatafile'e:\cc.dbf9;\\ឮὶz4.alterdatabasedatafile'e:\cc.dbFonline;17.89ᜧᦻ=⊤jkᜧb10mCreatebigfiletablespaceddDatafile'd:\dd.dbfsize10m;18.89}ᙢᳮḄ⊤jkee,dᑖᜧb10mCreatetablespaceeeDatafile4d:\ee.dbfsize100mExtentmanagementlocaluniformsize10m;ᨵஹ¡¢£¤¥19.89⊤jkhh,¦ᵨ§¨jkᳮCreatetablespacehh

7Datafile'd:\hh.dbfsize10mreuseSegmentspacemanagementauto;20.EF©ªᦋ«ᦪḄ¬᫏barchivelogSelectlog_modeFromv$database;shutdownimmediatestartupmountalterdatabasearchivelog;alterdatabaseopen;21.EF©ªᦻ=)©ªᦻ=®Ḅ\]Selectgroup#,memberFromv$logfile;EFᦻ=GHSelectgroup#,statusFromv$log;EF¯᝱22.°±©ªᦻ=®ᯠᑤ◀²°±Ḅ©ªᦻ=®

8AlterdatabaseAddlogfile('d:\log4.log')size10m;AlterdatabaseDroplogfile('d:\log4.log');######^´±©ª®ᡂᕒ¶AkerdatabaseAddlogfilemember'e:

9ewlog.log'togroup1;######ᑤ◀©ª®ᡂᕒAltersystemswitchlogfile;AlterdatabaseDroplogfilemember'e:

10ewlog.log';23.·¸ᑗᣚ©ªAltersystemswitchlogfile;24.»¼89dR⊤¦ᵨ½kᑖ½CreatetableRR(range_coldata,Aaint)

11Partitionbyrange(range_col)(partitionpartivalueslessthan(to_date(401/01/20099/dd/mm/yyyy?)),Partitionpart2valueslessthan(to_date('01/01/2009','dd/mm/yyyy')),Partitionpart3valueslessthan(maxvalue));25.»¼89dR⊤¦ᵨᦣᑡᑖ½CreatetablespaceplDatafile'd:\pl.dbFsize10mCreatetablespacep2Datafile4d:\p2.dbfsize10m;Createtablehash_t(hhint,Bbint)Partitionbyhash(hh)(Partitionpartitablespacep1,Partitionpart2tablespacep2);26.»¼89dR⊤¦ᵨᑡ⊤ᑖ½

12Createtablelist_t(list__colvarchar2(20),Bbint)Partitionbylist(list_col)(partitionpartivalues(two,,,de,,,en,),Partitionpart2values('zhao9,,gong,/zuo9),Partitionpart3valuesCyao,,,cheng,,,hao,));27.»¼89dR⊤¦ᵨ®ᔠᑖ½Createtablecom(rrint,hhint,ccint)Partitionbyrange(rr)Subpartitionbyhash(hh)subpartitions2(partitionpartivalueslessthan(10)(subpartitionpartl_subl-----tablespacepl,☢ÂRtablespace±'±¢()ḄSubpartitionpartl__sub2-----tablespacep2),Partitionpart2valueslessthan(20)(subpartitionpart2_sub1-----tablespacepl,Subpartitionpart2_sub2-----tablespacep2));

1328.89ᵨᡝul,l⊤jkbtest,ÃdÄ▮ÅÆ#ᦋ:ṹCreateuserulidentifiedbyuldefaulttablespacetestpasswordexpire;29.89ᵨᡝu2,qᐸ┝Èᯠ.┝Createuseru2identifiedbyu2;Alteruseru2accountlock;Alteruseru2accountunlock;30.89ᵨᡝu3,bxᑖᩗ▲ËKLᦪu89⊤Createuseru3identifiedbyu3Grantcreatesession,createtabletou3;31.89ᵨᡝu4,bxᑖKLᦪḄᩗ▲uᐕÍxqÎᩗ▲ᑖÏᐸxᵨᡝCreateuseru4identifiedbyu4Grantcreatesessiontou4

14Withadminoption;32.89ᵨᡝu5,bxᑖEÐѲ⊤testḄᩗ▲uᐕÍxqÎᩗ▲ᑖÏᐸxᵨᡝCreateuseru5identifiedbyu5;Grantselect,updateontesttou5Withgrantoption;33.Ò├ᵨᡝu5ḄEÐÔѲᩗ▲Revokeselect,updateontestfromu5;34.89ᵨᡝu6,EF#ᦋTEST-ḄaaÕhCreateuseru6identifiedbyu6;Grantselect,update(aa)ontesttou6;35.89ᵨᡝul,89Ö⁐rl,r2,ᐸ-r2:ṹbr2,qÖ⁐rlr2ᣴᩗÏulCreaterolerlnotidentified;Createroler2identifiedbyr2Grantrl,r2toul;

1536.Ïrlᣴᩗbcreatesession,)EÐsysÙᫀ-Ḅ⊤test,Ïr2ᣴᩗ#ᦋsysÙᫀ-Ḅtest⊤37.#ᦋᵨᡝid,lÖ⁐brlAlteruseru1defaultrolerl;38.ulÛ#ᦋsysÙᫀ-Ḅtest,ÜÝr2,#ᦋἭr2Ö⁐setroler2identifiedbyr2;updatesys.testsetaa=aa+100;setroleallexceptr2;39.89ᵨᡝu2,ᐕÍ:ṹ#ᦋ3Ä:ṹ┝È10ᜩCreateuseru2identifiedbyu2;CreateprofileplLimitFailed_Iogin_attempts3Password_lock_time10;Alteruseru2profilep1;

1640.89ᵨᡝu3,ᐕÍ:ṹwᵨ5ᜩ5ᜩÅÆ#ᦋ:ṹuv:ṹßà:ṹ'áCreateuseru3identifiedbyu3Createprofilep2LimitPassword_life_time5Password_grace_time5Password_reuse_maxunlimited;Alteruseru3profilep2;41.89ᵨᡝu4,7âwᵨ᪥äåᦪverify_function@d:\oracle\product\10.2.0\db_l\rdbms\admin\utlpwdmg.sql;Createprofilep4LimitPassword_verify_functionverify_function;Createuseru4identifiedbyuser123#;42.ÜÝæç▲ᑴ

17Altersystemsetresource_limit=true;43.ᵨprofileᳮèéæç▲ᑴèéᦪb3,uqg▲ᑴᑖÏu5CreateprofilesessionêimitLimitSessions_per_user3;Alteruseru5profilesessionjimit;44.newᵨᡝPᨵ⊤newtb,oᙠdRᵨᡝul,ᣴᩗul()EF#ᦋÔᑤ◀newᵨᡝPḄnewtb,ëìulmnewᵨᡝPḄnewtb⊤Ḅ#ᦋuEF஺Showparameteraudit_sys_operations;Altersystemsetaudit_sys_operations=truescope=spfile;Showparameteraudit_trail;Altersystemsetaudit_trail=truescope=spfile;íᦪ஺Createusernewidentifiedbynew123#DefaulttablespaceusersQuota2monusers;Grantcreatesession,createtabletonew;

18)newîïCreatetablenewtb(aaint,bbint);)sysîïGrantselect,update,deleteonnew.newtbtoul;(bðnewð'ᣴᩗ"""Conn