第三天上午oracle高級11g管理2課件

OracleTotalRecall以SYS用戶登錄SQL*Plus，然后執(zhí)行$HOME/labs flada_setup。該設(shè)置可創(chuàng)建另一個還原表空間、一個小型FLA_TBS1表空間，以及使用oracle_4U口令的ARCHIVE_ADMIN用戶。$sqlplus/asSQL>SQL>setserveroutputonSQL>--setverifyonSQL>settermonSQL>setlines200SQL>setpages44SQL>setpauseonSQL>/*==CreateatablespaceforyourflashbackdataSQL>DROPTABLESPACEfla_tbs1INCLUDING2DROPTABLESPACEfla_tbs1INCLUDING*ERRORatlineORA-00959:tablespace'FLA_TBS1'doesnotSQL>CREATESMALLFILETABLESPACEDATAFILESIZE10MREUSEAUTOEXTENDONNEXT640KMAXSIZENOLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO/TablespaceSQL>pausePress[Enter]toPress[Enter]toSQL>/*==Createasecondundotablespacefortesting==*/SQL>DROPTABLESPACEundotbs2INCLUDINGCONTENTS2DROPTABLESPACEundotbs2INCLUDING*ERRORatlineORA-00959:tablespace'UNDOTBS2'doesnot234

CREATESMALLFILEUNDOTABLESPACEDATAFILESIZE105MREUSEAUTOEXTENDONNEXT5120KMAXSIZE/TablespaceSQL>pausePress[Enter]toPress[Enter]toSQL>/*==CreateanARCHIVE_ADMINuserliketheHRuser==*/SQL>/*==withFLA_TBS1defaulttablespace==*/SQL>CREATEUSERARCHIVE_ADMINPROFILEDEFAULTIDENTIFIEDDEFAULTTABLESPACEFLA_TBS1TEMPORARYTABLESPACEACCOUNTUserSQL>pausePress[Enter]toPress[Enter]toSQL>GRANTALTERSESSIONTOGrantSQL>GRANTCREATEDATABASELINKTOGrantSQL>GRANTCREATESEQUENCETOGrantSQL>GRANTCREATESESSIONTOGrantSQL>GRANTCREATESYNONYMTOGrantSQL>GRANTCREATEVIEWTOGrantSQL>GRANTUNLIMITEDTABLESPACETOGrantSQL>GRANTEXECUTEONSYS.DBMS_STATSTOGrantSQL>GRANTCONNECT,RESOURCETOSQL>/*==SetupforFlashbackDataArchivecompleted==*/SQL>/*==TheARCHIVE_ADMINuserhasthepassword:oracle_4USQL>pausePress[Enter]toPress[Enter]to要授予ARCHIVE_ADMIN用戶管理權(quán)限以便其創(chuàng)建、和刪除閃回數(shù)據(jù)歸檔，請以SYS用戶執(zhí)行以下命令：GRANTFLASHBACKARCHIVEADMINISTERTO$$sqlplus/asSQL>GRANTFLASHBACKARCHIVEADMINISTERTOSQL>exit在SQL*Plus中，以ARCHIVE_ADMIN用戶使用ARCHIVE_ADMIN口令進(jìn)行<<<<<<not CREATEFLASHBACKARCHIVEQUOTA10MRETENTION1YEAR/SQL>SQL>CREATEFLASHBACKARCHIVEQUOTA10MRETENTION1YEAR/234Flashbackarchivecreated.GRANTFLASHBACKARCHIVEonFLA1toSQL>SQL>GRANTFLASHBACKARCHIVEonFLA1to現(xiàn)在，您將切換到閃回歸檔用戶角色。以HR用戶使用oracle_4U口令進(jìn)行連接。要為EMPLOYEES表啟用閃回數(shù)據(jù)歸檔，請執(zhí)行以下命令：ALTERTABLEhr.employeesFLASHBACKARCHIVESQL>SQL>connectEnterpassword:oracle_4U<<<not SQL>ALTERTABLEhr.employeesFLASHBACKARCHIVETablealtered.要查看Fox先生的薪金并為其加薪三次，每次增加1000，請以HR用戶執(zhí)SQL>SQL>REM"**********************************************"SQL>REM"FordemopurposesONLY:FlashbackDataArchive"SQL>setechoSQL>setserveroutputonSQL>--setverifyonSQL>settermonSQL>setlines200SQL>setpages44SQL>setpauseonpause"Press[Enter]tocontinue..."SQL>/*==QuerythecurrentsalaryforMr.Fox==*/SQL>SELECTemployee_id,last_name,FROMWHERElast_name=/Press[Enter]toEMPLOYEE_ID 170 rowSQL>pausePress[Enter]toPress[Enter]toSQL>/*==Increasethesalarythreetimesby1000==*/SQL>UPDATESETsalary=salary+WHERElast_name=/1rowSQL>2Commit234

