Pragma autonomous transaction

 An autonomous transaction is an independent transaction to the main or parent transaction. If an Autonomous transaction is started by another transaction it is not nested, but  independent of parent transaction.


In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below:
PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.

PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.

PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.

PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.

PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.

Introduction to REF CURSORs

Using REF CURSORs is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSORs are represented through the OracleRefCursor ODP.NET class.

REF CURSORs have the following characteristics:

A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.

A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.

A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.

A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.

A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

Which are the important performance parameters for MyISAM and InnoDB?

For MyISAM

key_cache_size
thread_cache_size
tmp_table_size
max_heap_table_size
read_buffer_size
query_cache

For InnoDB

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
tmp_table_size
max_heap_table_size
table_open_cache

What's the difference between MyISAM and InnoDB

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.


INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB

Partitioned Tables And Indexes in oracle

Range Partitioning Tables
Hash Partitioning Tables
Composite Partitioning Tables


range partition
***************


 create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
 partition by range(deptno) (partition p1 values less than(10),
    partition p2 values less than (20),
    partition p3 values less than (30),
    partition p4 values less than (maxvalue))




insert into emp_pat select * from emp;





SQL> explain plan for select * from emp_pat where deptno=40;

Explained.

SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 253299801

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |    87 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |     1 |    87 |     3   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | EMP_PAT |     1 |    87 |     3   (0)| 00:00:01 |     4 |     4 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=40)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> explain plan for select * from emp_pat where sal<=1050;

Explained.



