Chapter 1: Log Management
1.forcing log switches sql> ALTER SYSTEM SWITCH LOGFILE;
2.FORCING CHECKPOINTS SQL> ALTER SYSTEM CHECKPOINT;
3.Adding Online Redo Log Groups SQL> Alter Database Add logfile [Group 4] SQL> ('/disk3/log4a.rdo' ,'/disk4/log4b.rdo') size 1m;
4.Adding Online Redo Log Members SQL> ALTER DATABASE Add Logfile Member Sql> '/disk3/log1b.rdo' to Group 1, SQL> '/Disk4/log2b.rdo' to group 2;
5.changes the name of the online redo logfile sql> ALTER DATABASE RENAME FILE 'C: /Oracle/oradata/oradb/redo01.log' sql> to 'c: /oacle/oradata/redo01.log';
6.Drop Online Redo Log Groups SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
7.Drop Online Redo Log Members SQL> ALTER DATABASE DROP LOGFILE MEMBER 'C: / ORACLE/Ordata/redo01.log';
8. Clearing Online Redo Log Files SQL> ALTER DATABASE CLOAR [Unarchived] logfile 'c: /oacle/log2a.rdo'
9.using logminer analyzing redo logfiles
a. in the init.ora specify utl_file_dir = '' b. SQL> EXECUTE DBMS_LOGMNR_D.BUILD ('ORADB.ORA', 'C: / Oracle / ORADB / LOG'); c. SQL> EXECUTE DBMS_LOGMNR_ADD_LOGFILE ('C: / Oracle / ORADATA / ORADB / Redo01.Log ', SQL> DBMS_Logmnr.new); d. sql> execute dbms_logmnr.add_logfile (' c: /oracle/oradata/oradb/redo02.log ', sql> dbms_logmnr.addfile; e . sql> execute dbms_logmnr.start_logmnr. (dictfilename => 'c: /oracle/oradb/log/oradb.ora'); f sql> select * from v $ logmnr_contents (v $ logmnr_dictionary, v $ logmnr_parameters sql> v $ logmnr_logs ); g. sql> execute dbms_logmnr.end_logmnr;
Chapter II: Table Space Management 1.create TableSpaces SQL> CREATE TABLESPACE TABLESPACE_NAME DATAFILE 'C: /Oracle/oradata/file1.dbf' size 100m, SQL> 'C: /oracle/roadata/file2.dbf' size 100m minimum extent 550k [logging / nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online / offline] [permanent / temporary] [extent_management_clause] 2.locally managed tablespace sql> create tablespace user_data datafile 'c: / Oracle / ORADATA / User_Data01.dbf 'SQL> Size 500M Extent Management Local Uniform Size 10M;
3.Temporary TableSpace SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'C: /Oracle/oradata/temp01.dbf' SQL> Size 500M Extent Management Local Uniform Size 10M;
4.Change the Storage Setting SQL> ALTER TABLESPACE APP_DATA Minimum Extent 2m; SQL> ALTER TABLESPACE APP_DATA Default Storage (Initial 2M Next 2M MaxExtents 999);
5.Taking TableSpace Offline or Online SQL> ALTER TABLESPACE APP_DATA OFFLINE; SQL> ALTER TABLESPACE APP_DATA Online;
6.Read_only tablespace SQL> ALTER TABLESPACE APP_DATA READ Ion | Write
7.Droping TableSpace SQL> DROP TABLESPACE APP_DATA INCLUDING CONTENTS;
8.enableing Automatic Extension of Data Files SQL> ALTER TABLESPACE APP_DATA Add DataFile 'C: /oracle/oradata/app_data01.dbf' size 200m SQL> AutoExtend On Next 10m MaxSize 500M;
9.change the size fo data files manually sql> ALTER DATABASE DATAFILE 'C: / ORACLE/Ordata/app_data.dbf' Resize 200M;
10.Moving Data Files: ALTER TABLESPACE SQL> ALTER TABLESPACE APP_DATA RENAME DATAFILE 'C: /Oracle/oradata/app_data.dbf' SQL> To 'C: /Oracle/App_data.dbf';
11.Moving Data Files: ALTER DATABASE SQL> ALTER DATABASE RENAME FILE 'C: /Oracle/oradata/app_data.dbf' SQL> To 'C: /oracle/App_data.dbf'; Chapter 3: Table
1.create a table sql> create table table_name (column datatype, column datatype] ....) sql> tablespace tablespace_name [pctfree integer] [pctused integer] sql> [initrans integer] [maxtrans integer] sql> storage (initial 200k Next 200k Pctincrease 0 MaxExtents 50) SQL> [Logging | NOLGGING] [Cache | Nocache]
2.Copy An EXISTING TABLE SQL> CREATE TABLE TABLE_NAME [Logging | NOLOGGING] AS SUBQUERY
3.Create Temporary Table SQL> Create Global Temporary Table Xay_Temp as Select * from xay; on commit preserve rows / on commit delete rows
4.pctfree = (Average Row Size - Initial Row Size * 100 / Average Row Size Pctused = 100-PctFree- (Average Row Size * 100 / Available Data Space)
5.Change Storage and Block Utilization Parameter SQL> ALTER TABLE TABLE_NAME PCTFREE = 30 Pctused = 50 Storage (Next 500K SQL> Mineltents 2 MaxExtents 100);
6.Manually Allocating Extents SQL> ALTER TABLE TABLE_NAME Allocate Extent (Size 500k DataFile 'C: /Oracle/data.dbf');
7.Move Tablespace SQL> ALTER TABLE Employee Move TableSpace User;
8.deallocate of unused SPACE SQL> ALTER TABLE TABLE_NAME DEAALLOCATE UNUSED [Keep Integer]
9.Truncate a Table SQL> Truncate Table Table_name;
10.Drop a Table SQL> Drop Table Table_name [Cascade Constraints];
11.Drop A Column SQL> ALTER TABLE TABLE_NAME DROP Column Comments Cascade Constraints Checkpoint 1000; Alter Table Table_Name Drop Column Continue
12.mark a column as unused sql> alter table table_name set unused column comments cascade constraints; alter table table_name drop unused columns checkpoint 1000; alter table orders drop columns continue checkpoint 1000 data_dictionary: dba_unused_col_tabs [This paste has been 05 months of 2004 At 10:14, 24, 17 seconds edited] [/ align]
[Align = Right] [This post has been edited by the author on May 24, 2004, 53 seconds] ■■■■■■■■■■■■■ From ■■■■■■■■■■■■■■■
Email: binss@163.com 2004-05-24 10:11 [Recommended Training] Oracle9i DBA Training Package (Classic Training) Binss Level: One Star Member Post: 341 Points: 795 Euro: 0 Registration: 2004-04-17 Chapter 4 of the 2nd floor: Index
1.creating function-based indexes SQL> CREATE INDEX SUMMIT.ITEM_QUANTITY ON SUMMIT.ITEM (Quantity-Quantity_Shipped);
2.create a B-tree index sql> create [unique] index index_name on table_name (column, .. asc / desc) tablespace sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] sql> [logging | nologging] [Nosort] Storage (Initial 200k Next 200k Pctincrease 0 SQL> MAXEXTENTS 50);
3. PctFree (INDEX) = (Maximum Number of Rows-Initial Number of Rows) * 100 / Maximum Number Of Rows
4.CREATING REVERSE Key Indexes SQL> Create Unique Index XAY_ID ON XAY (a) Reverse Pctfree 30 Storage (Initial 200k SQL> Next 200k Pctincrease 0 MaxExtents 50) TABLESPACE INDX;
5.create Bitmap Index SQL> CREATE BITMAP INDEX XAY_ID ON XAY (A) PCTFree 30 Storage (Initial 200k Next 200k SQL> Pctincrease 0 MaxExtents 50) TABLESPACE INDX;
6.Change Storage Parameter Of Index SQL> ALTER INDEX XAY_ID Storage (Next 400K MaxExtents 100);
7.Allocating index space SQL> ALTER INDEX XAY_ID Allocate Extent (Size 200k DataFile 'C: / Oracle/index.dbf');
8.alter index xay_id deallocate unused; Chapter 5: Constraint
1.define constraints as immediate or defred sql> ALTER session set constraint [s] = immediate / deferred / default; set constraint [S] constraint_name / all immediate / deferred;
2. SQL> Drop Table Table_name Cascade Constraints SQL> Drop TableSpace TableSpace_name Including Contents Cascade Constraints
3. define constraints while create a table sql> create table xay (id number (7) constraint xay_id primary key deferrable sql> using index storage (initial 100k next 100k) tablespace indx); primary key / unique / references table (column) / CHECK
4.enable constraints SQL> ALTER TABLE XAY Enable NoValidate Constraint Xay_ID;
5.enable constraints SQL> ALTER TABLE XAY ENABLE VALIDATE CONSTRAINT XAY_ID;
Chapter 6: Load Data
1.Loading Data Using Direct_load Insert SQL> INSERT / * APPEND * / INTO EMP NOLOGGING SQL> SELECT * from EMP_OLD;
2.Parallel Direct-Load Insert SQL> ALTER Session Enable Parallel DML; SQL> INSERT / * Parallel (EMP, 2) * / INTO EMP NOLOGGING SQL> SELECT * FROM EMP_OLD;
3.USING SQL * Loader SQL> SQLLDR Scott / Tiger / SQL> Control = ULCase6.ctl / SQL> Log = ULCase6.log Direct = TRUE
Chapter 7: REORGANIZING DATA
1.USING Expoty $ EXP Scott / Tiger Tables (DEPT, EMP) File = C: /emp.dmp log = exp.log compress = n direct = y
2.USING IMPORT $ IMP Scott / Tiger Tables (DEPT, EMP) File = Emp.dmp Log = Imp.log ignore = Y
3.transporting a tablespace sql> alter tablespace sales_ts read only; $ exp sys / .. file = xay.dmp transport_tablespace = y tablespace = sales_ts triggers = n constraints = n $ copy datafile $ imp sys / .. file = xay.dmp transport_tablespace = y datafiles = (/ disk1 / sles01.dbf, / disk2 /sles02.dbf) sql> alter tablespace sales_ts read write; 4.checking transport set sql> DBMS_tts.transport_set_check (ts_list => 'sales_ts' .., incl_constraints = > TRUE); View SQL> dbms_tts.isselfcontained is true in Table Transport_Set_violations, indicating that it is included
Chapter 8: Managing Password Security and Resources
1.Controlling Account Lock and Password SQL> ALTER User Juncky Identified by Oracle Account UNLOCK
2.User_Provided Password Function SQL> Function_Name (Userid in varcha2 (30), old_password in varcha2 (30)) Return Boolean
3.create a profile: password setting sql> create profile grace_5 limit failed_login_attempts 3 sql> password_lock_time unlimited password_life_time 30 sql> password_reuse_time 30 password_verify_function verify_function sql> password_grace_time 5;
4.ALTERING a profile sql> ALTER profile default failed_login_attempts 3 SQL> Password_life_time 60 password_grace_time 10;
5.Drop a profile sql> Drop Profile grace_5 [cascade];
6.create a profile: Resource Limit SQL> CREATE PROFILE DEVEL_PROF LIMIT sessions_per_user 2 SQL> CPU_PER_SESSION 10000 IDLE_TIME 60 Connect_Time 480;
7. View => resource_cost: Alter Resource Cost DBA_USERS, DBA_PROFILES
8. Enable Resource Limits SQL> ALTER SYSTEM SET Resource_Limit = true;
Chapter 9: Managing Uses
1.create a user: database authentication sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock | unlock] [profile profilename | default]; 2.change user quota On Tablespace SQL> ALTER USER JUNCKY Quota 0 on Use;
3.Drop a User SQL> Drop User Juncky [Cascade];
4. Monitor User View: DBA_USERS, DBA_TS_QUROS ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Email: binss@163.com 2004-05-24 10:12 [Recommended Training] Oracle9i DBA Training Package (Classic Training) Binss Level: One Star Member Post: 341 Points: 795 Euro: 0 Registration: 2004-04-17 Chapter 10, Chapter 3: Managing Privileges
1.system privileges: view => system_privilege_map, dba_sys_privs, session_privs
2.Grant System Privilege, Create Table To Managers; SQL> Grant Create Session To Scott with Admin Option; With Admin Option CAN Grant Or Revoke Privilege from any user or role
3.sysdba and sysoper privileges: sysoper: startup, shutdown, alter database open | mount, alter database backup controlfile, alter tablespace begin / end backup, recover database alter database archivelog, restricted session sysdba: sysoper privileges with admin option, create database, Recover Database Until
4.Password file members: view: => v $ pwfile_users
5.O7_DICTIONARY_ACCESSIBILITILIBILITY = True Restriction Access to View or Tables in Other Schema
6.Revoke System Privilege SQL> Revoke Create Table from Karen; SQL> Revoke Create Session from Scott;
7.Grant Object Privilege SQL> Grant Execute On DBMS_PIPE TO PUBLIC; SQL> Grant Update (First_Name, Salary) ON Employee To Karen with grant Option;
8.Display Object Privilege: View => DBA_TAB_PRIVS, DBA_COL_PRIVS9.REVOKE OBA_COLEGE SQL> Revoke Execute on dbms_pipe from scott [cascade constraints];
10.Audit Record View: => sys.aud $
11. Protecting The Audit Trail Sql> Audit Delete on Sys.AUD $ by Access;
12.Statement Auditing SQL> Audit User;
13.Privilege Auditing SQL> Audit Select Any Table by Summit By Access;
14. Schema Object Auditing SQL> Audit Lock on Summit.employee by Access WHENEVER SUCCESSFUL;
15.View audit option: view => all_def_audit_opts, dba_stmt_audit_opts, dba_priv_audit_opts, dba_obj_audit_opts
16.View audit result: view => dba_audit_trail, dba_audit_exists, dba_audit_ object, dba_audit_session, dba_audit_statement
Chapter 11: Manager Role
1.Create Roles SQL> CREATE ROLE SALES_CLERK; SQL> Create Role HR_Clerk Identified by Bonus; SQL> CREATE ROLE HR_MANAGER Identified EXTERNALLY
2.Modify Role SQL> ALTER ROLE SALES_CLERK Identified by Commission; SQL> ALTER ROLE HR_CLERK Identified Externalness; SQL> ALTER ROLE HR_MANAGER NOT IDENTIFIED;
3.Assigning Roles SQL> Grant Sales_Clerk to Scott; SQL> Grant HR_Clerk to HR_Manager; SQL> Grant HR_Manager To Scott with Admin Option;
4.establish default role sql> alter user scott default role hr_clerk, sales_clerk; sql> alter user scott default role all; sql> alter user scott default role all except hr_clerk; sql> alter user scott default role none;
5.enable and disable rolls SQL> SET ROLE HR_Clerk; SQL> SET ROLE SALES_CLERK Identified by Commission; SQL> Set Role All Except sales_clerk; sql> set role none;
6.Remove Role from User SQL> Revoke Sales_Clerk from Scott; SQL> Revoke HR_Manager from Public;
7.Remove Role SQL> Drop Role HR_Manager; 8.display Role Information View: => DBA_ROLES, DBA_ROLE_PRIVS, ROLE_ROLE_PRIVS, DBA_SYS_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, SESSION_ROLES
Chapter 12: Backup and Recovery
1. V $ SGA, V $ INSTANCE, V $ PROCESS, V $ BGPROCESS, V $ DATABASE, V $ DataFile, V $ SGASTAT
2. RMAN NEED SET DBWR_IO_SLAVES or Backup_tape_io_slaves and large_pool_size
3. Monitoring Parallel Rollback> V $ FAST_START_SERVERS, V $ FAST_START_TRANSACTIONS
4.Perform A Closed Database Backup> Shutdown Immediate> CP Files / Backup /> Startup
5.RESTORE TO A DIFFERENT LOCATION> Connect System / Manager As Sysdba> Startup Mount> ALTER DATABASE RENAME FILE '/DISK1/./user.dbf' to '/disk2/../user.dbf';> Alter Database Open ;
6.recover syntax --recover a mounted database> recover database;> recover datafile '/disk1/data/df2.dbf';> alter database recover database; --recover an opened database> recover tablespace user_data;> recover datafile 2; > ALTER DATABASE Recover DataFile 2;
7.how to Apply Redo Log Files Automatically> Set Autorecovery On> Recover Automatic DataFile 4;
8.Complete Recovery: --Method 1> Copy C: /backup/User.dbf c: /oradata/user.dbf> Startup Mount> Recover DataFile 'C: /oraData/User.dbf;> Alter Database Open; --Method 2 (Opened Database, Initially Opened, NOT System or Rollback DataFile> Copy C: /backup/User.dbf C: / OraData/User.dbf (ALTER TABLESPACE OFFLINE)> Recover DataFile 'C: / ORADATA /user.dbf 'or> recover tablespace user_data;> alter database datafile' c: /oradata/user.dbf 'online or> alter tablespace user_data online; --method 3 (opened database, initially closed not system or rollback datafile)> Startup Mount> ALTER DATABASE DATAFILE 'C: /ORADATA/USER.DBF' OFFLINE;> ALTER DATABASE OPEN> COPY C: /BACKUP/USER.DBF D: / ORADATA/USER.DBF> ALTER DATABASE RENAME FILE 'C: / ORADATA /user.dbf 'to' D: /oradata/User.dbf '> Recover DataFile' E: / ORADATA/User.dbf 'or Recover TableSpace User_Data;> ALTER TABLESPACE USER_DATA online; --Method 4 (Loss of Data File with NO Backup and have all archive logs> ALTER TABLESPACE USER_DATA OFFLINE IMMEDIATE ;> Alter database create datafile 'd: /oradata/user.dbf' as' c: /oradata/user.dbf ''> recover tablespace user_data;> alter tablespace user_data online 5.perform an open database backup> alter tablespace user_data begin Backup;> Copy Files / Backup /> ALTER DATABASE DATAFILE '/ C:> ALTER System Switch logfile; 6.backup a control file> ALTER DATABASE BACKUP ControlFile To' Control1.BKP ' ;> ALTER DATABASE BACKUP ControlFile to TRACE; 7.Recovery (NoArchiveLog Mode)> Shutdown Abort> CP Files> Startup 8.Recovery of File In Backup Mode>
ALTER DATABASE DATAFILE 2 End Backup; 9.Clearing Redo Log File> ALTER DATABASE CLEAR Unrchived logfile group 1;> Alter Database Clear Unrchived logfile group 1 unrecoverable datafile;
10.Redo Log Recovery> ALTER DATABASE ADD Logfile Group 3 'c: /oradata/redo03.log' size 1000k;> Alter Database Drop logfile group 1;> Alter Database Open; Or> CP: /oradata/redo02.log ' C: /oradata/redo01.log> ALTER DATABASE CLOGFILE 'C: /ORADATA/LOG01.LOG'
