Common commands in Oracle

Source: Internet
Author: User

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
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 ';

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.