SQL> select * from table (dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3695347441

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     2 |   174 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|         |     2 |   174 |     6   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL | EMP_PAT |     2 |   174 |     6   (0)| 00:00:01 |     1 |     4 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("SAL"<=1050)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>




range partition without maxvalue
********************************


SQL> drop table emp_pat;

Table dropped.

SQL>  create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2      mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3   partition by range(deptno) (partition p1 values less than(10),
  4      partition p2 values less than (20),
  5      partition p3 values less than (30))
  6  /

Table created.

SQL> insert into emp_pat select * from emp;
insert into emp_pat select * from emp
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

  or
  SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3       partition by range(sal) (partition p1 values less than(1000),
  4         partition p2 values less than (2000),
  5          partition p3 values less than (4000))
  6  /

Table created.


SQL> insert into emp_pat select * from emp;
insert into emp_pat select * from emp
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition --( one row is more than 5000 sal is there )


SQL> insert into emp_pat select * from emp where sal <=4000;

13 rows created.


SQL> select * from emp_pat partition (p1);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

SQL> select * from emp_pat partition (p2);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

6 rows selected.

SQL> select * from emp_pat partition (p3);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20


 
 
SQL> SELECT  TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME;

       TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
 ------------------------------ ------------------------------ -------------------------
           EMP_PAT                        P1                             1000
           EMP_PAT                        P2                             2000
           EMP_PAT                        P3                             4000

 
 
 
 
 
  range partition with Interval
********************************
 
  SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY RANGE(hiredate)
  4  INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
  5  (
  6     PARTITION p1 VALUES LESS THAN (TO_DATE('01-12-1980', 'DD-MM-YYYY')),
  7     PARTITION p2 VALUES LESS THAN (TO_DATE('1-12-1981', 'DD-MM-YYYY')) ,
  8       PARTITION p3 VALUES LESS THAN (TO_DATE('1-12-1982', 'DD-MM-YYYY'))
  9  )
 10  /

Table created.

 
 SQL> insert into emp_pat select * from emp where hiredate<='03-DEC-82';

12 rows created.

SQL> SELECT num_rows, TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME
  5  /

  NUM_ROWS TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------
           EMP_PAT                        P1                             TO_DATE(' 1980-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P2                             TO_DATE(' 1981-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P3                             TO_DATE(' 1982-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'



SQL> delete from emp_pat;

12 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into emp_pat select * from emp;

14 rows created.

SQL> SELECT num_rows, TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  /

  NUM_ROWS TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
---------- ------------------------------ ------------------------------ --------------------------------------------------------------------------------
           EMP_PAT                        P1                             TO_DATE(' 1980-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P2                             TO_DATE(' 1981-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        P3                             TO_DATE(' 1982-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
           EMP_PAT                        SYS_P21                        TO_DATE(' 1983-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'

 
 
 
  List Partition
  ****************
 
 SQL>  create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY LIST(deptno)
  4  (Partition p1 values (10),
  5  partition p2 values (20,30),
  6  partition p3 values (default))
  7  /

Table created.

SQL>  create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY LIST(deptno)
  4  (Partition p1 values (10),
  5  partition p2 values (20,30),
  6  partition p3 values (default))
  7  /

Table created.

SQL> insert into emp_pat select * from emp;

14 rows created.


SQL> SELECT num_rows, TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME;

         TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
  ------------------------------ ------------------------------ --------------------------------------------------------------------------------
           EMP_PAT                        P1                             10
           EMP_PAT                        P2                             20, 30
           EMP_PAT                        P3                             default

   SQL> select * from emp_pat partition (p3);

no rows selected

SQL> select * from emp_pat partition (p2);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

11 rows selected.

SQL> select * from emp_pat partition (p1);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> explain plan for select empno,ename,sal,deptno from emp_pat where deptno=20;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3226506680

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |    46 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|         |     1 |    46 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL   | EMP_PAT |     1 |    46 |     3   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=20)

14 rows selected.

SQL> explain plan for select empno,ename,sal,deptno from emp_pat where sal<=3000;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273502597

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    46 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|         |     1 |    46 |     4   (0)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| EMP_PAT |     1 |    46 |     4   (0)| 00:00:01 |     1 |     3 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("SAL"<=3000)

14 rows selected.



  List Partition without (default )
  *********************

SQL> drop table emp_pat;

Table dropped.

SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY LIST(deptno)
  4  (Partition p1 values (10),
  5  partition p2 values (20))
  6  /

Table created.

SQL>
SQL> SELECT  TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME
  5  /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
EMP_PAT                        P1                             10
EMP_PAT                        P2                             20

SQL>

 SQL> insert into emp_pat select * from emp;
insert into emp_pat select * from emp
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


 
             HASH Partitioning
             *******************
 
 SQL>   create  table emp_pat (empno number(4) not null,ename varchar2(10),job varchar2(9),
  2    mgr number(4),hiredate date,sal number(7,2) ,comm number(7,2),deptno number(2))
  3  PARTITION BY HASH(deptno)
  4  Partitions 4;

Table created.

SQL> insert into emp_pat select * from emp;

14 rows created.

SQL> SELECT  TABLE_NAME,    PARTITION_NAME,    HIGH_VALUE
  2  FROM    USER_TAB_PARTITIONS
  3  WHERE   TABLE_NAME='EMP_PAT'
  4  ORDER BY  PARTITION_NAME
  5  /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
EMP_PAT                        SYS_P22
EMP_PAT                        SYS_P23
EMP_PAT                        SYS_P24
EMP_PAT                        SYS_P25

SQL> select * from emp_pat partition (SYS_P22);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

SQL> select * from emp_pat partition (SYS_P23);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> select * from emp_pat partition (SYS_P24);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

SQL> select * from emp_pat partition (SYS_P25);

no rows selected

SQL> explain plan for select * from emp_pat where sal>100;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1594423551

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |  1218 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH ALL|         |    14 |  1218 |     6   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL| EMP_PAT |    14 |  1218 |     6   (0)| 00:00:01 |     1 |     4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("SAL">100)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL>
SQL>
SQL> explain plan for select sal,empno,ename from emp_pat where deptno=10;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3260685292

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     3 |   138 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION HASH SINGLE|         |     3 |   138 |     3   (0)| 00:00:01 |     2 |     2 |
|*  2 |   TABLE ACCESS FULL   | EMP_PAT |     3 |   138 |     3   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.







 
 
 
 

  

ORA-14402: updating partition key column would cause a partition change

SQL> CREATE TABLE honnikery
  2  (
  3    id NUMBER       NOT NULL,
  4    name       VARCHAR2(12) NOT NULL,
  5    name_id   VARCHAR2(30) NOT NULL
  6  )
  7  PARTITION BY LIST (CITY_ID)
  8  (
  9    PARTITION P282 VALUES ('1'),
 10    PARTITION P283 VALUES ('10'),
 11    PARTITION P284 VALUES ('20'));
Table created.
SQL>
SQL> INSERT INTO honnikery VALUES (1,'prabhakr','1');
1 row created.
SQL> INSERT INTO honnikery VALUES (2,'honnikery','1');
1 row created.
SQL> INSERT INTO honnikery VALUES (3,'pawan','1');
1 row created.
SQL> INSERT INTO honnikery VALUES (4,'daivik','1');
1 row created.
SQL> COMMIT;
Commit complete.




SQL> UPDATE honnikery SET name_id = '1' WHERE id = 1;
UPDATE honnikery SET name_id = '1' WHERE id = 1
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change




SQL> ALTER TABLE honnikery ENABLE ROW MOVEMENT;
Table altered.
SQL>UPDATE honnikery SET name_id = '1' WHERE id = 1;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> ALTER TABLE honnikery DISABLE ROW MOVEMENT;
Table altered.