Common commands in Oracle

Oracle First chapter: 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:/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 ';