UPDATESETsalary=salary+1000WHERElast_name='Fox'/1rowSQL>2Commit234

UPDATESETsalary=salary+1000WHERElast_name='Fox'/1rowSQL>2CommitSQL>pausePress[Enter]toPress[Enter]toSQL>/*==Querytheup-to-datevalueforMr.Fox==*/SQL>SELECTemployee_id,last_name,2FROM3WHERElast_name=4Press[Enter]toEMPLOYEE_ID 170 1rowSQL>pausePress[Enter]toPress[Enter]toSQL>setpauseoffSELECT*FROMSQL>SELECT*FROMUSER ARCHIVE 1row空間。以SYS用戶將UNDOTBS2指定為當(dāng)前還原表空間，方法是使用“EnterpriseManager>Server>AutomaticUndoManagement（EnterpriseManager>自動還原管理）”下的“ChangeTablespace（更改表空間connect/asSQL>connect/asSQL>connect/asSQL>ALTERSYSTEMSETSystemSQL>DROPTABLESPACETablespaceDROPORA-以HR用戶，選擇在創(chuàng)建閃回數(shù)據(jù)歸檔之后到執(zhí)行錯誤DML之前這段時間內(nèi)的一個時間。要查看Fox先生在該時間的雇員記錄，請執(zhí)行以下查詢（用所選歷史日期替換'15'MINUTE，示例格式如下：'50'SECOND,'10'DAY,'5'ORA-1466錯誤?？s短時間間隔，然后重試。如果仍然看到薪金為12600，請延長時間間隔。SELECTemployee_id,last_namesalaryFROMhr.employeesASOFTIMESTAMP(SYSTIMESTAMP-INTERVAL'15'WHERElast_name=SQL>connect<<<SQL>connect<<<not SQL>SELECTemployee_id,last_name,salaryFROMhr.employeesASOFTIMESTAMP(SYSTIMESTAMP-INTERVAL'15'MINUTE)WHERElast_name=EMPLOYEE_ID1701rowselected.（例如，十分鐘前），請執(zhí)行以下命令（'15'UPDATESETsalary=(SELECTsalaryFROMASOFTIMESTAMP(SYSTIMESTAMP-INTERVAL'15'WHERElast_name='Fox')WHERElast_name='Fox';鍵入，則也可執(zhí)行flada_upd.upd。SQL>SQL>UPDATE2SETsalary=(SELECTsalaryFROM3ASOFTIMESTAMP(SYSTIMESTAMP-INTERVAL'15'4WHERElast_name=5WHERElast_name=1row在SQL*Plus會話中，以SYS用戶連接到數(shù)據(jù)庫，并列出可用的數(shù)據(jù)字典視圖。執(zhí)行flada_list1.sql文件：SQL>connect/asSQL>SQL>REM"*******************************************"SQL>REM"FordemopurposesONLY:"SQL>connect/assysdbaSQL>setechoSQL>setserveroutputonSQL>--setverifyonSQL>settermonSQL>setlines200SQL>setpages44SQL>setpauseonpause"Press[Enter]tocontinue..."SQL>/*==Tolisttheavailabledatadictioaryviews==*/SQL>SELECT2FROM3WHEREtable_nameLIKE4Press[Enter]tocontinueSQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>colFLASHBACK_ARCHIVE_NAMEformatSQL>colARCHIVE_TABLE_NAMEformatA20SQL>colTABLE_NAMEformatA12SQL>colOWNER_NAMEformatA10SQL>DESCdba_flashback_archiveNull?NOTNULLVARCHAR2(255)NOTNULLNUMBERNOTNULLNUMBERSQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>/*==Toquerythetimewhentheflashbackdataarchive(s)havebeencreated==*/SQL>SELECTflashback_archive_name,create_time,2FROM3Press[Enter]tocontinue 05-JUN-09 SQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>DESCdba_flashback_archive_tsNull?-NOTNULLVARCHAR2(255)NOTNULLNUMBERNOTNULLVARCHAR2(30)SQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>/*==Tolistthetablespace(s),whichareusedforflashbackdataarchives==*/SQL>SELECT2FROM3Press[Enter]tocontinueFLASHBACK_ARCHIVE_NAMEFLASHBACK_ARCHIVE#TABLESPACE_NAME- 1SQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>DESCdba_flashback_archive_tablesNull?NOTNULLVARCHAR2(30)NOTNULLVARCHAR2(30)NOTNULLVARCHAR2(255)SQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>/*==Querythetablename(s),theownername(s),and==*/SQL>/*==theinternal"history"tablename(s)oftheflashbackdataarchive==*/SQL>SELECTFROMPress[Enter]tocontinueEMPLOYEES SQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>clearcolumns以HR用戶（使用oracle_4U口令）列出可以的flada_list2。SQL>SQL>connectEnterpassword:oracle_4U<<<not SQL>SQL>SELECTtable_nameFROMdictWHEREtable_nameLIKEPress[Enter]tocontinueSQL>pausePress[Enter]tocontinuePress[Enter]tocontinueSQL>用戶登錄SQL*Plus$$sqlplusEnterpassword:oracle_4U<<<not SQL>ALTERFLASHBACKARCHIVEfla1PURGEBEFORETIMESTAMP(SYSTIMESTAMPSQL>ALTERFLASHBACKARCHIVEfla1PURGEBEFORETIMESTAMP(SYSTIMESTAMP-INTERVAL'2'Flashbackarchive執(zhí)行flada_tbs2.sql以SYS用戶創(chuàng)建另一個大小為10MB的表SQL>SQL>SQL>setserveroutputonSQL>--setverifyonSQL>settermonSQL>setlines200SQL>setpages44SQL>setpauseonSQL>/*==Createanothertablespace==*/SQL>DROPTABLESPACEfla_tbs2INCLUDING2DROPTABLESPACEfla_tbs2INCLUDINGERRORatlineORA-00959tablespace'FLA_TBS2'doesnotSQL>CREATESMALLFILETABLESPACEDATAFILESIZE10MREUSEAUTOEXTENDONNEXT640KMAXSIZE32767MNOLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTTablespaceTablespaceSQL>pausePress[Enter]toPress[Enter]to以ARCHIVE_ADMIN用戶，將5MB的FLA_TBS2表空間添加到FLA1閃回SQL>SQL>connectEnterpassword:oracle_4U<<<not SQL>ALTERFLASHBACKARCHIVEfla1ADDTABLESPACEfla_tbs2QUOTA5M;Flashbackarchivealtered.FLA1閃回數(shù)據(jù)SQL>SQL>ALTERFLASHBACKARCHIVEfla1MODIFYRETENTION2Flashbackarchivealtered.SQL>SQL>DROPFLASHBACKARCHIVEFlashbackarchivedropped.稍等一兩分種并以SYS用戶連接；執(zhí)行flada_cleanup.sql清理REMExecutethisscriptscriptfortrainingpurposesonlyREMUndoCleanupforFlashbackDataArchiveREMExecutescriptasechoontermonlinesSQL>SQL>setpauseonSQL>connect/assysdbaSQL>setechoSQL>ALTERSYSTEMSET2SystemSQL>DROPTABLESPACEfla_tbs1INCLUDING2TablespaceSQL>DROPTABLESPACEfla_tbs2INCLUDING2SQL>DROPTABLESPACEundotbs2INCLUDING2TablespaceSQL>hostrm-f$HOME/BACKUP/fla_tbs01.dbfSQL>hostrm-f$HOME/BACKUP/fla_tbs02.dbfSQL>hostrm-f$HOME/BACKUP/undotbs02.dbfSQL>promptFlashbackDataArchivecleanupcomplete.FlashbackDataArchivecleanupcomplete.SQL>pausePress[Enter]toPress[Enter]toSQL>DROPORA-在本練習(xí)中，您將使用回收站還原已刪除對象。可以使用SQL命令或OracleEnterpriseManager14。對于后者，請執(zhí)行步驟1，然后執(zhí)行步驟5至11。 更改為$HOM