【正文】 cesscount)from tbl_MiddleMt0411 where ServiceType2=111 select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype原文地址：alter database backup controlfile to trace。//增加一個新的日志文件組的語句 connect internal as sysdba alter database add logfile group 4(’/db01/oracle/CC1/’, ’/db02/oracle/CC1/’)size 5M。alter database add logfile member ’/db03/oracle/CC1/’ to group 4。//在Server Manager上MOUNT并打開一個數(shù)據(jù)庫: connect internal as sysdba startup mount ORA1 exclusive。alter database open。//生成數(shù)據(jù)字典 @catalog @catproc // 中備份數(shù)據(jù)庫的位置log_archive_dest_1 = ’/db00/arch’ log_archive_dest_state_1 = enable log_archive_dest_2 = “service= mandatory reopen=60” log_archive_dest_state_2 = enable //對用戶的表空間的指定和管理相關(guān)的語句create user USERNAME identified by PASSWORD default tablespace TABLESPACE_NAME。alter user USERNAME default tablespace TABLESPACE_NAME。alter user SYSTEM quota 0 on SYSTEM。alter user SYSTEM quota 50M on TOOLS。create user USERNAME identified by PASSWORD default tablespace DATA temporary tablespace TEMP。alter user USERNAME temporary tablespace TEMP。//重新指定一個數(shù)據(jù)文件的大小 : alter database datafile ’/db05/oracle/CC1/’ resize 200M。//創(chuàng)建一個自動擴展的數(shù)據(jù)文件: create tablespace DATA datafile ’/db05/oracle/CC1/’ size 200M autoextend ON next 10M maxsize 250M。//在表空間上增加一個自動擴展的數(shù)據(jù)文件: alter tablespace DATA add datafile ’/db05/oracle/CC1/’ size 50M autoextend ON maxsize 300M。//修改參數(shù): alter database datafile ’/db05/oracle/CC1/’ autoextend ON maxsize 300M。//在數(shù)據(jù)文件移動期間重新命名: alter database rename file ’/db01/oracle/CC1/’ to ’/db02/oracle/CC1/’。alter tablespace DATA rename datafile ’/db01/oracle/CC1/’ to ’/db02/oracle/CC1/’。alter database rename file ’/db05/oracle/CC1/’ to ’/db02/oracle/CC1/’。alter database datafile ’/db05/oracle/CC1/’ resize 80M。//創(chuàng)建和使用角色: create role APPLICATION_USER。grant CREATE SESSION to APPLICATION_USER。grant APPLICATION_USER to username。//回滾段的管理create rollback segment SEGMENT_NAME tablespace RBS。alter rollback segment SEGMENT_NAME offline。drop rollback segment SEGMENT_NAME。alter rollback segment SEGMENT_NAME online。//回滾段上指定事務(wù) mit。set transaction use rollback segment ROLL_BATCH。insert into TABLE_NAME select * from DATA_LOAD_TABLE。mit。//查詢回滾段的 大小和優(yōu)化參數(shù) select * from DBA_SEGMENTS where Segment_Type = ’ROLLBACK’。select , /* rollback segment name */ /* rollback segment OPTIMAL size */ from V$ROLLNAME N, V$ROLLSTAT S where =。//回收回滾段alter rollback segment R1 shrink to 15M。alter rollback segment R1 shrink。//例子set transaction use rollback segment SEGMENT_NAME alter tablespace RBS default storage(initial 125K next 125K minextents 18 maxextents 249)create rollback segment R4 tablespace RBS storage(optimal 2250K)。alter rollback segment R4 online。select Sessions_Highwater from V$LICENSE。grant select on EMPLOYEE to PUBLIC。//用戶和角色create role ACCOUNT_CREATOR。grant CREATE SESSION, CREATE USER, ALTER USER to ACCOUNT_CREATOR。alter user THUMPER default role NONE。alter user THUMPER default role CONNECT。alter user THUMPER default role all except ACCOUNT_CREATOR。alter profile DEFAULT limit idle_time 60。create profile LIMITED_PROFILE limit FAILED_LOGIN_ATTEMPTS 5。create user JANE identified by EYRE profile LIMITED_PROFILE。grant CREATE SESSION to JANE。alter user JANE account unlock。alter user JANE account lock。alter profile LIMITED_PROFILE limit PASSWORD_LIFE_TIME 30。alter user jane password expire。//創(chuàng)建操作系統(tǒng)用戶REM Creating OPS$ accounts create user OPS$FARMER identified by SOME_PASSWORD default tablespace USERS temporary tablespace TEMP。REM Using identified externally create user OPS$FARMER identified externally default tablespace USERS temporary tablespace TEMP。//執(zhí)行ORAPWD ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users create role APPLICATION_USER。grant CREATE SESSION to APPLICATION_USER。create role DATA_ENTRY_CLERK。grant select, insert on to DATA_ENTRY_CLERK。grant select, insert on to DATA_ENTRY_CLERK。grant select, insert on to DATA_ENTRY_CLERK。grant APPLICATION_USER to DATA_ENTRY_CLERK。grant DATA_ENTRY_CLERK to MCGREGOR。grant DATA_ENTRY_CLERK to BPOTTER with admin option。//設(shè)置角色set role DATA_ENTRY_CLERK。set role NONE。//回收權(quán)利: revoke delete on EMPLOYEE from PETER。revoke all on EMPLOYEE from MCGREGOR。//回收角色: revoke ACCOUNT_CREATOR from HELPDESK。drop user USERNAME cascade。grant SELECT on EMPLOYEE to MCGREGOR with grant option。grant SELECT on to BPOTTER with grant option。revoke SELECT on EMPLOYEE from MCGREGOR。create user MCGREGOR identified by VALUES ’1A2DD3CCEE354DFA’。alter user OPS$FARMER identified by VALUES ’no way’。//備份與恢復(fù)使用 export 程序exp system/manager file= press=Y owner=(HR,THUMPER)exp system/manager file= owner=HR indexes=Y press=Y imp system/manager file= full=Y buffer=64000 mit=YMartriWang@ 14/05/20071 將數(shù)據(jù)庫TEST完全導(dǎo)出,用戶名system 密碼manager 導(dǎo)出到D:exp system/manager@TEST file=d: full=y 2 將數(shù)據(jù)庫中system用戶與sys用戶的表導(dǎo)出exp system/manager@TEST file=d: owner=(system,sys)3 將數(shù)據(jù)庫中的表tabletable2導(dǎo)出exp system/manager@TEST file=d: tables=(table1,table2)4 將數(shù)據(jù)庫中的表table1中的字段filed1以“00”打頭的數(shù)據(jù)導(dǎo)出exp system/manager@TEST file=d: tables=(table1)query=“ where filed1 like 39。00%39?！盡artriWang@ 14/05/20071 將數(shù)據(jù)庫TEST完全導(dǎo)出,用戶名system 密碼manager 導(dǎo)出到D:exp system/manager@TEST file=d: full=y 2 將數(shù)據(jù)庫中system用戶與sys用戶的表導(dǎo)出exp system/manager@TEST file=d: owner=(system,sys)3 將數(shù)據(jù)庫中的表tabletable2導(dǎo)出exp system/manager@TEST file=d: tables=(table1,table2)4 將數(shù)據(jù)庫中的表table1中的字段filed1以“00”打頭的數(shù)據(jù)導(dǎo)出exp system/manager@TEST file=d: tables=(table1)query=“ where filed1 like 39。00%39?！?/備份表exp