A complete set of common Oracle commands
A complete set of common Oracle commands
The ORACLE tutorial is a complete set of common Oracle commands.
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 1 m;
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 2: Table space management
1. create tablespaces
SQL> create tablespace tablespace_name datafile 'C: \ oracle \ oradata \ file1.dbf 'size 100 m,
SQL> 'C: \ oracle \ oradata \ file2.dbf' size 100 m minimum extent 550 k [logging/nologging]
SQL> default storage (initial 500 k next 500 k 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 500 m extent management local uniform size 10 m;
3. temporary tablespace
SQL> create temporary tablespace temp tempfile 'C: \ oracle \ oradata \ temp01.dbf'
SQL> size 500 m extent management local uniform size 10 m;
4. change the storage setting
SQL> alter tablespace app_data minimum extent 2 m;
SQL> alter tablespace app_data default storage (initial 2 m next 2 m maxextents 999 );
5. taking tablespace offline or online
SQL> alter tablespace app_data offline;
SQL> alter tablespace app_data online;
& Nbs
[1]
The ORACLE tutorial is a complete set of common Oracle commands. P; 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 200 m
SQL> autoextend on next 10 m maxsize 500 m;
9. change the size fo data files manually
SQL> alter database datafile 'C: \ oracle \ oradata \ app_data.dbf' resize 200 m;
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 ';
[NextPage]
Chapter 3: tables
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 200 k next 200 k 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 500 k
SQL> minextents 2 maxextents 100 );
6. manually allocating extents
SQL> alter table table_name allocate extent (size 500 k 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 checkpoints 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 checkpoints 1000;
Alter table orders drop columns continue checkpoint 1000
Data_dictionary: dba_unused_col_tabs
Chapter 4: Index
[2]
The ORACLE tutorial is a complete set of common Oracle commands.
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]
The ORACLE tutorial is a complete set of common Oracle commands.
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
[NextPage]
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. s
[4]
The ORACLE tutorial is a complete set of common Oracle commands. Ysdba 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;
[5]
The ORACLE tutorial is a complete set of common Oracle commands.> 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
[6]
The ORACLE tutorial is a complete set of common Oracle commands. 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
Previous Page
[7]