Common commands in Oracle

Chapter 4: 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 200 K next 200 K 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 200 K
SQL> next 200 K pctincrease 0 maxextents 50) tablespace indx;

5. Create bitmap Index
SQL> Create bitmap index xay_id on xay (a) pctfree 30 storage (initial 200 K next 200 K
SQL> pctincrease 0 maxextents 50) tablespace indx;

6. Change storage parameter of Index
SQL> alter index xay_id storage (next 400 k maxextents 100 );

7. Allocating index space
SQL> alter index xay_id allocate extent (size 200 K datafile 'C:/Oracle/index. dbf ');

8. Alter index xay_id deallocate unused;

Chapter 5: Constraints

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 100 K next 100 k) 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 in Table transport_set_violations
SQL> If dbms_tts.isselfcontained is true, it indicates self-contained

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 varchar2 (30), password in varchar2 (30 ),
Old_password in varchar2 (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 pai_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: management users

1. Create a user: Database Authentication
SQL> create user juncky identified by Oracle default tablespace users
SQL> temporary tablespace temp quota 10 m 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

Chapter 10: 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 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

4. Password File members: View: => V $ pwfile_users

5. o7_dictionary_accessibility = 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_privs

9. Revoke Object Privilege
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 2: 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_clerto 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 privileges t 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 distinct T 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 2: 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 1000 K;
> 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 ';