Oracle Training in OMR Chennai

ORACLE Training Courses in Chennai

1. Oracle SQL
2. Oracle PL/SQL
3. Oracle Forms & Reports
4. Unix Shell Scripting
5. Oracle DBA
6. Oracle RAC DBA
7. Oracle Apps DBA
8. Oracle Apps Financial
9. Oracle Performance Tuning
10. Live Project as in real time environment following all SDLC Principles

Oracle Training in Chennai

Learn Oracle from the best ORACLE Training Institute in Chennai which offers complete ORACLE course in Chennai by well experienced ORACLE Certified Trainer having more than 12+ years of Real time Development experience using ORACLE Technologies. We are proud to mention that we are the Best institutes to provide ORACLE COMPLETE PRACTICAL REALTIME TRAINING and hence, closely working with various companies in India. We are, today, one of the few best training companies providing complete practical trainings on ORACLE Technologies.

Best Oracle Training In Chennai

ORACLE training course content is exclusively designed with Basics through Advanced Concepts. This ORACLE course is specifically designed as per the ORACLE Official Curriculum (MOC) to provide COMPLETE PRACTICAL Training on Oracle Technologies.

Best Oracle Training Institute in Chennai

We are committed to provide Complete PRACTICAL Training and ORACLE Certification Training exclusively on SQL, PL/SQL, DBA, RAC DBA, APPS DBA, ORACLE Live Projects, Performance Tuning, OCA and OCP Certification Study Material and Practice Material included with the course.

What is Oracle Parallel Query?

ALTER TABLE table_name PARALLEL (DEGREE 8);

SELECT --+ PARALLEL(table_alias, degree, nodes)
       * 
  FROM table_name ...

SELECT /*+PARALLEL(table_alias, degree, nodes)*/ * 
  FROM table_name ...

ALTER TABLE table_name PARALLEL (DEGREE 1 INSTANCES 1);

ALTER TABLE table_name NOPARALLEL;

ALTER INDEX index_name PARALLEL (DEGREE 1 INSTANCES 1);

ALTER INDEX IND_XXX NOPARALLEL;

SELECT * FROM sys.v$pq_sysstat;

SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;

Partitioning an Existing Table using EXCHANGE PARTITION

This article presents a simple method for partitioning an existing table using the EXCHANGE PARTITION syntax. The contents of the article should not be used as an indication of when and how to partition objects, it simply shows the method of getting from A to B. Remember, in many cases incorrect partitioning is worse than no partitioning!

1. 1. Create a Sample Table
   2. Create a Partitioned Destination Table
   3. EXCHANGE PARTITION
   4. SPLIT PARTITION

Create a Sample Table

First we create a sample table as our starting point.

-- Create and populate a small lookup table.
CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;

    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
  CONSTRAINT big_table_pk PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (
  CONSTRAINT bita_look_fk
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

Create a Partitioned Destination Table

Next we create a new table with the appropriate partition structure to act as the destination table. The destination must have the same constraints and indexes defined.

-- Create partitioned table.
CREATE TABLE big_table2 (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
  CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (
  CONSTRAINT bita_look_fk2
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);

With this destination table in place we can start the conversion.

EXCHANGE PARTITION

We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.

ALTER TABLE big_table2
  EXCHANGE PARTITION big_table_2007
  WITH TABLE big_table
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;

The exchange operation should not be affected by the size of the segments involved.

Once this is complete we can drop the old table and rename the new table and all it’s constraints.

DROP TABLE big_table;
RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

SPLIT PARTITION

Next, we split the single large partition into smaller partitions as required.

ALTER TABLE big_table
  SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION big_table_2005,
        PARTITION big_table_2007)
  UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
  SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION big_table_2006,
        PARTITION big_table_2007)
  UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

The following queries show that the partitioning was successful.

SELECT partitioned
FROM   user_tables
WHERE  table_name = 'BIG_TABLE';

PAR
---
YES

1 row selected.

SELECT partition_name, num_rows
FROM   user_tab_partitions
WHERE  table_name = 'BIG_TABLE';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
BIG_TABLE_2005                     335326
BIG_TABLE_2006                     332730
BIG_TABLE_2007                     334340

3 rows selected.