Chapter One: journal 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:/oracle/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/oradata/redo01.log ';
8.clearing Online redo log files
Sql> ALTER DATABASE clear [unarchived] logfile ' C:/oracle/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/oradata/file2.dbf ' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 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 only|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/ORADATA/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 III: 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|nologging] [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> minextents 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 users;
8.deallocate of unused space
Sql> ALTER TABLE table_name deallocate 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 columns 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
Fourth Chapter: 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 storage (initial 200k
Sql> next 200k pctincrease 0 maxextents tablespace indx;
5.create Bitmap Index
Sql> Create bitmap index xay_id on Xay (a) pctfree storage (initial 200k next 200k
sql> pctincrease 0 maxextents) 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;
Fifth chapter: Restraint
1.define constraints as immediate or deferred
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;
Sixth chapter: 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
Seventh chapter: 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 in Table Transport_set_violations
Sql> dbms_tts.isselfcontained is true to represent the self contained
Eighth chapter: 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 Varchar2 (a), password in VARCHAR2 (30),
Old_password in VARCHAR2 (m) 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 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 Password_grace_time 10;
5.drop a profile
sql> Drop Profile grace_5 [cascade];
6.create a profile:resource limit
Sql> Create profile Developer_prof limit Sessions_per_user 2
Sql> cpu_per_session 10000 idle_time 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;
Nineth Chapter: Managing Users
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 users;
3.drop a user
sql> drop User Juncky [cascade];
4. Monitor User
View:dba_users, Dba_ts_quotas
Tenth chapter: Managing Privileges
1.system Privileges:view => System_privilege_map, Dba_sys_privs,session_privs
2.grant system Privilege
Sql> Grant create session,create table to managers;
Sql> Grant create session to Scott with admin option;
With admin option can grant or revoke privilege the 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_accessibility =true restriction access to view or tables in the 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_privs
9.revoke Object Privilege
Sql> revoke execute on dbms_pipe from Scott [Cascade constraints];
10.audit record View:=> sys.aud$
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 from 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
11th Chapter: 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 externally;
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 roles
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
12th chapter: 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 (noarchivelog)
> 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 (Mounted databae)
>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 log)
>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:/. /DATA.DBF ' End backup;
> 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 unarchived logfile Group 1;
>alter Database Clear unarchived 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 C:/oradata/redo02.log ' C:/oradata/redo01.log
>alter database Clear logfile ' c:/oradata/log01.log ';