SQL_PL_SQL

Nth max salary query:

Select salary
From Employee E1
Where (N-1) = (
                                 Select Count(Distinct(E2.Salary))
                                 From Employee E2
                                 Where E2.Salary > E1.Salary

                              )

Partitioned tables:

Oracle allow to divide the rows of a table into multiple parts. Dividing a tables data in this manner is called partitioned table.

Creating table: To create a partitioned table, you have to specify how to set up the partitions of the tables data as part of the create command.

Typically tables are partitioned by range of values. To partition the tables records use the "partition by range" clause. The range will determine the values stored in each of the table partition.

Syntax:

create table table_name (column1 data_type(size), column2 data_type(size),.............)
partition by range (column1)
(partition partition_name1 values < some_value,
partition partition_name2 values < some_value,
partition partition_name2 values < some_value,
-----------------------------------------------
-----------------------------------------------);
You do not need to specify a maximum value for the last partition.

Syntax to select data from partition:

select * from table_name partition(partition_name);

Adding Partition:

Adding new partition to table.

Syntax: alert table table_name add partition partition_name values < value;


Dropping  partition:

Removing partition.

Syntax: alert table table_name drop partition partition_name

Splitting partition:

Splitting one partition into two partitions.

Syntax: alter table table_name split partition partition_name at (value) into (partition partition_name1,partition partition_name2);


Moving partitions:

The move clause of the alter table statement is used to move a partition from a most active table space to different table space in order to balance I/O operations.

Syntax: alter table table_name move partition partition_name tablespace table_space_name;


Work load capturing  

sys> begin
         dbms_wokload_capture.start_capture(name=>'Test_cature_1', dir=>'db_reply_1',     
         duration=>null)
         end;
         /
sys> create user test_1 identified by test_1;

sys> grant connect resource1.create table to test_1;

sys> conn test_1/test_1

test_1> create table db_replay(id number, description varchar2(50), constraint db_replay primary 
              key (id));

test_1> begin
              for i in 1..500000 loop
              insert into db_replay(id, description) values (i,'description_test');
              end loop;
              commit;
              end;
              /
Finishing capture process

conn as sysdba

sys> begin
         dbms_workload_capture.finish_capture;
         end;
         /

Selecting work load capture 

sys> dbms_workload_capture.get_capture_info('DB_REPLAY') from dual;

sys> select id,name from dba_workload_captures;


SQL * Loader
===================================

SQL Loader loads data from external files into tables in oracle data base.

It requires the following files.
1. Data file
2. Control file


Data File: It contains the information to be loaded
Control File: It tells oracle how to read and load the data. It contains the information on the format of the data, the records and fields within the file, The order in which they are to be loaded. It is created at the OS level.

SQL Loader creates the following files.
1. Log file
2. Bad file

Log File: It records the status of the load, such as the number of rows processed and the number of rows committed.

Bad File: It contains rows that are rejected during the load due to data errors.

Syntax:

LOAD DATA
INFILE 'file_name.extension'
BADFILE 'PATH/bad_file_name.bad'
[TRUNCATE | APPEND| INSERT | REPLACE]
INTO TABLE table_name  fields terminated by "," // instead of "," you can specify your delimiter
( column1, column2, column3,.................)

Here column1, column2, column3...................... are names of table column name.
These must be equal to table field names.

TRUNCATE: This clause truncates the table before starting.
APPEND: This clause adds the rows to the table.
INSERT:  This clause adds the rows to an empty table. The load will abort with an error if the table is not empty.
REPLACE: This clause empties the table and then adds the new rows. The user must have DELETE privilege on the table.


Checkpoint Process

CKPT updates the headers of all data files to record the details of the check point.

System Monitor Process  

It performs recovery and also responsible for clean up temporary segments that are no longer in use and for unite contiguous free extents within dictionary managed table spaces. It checks regularly to see whether recovery is needed.

BULK COLLECT AND FORALL

BULK COLLECT: It retrieve multiple rows at a time in single fetch

FORALL: it performes multiple INSERTs | UPDATEs | DELETEs at a time.

Example:

Procedure insertempproc() is
   CURSOR emp_cur IS
      SELECT employee_id
        FROM emp;
cur_limit PLS_INTEGER := 1000;
TYPE multi_cols_pslqltable IS RECORD
(
emp_id   emp.emp_id%TYPE,
emp_name emp.ename%TYPE,
sal        emp.sal%TYPE
);
TYPE multi_cols_pslqltab IS TABLE OF multi_cols_pslqltable;
multi_cols_pslqltab_varr   multi_cols_pslqltab;
BEGIN
open emp_cur;
LOOP
begin
FETCH employees_cur
BULK COLLECT INTO multi_cols_pslqltab_varr
LIMIT c_limit;
       EXIT WHEN multi_cols_pslqltab_varr.COUNT = 0;
FORALL IND IN multi_cols_pslqltab_varr.FIRST..multi_cols_pslqltab_varr.LAST  SAVE              EXCEPTIONS
INSERT INTO EMP_NEW(EMPID,NAM, SAL, CMANE ) VALUES
(multi_cols_pslqltab_varr(IND).emp_id,multi_cols_pslqltab_varr(IND).emp_name,multi_c                  ols_pslqltab_varr(IND).'xyz');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
  NULL;
end;
   END LOOP;
insertempproc END;