• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: ORACLE 12c Release 2

ORA12R2 – IMPdp may change segment_column_id

25 Monday May 2020

Posted by troroblog in BUG, impdp, ORACLE, ORACLE 12c Release 2

≈ Leave a comment

This is a case we faced just a couple of days ago and somehow I cannot believe what I see.

We have a table containing a invisible column which was added to the table and therefor was placed at the end of the segment.


Testcase

drop table myemp;

create table myemp (empno    number(4)     not null
                   ,ename    varchar2(10)
                   ,job      varchar2(10)
                   ,mgr      number(4)
                   ,hiredate date
                   ,sal      number(6)
                   ,comm     number(4)
                   ,deptno   number(2));

alter table myemp add constraint myemp_pk primary key (empno);

Insert into MYEMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
values (7369,'SMITH','CLERK',7902,date '1980-12-17',800,null,20);
...
commit;

alter table myemp add birthday date invisible;

update myemp set birthday = add_months(hiredate,-18);

commit;

col column_name format a15

select column_name
      ,hidden_column
      ,column_id
      ,segment_column_id
      ,internal_column_id
  from user_tab_cols
 where table_name = 'MYEMP';

COLUMN_NAME HID COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- --- --------- ----------------- ------------------
EMPNO       NO          1                 1                  1
ENAME       NO          2                 2                  2
JOB         NO          3                 3                  3
MGR         NO          4                 4                  4
HIREDATE    NO          5                 5                  5
SAL         NO          6                 6                  6
COMM        NO          7                 7                  7
DEPTNO      NO          8                 8                  8
BIRTHDAY    YES                           9                  9

9 rows selected.


expdp usr/pwd tables=MYEMP directory=EXP dumpfile=myemp.dmp logfile=myemp.log reuse_dumpfiles=yes

drop table myemp;

impdp usr/pwd tables=MYEMP directory=EXP dumpfile=myemp.dmp logfile=myemp.log

select column_name
      ,hidden_column
      ,column_id 
      ,segment_column_id 
      ,internal_column_id
  from user_tab_cols
 where table_name = 'MYEMP';

COLUMN_NAME HID COLUMN_ID SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
----------- --- --------- ----------------- ------------------
BIRTHDAY    YES                           1                  1
EMPNO       NO          1                 2                  2
ENAME       NO          2                 3                  3
JOB         NO          3                 4                  4
MGR         NO          4                 5                  5
HIREDATE    NO          5                 6                  6
SAL         NO          6                 7                  7
COMM        NO          7                 8                  8
DEPTNO      NO          8                 9                  9

9 rows selected.

Notes to this behaviour:

  • Oracle 19 does it correct, segment_column_id, internal_column_id remains unchanged
  • It is a problem of impdp
    • I tried to import an Oracle 12 export using the import utility of Oracle 19 and it turned out ok
    • I tried to import an Oracle 19 export using the import utility of Oracle 12 and it turned out wrong
  • The data is correct
  • I have not tried with Oracle 18c

Is it a bug?

Yes it is in my opinion – I just have to convince Oracle Support of this. They were able to reproduce but offered a workaround … which is not really an option for us. We will move to Oracle 19c in the next couple of months and we have this problem in our development environment only but still I was really surprised facing something like this…

Update (May 27, 2020)

The problematic piece of code is not datapump but DBMS_METADATA which returns the columns in the wrong order.

Oracle has opened a new SR for this – I am keeping an eye on it.

Package Initialization – Changes between ORACLE 11g and ORACLE 12c

16 Wednesday May 2018

Posted by troroblog in ORACLE, ORACLE 11g Release 2, ORACLE 12c Release 1, ORACLE 12c Release 2, ORACLE 18c, PL/SQL

≈ Leave a comment

The package initialization block

When a session references a package item, the package is instantiated and initialized. Initializing means:

  • assigning values to public constants (constants defined in the package specification or body outside of packaged procedures and functions)
  • assigning values to public variables if values are specified in variable declaration
  • execute the initial block of the package body  
CREATE OR REPLACE PACKAGE test_init IS
   PROCEDURE show_values;
END test_init;
/

CREATE OR REPLACE PACKAGE BODY test_init IS
   g_before_error VARCHAR2(30) := 'NOT INITIALIZED';
   g_after_error  VARCHAR2(30) := 'NOT INITIALIZED';
   g_test         VARCHAR2(5);

   PROCEDURE show_values IS
   BEGIN
      sys.dbms_output.put_line('Before : ' || g_before_error);
      sys.dbms_output.put_line('After  : ' || g_after_error);
   END show_values;

-- INIT BLOCK
BEGIN
   g_before_error := 'INITIALIZED';
   g_test         := 'This will raise an error';
   g_after_error  := 'INITIALIZED';
END test_init;
/

What happens if the initialization block raises an error?

Now basically you get an error – as we would expect.

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So far so good – but now, a significant thing has changed between ORACLE 11 and ORACLE 12.


What happens if we call the show_values procedure again?

ORACLE 12 (same in 18)

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So in ORACLE 12 the initialization block is executed again. This indicates, that the package was not instantiated due to the error in the initialization block. 

ORACLE 11

BEGIN
   test_init.show_values();
END;
/

PL/SQL procedure successfully completed.

Before : INITIALIZED
After  : NOT INITIALIZED

In ORACLE 11 the packages has been instantiated even if the initialization run into an error. So a second call will not run the initialization again…

As you can see the second global variable (g_after_error) has not been changed by the initialization block whereas the first was.

 


What does this mean?

In my opinion the current (12c/18c) behaviour is much more consistent…but this change in behaviour can (and will) affect applications that did not care too much on whether the initialization run into an error in Oracle 11g – as a secondary call to a package item that does not depend on a correct initialization of the package worked..

 

 

 

ORACLE 12.2 – External Table Enhancements – Overriding Parameters

26 Wednesday Jul 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, SQL

≈ 1 Comment

Another cool feature of 12.2 is the possibility to override defined parametrs of an external table when accessing it. The following properties may be overwritten:

  • default directory
  • location (file-name)
  • access parameters like badfile, logfile and discardfile
  • reject limit

This enables us to change e.g. the name of the file to be read without having to do an ALTER TABLE statement (and therefore doing an impicit commit). Furthermore the modification done this way is effective for the current statement only.

So imagine we have an external table defined like this:

CREATE TABLE employee_import (    
   department_id  NUMBER(2,0)  
  ,first_name     VARCHAR2(20 BYTE)  
  ,last_name      VARCHAR2(20 BYTE)  
  ,email          VARCHAR2(25 BYTE)  
  ,phone_number   VARCHAR2(20 BYTE)  
  ,hire_date      DATE
  ,job_id         VARCHAR2(10 BYTE)
  ,salary         NUMBER(8,2)  
  ,commission_pct NUMBER(2,2)
) 
ORGANIZATION EXTERNAL (    
   TYPE ORACLE_LOADER   
   DEFAULT DIRECTORY LTROT_VM_DIR   
   ACCESS PARAMETERS (       
      RECORDS DELIMITED BY NEWLINE      
      LOGFILE  LTROT_VM_DIR:'emp20_wrong.log'      
      BADFILE  LTROT_VM_DIR:'emp20_wrong.bad'      
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL (         
         department_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date date 'DD.MM.YYYY'
        ,job_id
        ,salary
        ,commission_pct
      )
   )
   LOCATION('emp20.txt')
)
REJECT LIMIT UNLIMITED ;

Now, if we would like to access a different os file (having the same structure) than “emp20.txt” we can easily achieve this with the following query:

SELECT *
 FROM employee_import 
 EXTERNAL MODIFY (LOCATION ('em10.txt'));

The parameters that can be changed this way are:

  • Default Directory
  • Location
  • Access Parameters
  • Reject Limit

If the external is partitioned only table level (not partition level) clauses may be overwritten.


Use-Case

Now, looking at the list of parameters that can be changed it should be possible to change the name of the bad-/log-file when loading the emp-os file and afterwards having an external table where i can read the bad-/log-file passing the names that i used before….

Something like this:

Generic External Table to read log/bad files

CREATE TABLE generic_file (
    message VARCHAR2(4000 BYTE)
) 
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
   DEFAULT DIRECTORY LTROT_VM_DIR
   ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      NOLOGFILE
      NOBADFILE
      NODISCARDFILE
      FIELDS (
         message CHAR(4000)
      )
   )
   LOCATION('emp20.bad')
)
REJECT LIMIT UNLIMITED;

So when I read the emp os-file I alter the name of the bad and the log file using the external modify clause:

SELECT * 
  FROM employee_import
EXTERNAL MODIFY (
   ACCESS PARAMETERS (
      BADFILE 'abadfile.bad'
      LOGFILE 'alogfile.log'));

And after that I should be able to access the bad-/log-file using my generic external table.

SELECT * 
  FROM generic_file 
EXTERNAL MODIFY (
   LOCATION('abadfile.bad'));

Unfortunately I cannot see any data, as the log-/bad-files are locked and empty (Linux Environment) so I need to commit first to release the locks. Afterwards I can read the log-file as well as the bad-file.

SELECT * 
 FROM generic_file 
EXTERNAL MODIFY (
 LOCATION('alogfile.log'))
 WHERE message IS NOT NULL;

MESSAGE
---------------------------------------------------------------------------
 LOG file opened at 07/26/17 07:25:18
Field Definitions for table EMPLOYEE_IMPORT
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Rows with all null fields are accepted
 Fields in Data Source:
 DEPARTMENT_ID CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 FIRST_NAME CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 LAST_NAME CHAR (255)
 Terminated by ","
...

Conclusion

Nice!

ORACLE 12.2 – Enhanced Whitelist (ACCESSIBLE BY)

25 Tuesday Apr 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

With Version 12.1 ORACLE provided a new way to determine that a program unit may only be called by other program units which are part of a whilelist. This new feature was implemented using the ACCESSIBLE BY clause. ACCESSIBLE BY was declared on top level (e.g. package specification).

ORACLE 12.2 provides a higher granularity for the whitelist. It is now possible to define the access on a packaged procedure/function too.

Code Example:

CREATE OR REPLACE PACKAGE emp_tapi IS
   TYPE emp_tapi_rect IS RECORD (ename    emp.ename%TYPE
                               , comm     emp.comm%TYPE
                               , hiredate emp.hiredate%TYPE
                               , empno    emp.empno%TYPE
                               , mgr      emp.mgr%TYPE
                               , job      emp.job%TYPE
                               , deptno   emp.deptno%TYPE
                               , sal      emp.sal%TYPE);

   TYPE emp_tapi_tabt IS TABLE OF emp_tapi_rect;

-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- update
-- ========================================================================
   PROCEDURE upd (emp_tapi_rec IN emp_tapi_rect) 
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- delete
-- ========================================================================
   PROCEDURE del (p_empno IN emp.empno%type)
      ACCESSIBLE BY (TRIGGER emp_io_d_t);
END emp_tapi;
/

CREATE OR REPLACE PACKAGE BODY emp_tapi IS
-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up)
   IS
   BEGIN
      ...

As you can see, ACCESSIBLE BY clause has to be specified in specification and body, if we want to define the whitelist on subprograms. 


Conclusion

The ACCESSIBLE BY clause is very useful (e.g. refactoring) and the higher granularity will help to make it even better.

ORACLE 12.2 – Coverage Pragma and DBMS_PLSQL_CODE_COVERAGE

20 Monday Mar 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

The new package DBMS_PLSQL_CODE_COVERAGE helps use to collect data on how well our testcases exercise our code.

Basic block coverage

The DBMS_PLSQL_CODE_COVERAGE package collects data at the basic block level. A basic block is a segment of code with no branches, the basic block has a single entry and a single exit point.

Preparation

The code coverage is collected in 3 tables (DBMSPCC_RUNS, DBMSPCC_UNITS, DBMSPCC_BLOCKS). Those tables may be created using the create_coverage_tables procedure of dbms_plsql_code_coverage. The procedure has a single boolean parameter (force_it) which would drop/create the tables if they already exist. The default value of the parameter is false. If this parameter remains false and the tables alreday exist, the execution of the procedure will raise an exception (dbms_plsql_code_coverage.coverage_error).

Run the code coverage analysis

To run the code coverage analysis we call the start_coverage function which accepts a run_comment to be able to identify your analysis run later and returns a unique run_id. Stoping the analysis is done using the stop_coverage procedure which is parameterless. 

DECLARE
   l_run_id pls_integer;
BEGIN
   l_run_id := dbms_plsql_code_coverage.start_coverage('COVERAGETEST1');
   coveragetest1(100);
   sys.dbms_plsql_code_coverage.stop_coverage();
END;
/

Analyse the code coverage results

The data collected by the code coverage package may be analysed using a simple query joining the 3 tables (DBMSPCC_RUNS, DBMPSPCC_UNITS, DBMSPCC_BLOCKS).

SELECT r.run_comment
      ,u.owner as object_owner
      ,u.name as object_name
      ,u.type as object_type
      ,ROUND((COUNT(CASE b.covered WHEN 1 THEN 1 ELSE NULL END) 
            + COUNT(CASE WHEN     b.covered = 0 
                              AND b.not_feasible = 1 THEN 1 ELSE NULL END)) 
         / NULLIF(COUNT(*),0) * 100,2) AS pct_covered
  FROM dbmspcc_runs   r
  JOIN dbmspcc_units  u ON (u.run_id = r.run_id)
  JOIN dbmspcc_blocks b ON (    b.object_id = u.object_id
                            AND b.run_id = r.run_id)
 GROUP BY r.run_comment
         ,u.owner
         ,u.name
         ,u.type;

RUN_COMMENT    OBJECT_OWNER  OBJECT_NAME    OBJECT_TYPE  PCT_COVERED
-------------  ------------  -------------  -----------  -----------
COVERAGETEST1  TEST          COVERAGETEST1  PROCEDURE           37.5

Now, if you have a closer look to the query, you may see that there are not only covered and uncovered blocks in the DBMSPCC_BLOCKS table but also some marked as “not feasible”. 


NOT FEASIBLE PRAGMA

If we want to exclude certain blocks from the code analysis, we may mark them using a new pragma as not feasible.

 1 CREATE OR REPLACE PROCEDURE coveragetest2(in_param IN INTEGER) AS 
 2    l_res integer;
 3 BEGIN
 4    l_res := in_param mod 2;
 5 
 6    -- mark a range of blocks as not feasible
 7   PRAGMA COVERAGE ('NOT_FEASIBLE_START');
 8   IF l_res = 0 THEN
 9      sys.dbms_output.put_line(in_param || ' is even');
10   ELSE
11      sys.dbms_output.put_line(in_param || ' is odd');
12   END IF;
13   PRAGMA COVERAGE ('NOT_FEASIBLE_END');
14
15   IF l_res = 0 THEN 
16      sys.dbms_output.put_line(in_param || ' is even');
17   ELSIF l_res = 1 THEN 
18      sys.dbms_output.put_line(in_param || ' is odd');
19   ELSIF l_res IS NULL THEN
20      sys.dbms_output.put_line(in_param || ' is null');
21   ELSE
22      PRAGMA COVERAGE ('NOT_FEASIBLE');
23      sys.dbms_output.put_line(in_param || ' is unknown');
24   end if;
25   sys.DBMS_OUTPUT.PUT_LINE('That''s all');
26 END coveragetest2;
/

So what happens if we do a code analysis of this procedure, which has “not feasible” blocks in it?

SELECT block, line, col, covered, not_feasible
 FROM dbmspcc_blocks 
WHERE run_id = (SELECT MAX(run_id) 
 FROM dbmspcc_runs) 
 ORDER BY LINE;


     BLOCK       LINE        COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
         1          1          1          1            1
         2          9          6          1            1
         3         11          6          0            1
         4         16          7          1            0
         6         17         16          0            0
         7         18          7          0            0
         8         19         10          0            0
         9         20          7          0            0
        10         22          7          0            1
         5         25          4          1            0

10 rows selected. 

Code coverage is also recorded if a block or a set of blocks are set to “not feasible”. But still this pragma may be used not only to mark “unreachable” code but also to mark code we do not care to cover during tests (which i would not call good practice).

ORACLE 12.2 -Auto List Partitioning

08 Sunday Jan 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, Partitioning

≈ Leave a comment

Another new feature in the partitioning aerea is the automatic list partitioning. Like with the interval partitioning that came with ORACLE 11.1 the automatic list partitioning creates new partitions as they are required.

The following lines show an example of automatic list partitioning:

Example:

-- =============================================================================
-- Create an automatic list partitioned table
-- =============================================================================
CREATE TABLE employees_part (
   employee_id     NUMBER(4)
  ,first_name      VARCHAR2(30)
  ,last_name       VARCHAR2(30)
  ,email           VARCHAR2(30)
  ,phone_number    VARCHAR2(20)
  ,hire_date       DATE
  ,salary          NUMBER(6,0)
  ,commission_pct  NUMBER(3,2)
  ,manager_id      NUMBER(4)
  ,department_id   NUMBER(4)
)
PARTITION BY LIST (department_id) AUTOMATIC (
   PARTITION P_10 VALUES (10)
);

SELECT partition_name, high_value, partition_position
  FROM all_tab_partitions 
 WHERE table_name = 'EMPLOYEES_PART';

PARTITION_NAME       HIGH_VALUE           PARTITION_POSITION
-------------------- -------------------- ------------------
P_10                 10                                    1


Adding new values to the partitioned table:

In a first step I try to create a list partitioned table having a global and a local index. 

INSERT INTO employees_part (employee_id
                          , first_name
                          , last_name
                          , email
                          , phone_number
                          , hire_date
                          , salary
                          , commission_pct
                          , manager_id
                          , department_id)
SELECT employee_id
     , first_name
     , last_name
     , email
     , phone_number
     , hire_date
     , salary
     , commission_pct
     , manager_id
     , department_id
 FROM employees;

107 rows inserted.

SELECT partition_name, high_value, partition_position
  FROM all_tab_partitions 
 WHERE table_name = 'EMPLOYEES_PART';

PARTITION_NAME       HIGH_VALUE           PARTITION_POSITION
-------------------- -------------------- ------------------
P_10                 10                                    1
SYS_P2161            90                                    2
SYS_P2162            60                                    3
SYS_P2163            100                                   4
SYS_P2164            30                                    5
SYS_P2165            50                                    6
SYS_P2166            80                                    7
SYS_P2167            NULL                                  8
SYS_P2168            20                                    9
SYS_P2169            40                                   10
SYS_P2170            70                                   11
SYS_P2171            110                                  12

How to find out whether a partitioned table is set to automatic?

The information whether a table is set to automatic is stored in the …_PART_TABLES dictionary views.

SELECT table_name, partitioning_type, autolist 
  FROM user_part_tables;

TABLE_NAME      PARTITIONING_TYPE  AUTOLIST
--------------- ------------------ --------
EMPLOYEES_PART  LIST               YES

Conclusion:

The new automatic list partitioning feature dispenses us from the duty to either have to manually (or programatically) create new partitions for new values or having a default partition to store all values that are not covered by the existing partitions. 

This burdon was not that big…but it is still a useful simplification.

ORACLE 12.2 – Convert a non-partitioned table to a partitioned table

31 Saturday Dec 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, Partitioning

≈ Leave a comment

ORACLE 12.2 allows the conversion of a non partitioned table to a partitioned table using the ALTER TABLE command. The whole conversion can be done “online” meaning, that during the conversion DML operations against the table are allowed.

Preparation:

-- =============================================================================
-- Create a non partitioned Table
-- =============================================================================
CREATE TABLE emp_dept
AS
SELECT e.empno ,e.ename ,e.job ,e.mgr
      ,e.hiredate ,e.sal ,e.comm ,d.deptno
      ,d.dname ,d.loc
  FROM emp e
  JOIN dept d ON (e.deptno = d.deptno);

-- =============================================================================
-- Add 2 indexes to the table
-- =============================================================================
CREATE UNIQUE INDEX emp_dept_empno_idx ON emp_dept(empno);
CREATE INDEX emp_dept_deptno_empno_idx ON emp_dept (deptno, empno);

 


ALTER TABLE MODIFY:

In a first step I try to create a list partitioned table having a global and a local index. 

-- =============================================================================
-- ALTER TABLE MODIFY
-- =============================================================================
ALTER TABLE emp_dept MODIFY
   PARTITION BY LIST (deptno) (
      PARTITION P_DEPT10 VALUES (10)
      ,PARTITION P_DEPT20 VALUES (20)
      ,PARTITION P_DEPT30 VALUES (30)
      ,PARTITION P_DEPT40 VALUES (40)
      ,PARTITION P_DEFAULT VALUES (DEFAULT)
   ) 
ONLINE
UPDATE INDEXES (
   emp_dept_empno_idx GLOBAL
  ,emp_dept_deptno_empno_idx LOCAL); 

-- =============================================================================
-- Check partitions
-- =============================================================================
SELECT table_name, partition_name
  FROM user_tab_partitions
 WHERE table_name = 'EMP_DEPT';

TABLE_NAME                     PARTITION_NAME 
------------------------------ ------------------------------
EMP_DEPT                       P_DEFAULT 
EMP_DEPT                       P_DEPT10 
EMP_DEPT                       P_DEPT20 
EMP_DEPT                       P_DEPT30 
EMP_DEPT                       P_DEPT40 

-- ============================================================================= 
-- Check indexes 
-- ============================================================================= 
SELECT index_name, uniqueness, status, partitioned 
  FROM user_indexes 
 WHERE table_name = 'EMP_DEPT';

INDEX_NAME                     UNIQUENES STATUS   PARTITIONED 
------------------------------ --------- -------- ---------------
EMP_DEPT_DEPTNO_EMPNO_IDX      NONUNIQUE N/A      YES 
EMP_DEPT_EMPNO_IDX             UNIQUE    VALID    NO 

SELECT index_name, uniqueness, status, partitioned 
 FROM user_indexes 
 WHERE table_name = 'EMP_DEPT';

INDEX_NAME                     PARTITION_NAME                 STATUS 
------------------------------ ------------------------------ --------
EMP_DEPT_DEPTNO_EMPNO_IDX      P_DEFAULT                      USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      P_DEPT10                       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      P_DEPT20                       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      P_DEPT30                       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      P_DEPT40                       USABLE

How about subpartitioning?

Now, let’s try to modify a table and create partitions and subpartitions from a non partitioned table. The base will be the same emp_dept table with the same indexes as in the previous example.

-- =============================================================================
-- ALTER TABLE MODIFY
-- =============================================================================
ALTER TABLE emp_dept MODIFY
   PARTITION BY RANGE (hiredate) SUBPARTITION BY LIST (deptno) ( 
      PARTITION hiredate_p1 VALUES LESS THAN (date '1990-01-01') ( 
         SUBPARTITION hiredate_p1_10 VALUES (10)
        ,SUBPARTITION hiredate_p1_20 VALUES (20)
        ,SUBPARTITION hiredate_p1_30 VALUES (30))
     ,PARTITION hiredate_p2 VALUES LESS THAN (date '2000-01-01') ( 
        SUBPARTITION hiredate_p2_10 VALUES (10)
       ,SUBPARTITION hiredate_p2_20 VALUES (20)
       ,SUBPARTITION hiredate_p2_30 VALUES (30))
   )
ONLINE
UPDATE INDEXES (
   emp_dept_empno_idx GLOBAL
  ,emp_dept_deptno_empno_idx LOCAL); 

-- =============================================================================
-- Check partitions
-- =============================================================================
SELECT utp.table_name, utp.partition_name, utsp.subpartition_name
  FROM user_tab_partitions utp
  JOIN user_tab_subpartitions utsp ON (utsp.partition_name = utp.partition_name)
 WHERE utp.table_name = 'EMP_DEPT'
 ORDER BY utp.partition_name, utsp.subpartition_name;

TABLE_NAME                   PARTITION_NAME             SUBPARTITION_NAME 
---------------------------- -------------------------- -------------------------
EMP_DEPT                     HIREDATE_P1                HIREDATE_P1_10 
EMP_DEPT                     HIREDATE_P1                HIREDATE_P1_20 
EMP_DEPT                     HIREDATE_P1                HIREDATE_P1_30 
EMP_DEPT                     HIREDATE_P2                HIREDATE_P2_10 
EMP_DEPT                     HIREDATE_P2                HIREDATE_P2_20 
EMP_DEPT                     HIREDATE_P2                HIREDATE_P2_30 

-- ============================================================================= 
-- Check indexes 
-- ============================================================================= 
SELECT index_name, uniqueness, status, partitioned 
  FROM user_indexes 
 WHERE table_name = 'EMP_DEPT';

INDEX_NAME                     UNIQUENES STATUS   PARTITIONED 
------------------------------ --------- -------- ---------------
EMP_DEPT_DEPTNO_EMPNO_IDX      NONUNIQUE N/A      YES 
EMP_DEPT_EMPNO_IDX             UNIQUE    VALID    NO 

SELECT uip.index_name, uip.partition_name, uip.subpartition_name, uip.status
 FROM user_ind_subpartitions uip
 JOIN user_indexes ui ON (ui.index_name = uip.index_name)
 WHERE ui.table_name = 'EMP_DEPT';

INDEX_NAME                     PARTITION_NAME       SUBPARTITION_NAME    STATUS 
------------------------------ -------------------- -------------------- --------
EMP_DEPT_DEPTNO_EMPNO_IDX      HIREDATE_P1          HIREDATE_P1_10       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      HIREDATE_P1          HIREDATE_P1_20       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      HIREDATE_P1          HIREDATE_P1_30       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      HIREDATE_P2          HIREDATE_P2_10       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      HIREDATE_P2          HIREDATE_P2_20       USABLE 
EMP_DEPT_DEPTNO_EMPNO_IDX      HIREDATE_P2          HIREDATE_P2_30       USABLE 

Conclusion:

Everything worked as expected. 

 

ORACLE 12.2 – create table for exchange with clause

30 Friday Dec 2016

Posted by troroblog in ORACLE, ORACLE 12c Release 2, Partitioning, SQL

≈ Leave a comment

With ORACLE 12.2 the create table statement has been enriched by the new “for exchange with” clause. This enables us to create a exchange table that is a clone of the partitioned table which is the target of a later exchange table command

The new clause takes care of datatypes, data scale and precision, NOT NULL constraints, hidden column, virtual columns, unused columns,  etc.

Indexes, constraints (other than NOT NULL) are not cloned.

Preparation:

-- =============================================================================
-- Create Partitioned Table
-- =============================================================================
CREATE TABLE EMP_PART ( 
   EMPNO    NUMBER(4,0)       NOT NULL
  ,ENAME    VARCHAR2(10 CHAR) NOT NULL
  ,JOB      VARCHAR2(9 CHAR)  NOT NULL
  ,MGR      NUMBER(4,0)
  ,HIREDATE DATE              NOT NULL
  ,SAL      NUMBER(7,2)
  ,COMM     NUMBER(7,2)
  ,DEPTNO   NUMBER(2,0)       NOT NULL
  ,CONSTRAINT chk_sal_comm_less_1000 CHECK (NVL(sal,0) + NVL(comm,0) < 10000)
  ,CONSTRAINT chk_empno CHECK (empno between 1000 and 9999)
) 
PARTITION BY LIST (deptno) (
   PARTITION p_accounting VALUES (10)
  ,PARTITION p_research   VALUES (20)
  ,PARTITION p_sales      VALUES (30)
  ,PARTITION p_default    VALUES (DEFAULT)
);

-- =============================================================================
-- Create Local Index
-- =============================================================================
CREATE INDEX emp_part_idx ON EMP_PART (hiredate)
   LOCAL (
      PARTITION p_accounting_hiredate_idx
     ,PARTITION p_research_hiredate_idx
     ,PARTITION p_sales_hiredate_idx
     ,PARTITION p_default_hiredate_idx
);

-- =============================================================================
-- Add a Primary Key
-- =============================================================================
ALTER TABLE emp_part 
   ADD CONSTRAINT emp_part_pk
   PRIMARY KEY (empno);

-- =============================================================================
-- Fill Data
-- =============================================================================
INSERT INTO emp_part (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
  FROM emp
 WHERE deptno != 20;
 
COMMIT;

-- =============================================================================
-- add a virtual column
-- =============================================================================
ALTER TABLE emp_part
   ADD total_income GENERATED ALWAYS AS (NVL(SAL,0) + NVL(COMM,0));

-- =============================================================================
-- set a column to be hidden
-- =============================================================================
ALTER TABLE emp_part MODIFY deptno INVISIBLE;

-- =============================================================================
-- set a column unused
-- =============================================================================
ALTER TABLE emp_part SET UNUSED COLUMN job;

If we wanted to load the data of department 20 using an exchange partition command with an existing table holding all those rows we somehow had to make sure that the table used for the exchange has the exact same structure, indexes, constraints, etc. as the partitioned table. The new clause in the create table command help us solving this (at least some of the issues).


Create exchange table:

-- =============================================================================
-- Create exchange table
-- =============================================================================
CREATE TABLE emp_part_exchange
  FOR EXCHANGE WITH TABLE emp_part;

-- =============================================================================
-- Fill exchange table w/o job as it is unused
-- =============================================================================
INSERT INTO emp_part_exchange(EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO
 FROM emp
 WHERE deptno = 20;

-- =============================================================================
-- Check the table
-- =============================================================================
SELECT column_name
      ,data_type || 
       CASE data_type
          WHEN 'VARCHAR2' THEN '(' || data_length || ')'
          WHEN 'NUMBER' THEN NULLIF('(' || data_precision || ',' || data_scale || ')','(,)')
          WHEN 'DATE' THEN NULL
       END AS data_type
      ,nullable
      ,hidden_column
      ,virtual_column
  FROM user_tab_cols
 WHERE table_name = 'EMP_PART_EXCHANGE'
 ORDER BY segment_column_id; 

COLUMN_NAME                    DATA_TYPE       NULL HIDDEN  VIRTUAL
------------------------------ --------------- ---- ------- --------
EMPNO                          NUMBER(4,0)     N    NO      NO 
ENAME                          VARCHAR2(10)    N    NO      NO 
SYS_C00003_16123011:26:29$     VARCHAR2(9)     Y    YES     NO 
MGR                            NUMBER(4,0)     Y    NO      NO 
HIREDATE                       DATE            N    NO      NO 
SAL                            NUMBER (7,2)    Y    NO      NO 
COMM                           NUMBER (7,2)    Y    NO      NO 
DEPTNO                         NUMBER (2,0)    N    YES     NO 
TOTAL_INCOME                   NUMBER (,)      Y    NO      YES 


SELECT constraint_name, constraint_type, search_condition_vc
  FROM user_constraints
 WHERE table_name = 'EMP_PART_EXCHANGE';

CONSTRAINT_NAME                TYPE SEARCH_CONDITION_VC 
------------------------------ ---- --------------------------------------------------
SYS_C008877                    C    "DEPTNO" IS NOT NULL 
SYS_C008878                    C    "EMPNO" IS NOT NULL 
SYS_C008879                    C    "ENAME" IS NOT NULL 
SYS_C008880                    C    "HIREDATE" IS NOT NULL 

SELECT index_name, index_type, partitioned
  FROM user_indexes
 WHERE table_name = 'EMP_PART_EXCHANGE';

no rows selected

Exchange Partition?

So if we did an exchange partition at this point we would fail if the exchange table is not empty…

-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Error starting at line : 1 in command -
ALTER TABLE emp_part 
 EXCHANGE PARTITION p_research 
 WITH TABLE emp_part_exchange
Error report -
SQL Error: ORA-14118: CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION
14118. 00000 - "CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the
 ALTER TABLE EXCHANGE PARTITION statement have CHECK constraint
 defined on them.
*Action: Ensure that the two tables do not have CHECK constraint
 defined on any column

Additional work to be done:

We still need to create constraints on the exchange table manually…

-- =============================================================================
-- Check constraint are not created but needed when exchanging the partition
-- =============================================================================
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_sal_comm_less_1000_ep 
   CHECK (NVL(sal,0) + NVL(comm,0) < 10000);
 
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_empno_ep 
   CHECK (empno between 1000 and 9999);

-- =============================================================================
-- Add primary key constraint as it is not created 
-- =============================================================================
ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT emp_part_pk_ep
   PRIMARY KEY (empno);

After this is done the exchange partition is working.

-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 

Table EMP_PART altered.

Conclusion:

The create table for exchange clause is nice but if would be even better if constraints would also be created on the clone. Furthermore the possibility to do a CTAS in combination with the for exchange clause would be on my wishlist.

ORACLE 12.2 – long names

03 Monday Oct 2016

Posted by troroblog in ORACLE 12c Release 2, SQL

≈ 3 Comments

Tags

ORACLE 12c Release 2, SQL

ORACLE 12.2 enables us to name our objects with names up to 128 byte long. Even if this has been something we were looking forward to for a very long time there are still many things to consider before deciding to cross the old 30 byte border.

  • Are there places in your PL/SQL code where you placed some VARCHAR2(30) variables to hold oracle object names?
  • What is the size of the columns in your log tables that you use to store the name of the object you are logging information about?
  • What about the variables you use in your PL/SQL code to put your dynamic SQL statements together? Are those variables wide enough?

How about this one:

  • If you are using some dynamic SQL that evaluates a dynamic select list using SYS.DBMS_SQL.DESCRIBE_COLUMNS … then this is a point where you will have to do some changes…

EXAMPLE: Problems using DBMS_SQL

CREATE TABLE Table_with_a_name_that_is_so_much_longer_than_usual (
   column_with_a_very_long_name_as_it_is_possible_in_other_databases NUMBER(5)
  ,another_column_with_also_a_very_very_long_name                    VARCHAR2(100)
);

Table TABLE_WITH_A_NAME_THAT_IS_SO_MUCH_LONGER_THAN_USUAL created.

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns(c       => l_handle
                                ,col_cnt => l_column_counter 
                                ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SQL", line 2084
ORA-06512: at line 10
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
 occurred. For example, this error occurs if an attempt is made to
 assign the value NULL to a variable declared NOT NULL, or if an
 attempt is made to assign an integer larger than 99 to a variable
 declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
 that values do not violate constraints.

The problem is, that desc_rec in DBMS_SQL is still limited to 30 character length for the column names of the result.

To support long names ORACLE has implemented a new function and new data structures in DBMS_SQL.  


EXAMPLE: Solution

DECLARE
   l_handle         PLS_INTEGER := sys.dbms_sql.open_cursor();
   l_column_counter NUMBER;
   l_describe_table_to_describe_a_very_long_table sys.dbms_sql.desc_tab2;
BEGIN
   sys.dbms_sql.parse(c             => l_handle
                     ,statement     => 'SELECT * FROM Table_with_a_name_that_is_so_much_longer_than_usual'
                     ,language_flag => sys.dbms_sql.NATIVE);

   sys.dbms_sql.describe_columns2(c       => l_handle
                                 ,col_cnt => l_column_counter 
                                 ,desc_t  => l_describe_table_to_describe_a_very_long_table);
 
   FOR i IN 1..l_column_counter
   LOOP
      sys.dbms_output.put_line('Column Name : ' || l_describe_table_to_describe_a_very_long_table(i).col_name);
   END LOOP;
END;
/

Column Name : COLUMN_WITH_A_VERY_LONG_NAME_AS_IT_IS_POSSIBLE_IN_OTHER_DATABASES
Column Name : ANOTHER_COLUMN_WITH_ALSO_A_VERY_VERY_LONG_NAME

Conclusion

Having the possibility to give decent names to objects – e.g. naming an index addresses_employees_usages_fk_index – instead of searching for abbreviation that are at least a little bit meaningfull is a nice thing. But before starting to cross the 30 byte border you have to deeply inspect your code.

ORACLE 12.2 – deprecated pragma for PL/SQL units

02 Sunday Oct 2016

Posted by troroblog in ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

Tags

Compiler Error, Compiler Warning, ORACLE 12c Release 2, PL/SQL, PRAGMA

With ORACLE 12.2 it is possible to mark program units as deprecated along with a message using a new pragma. The message will show up when the deprecated program unit is referenced and the referencing program unit is compiled (and compiler warnings are enabled).

This enables us to gently replace code by advising the user to move to an alternative program unit.

 


EXAMPLE: Standalond procedure

ALTER SESSION SET PLSQL_WARNINGS = 'Enable:All'
/

CREATE OR REPLACE PROCEDURE p IS
   PRAGMA deprecate(p,'p is deprecated please use p2 instead.');
BEGIN
   sys.dbms_output.put_line('p');
END p;
/

Warning(2,4): PLW-06019: entity P is deprecated

When compiling a deprecated program unit we get an PLW-06019 warning.


EXAMPLE: Packaged procedure

CREATE OR REPLACE PACKAGE pkg IS
   PROCEDURE p;
     PRAGMA deprecate(p,'pkg.p is deprecated please use pkg.p2 instead.');
 
   PROCEDURE p2;
END;
/

Warning(2,4): PLW-06019: entity P is deprecated

CREATE OR REPLACE PACKAGE BODY pkg IS
   PROCEDURE p IS
   BEGIN
      sys.dbms_output.put_line('pkg.p');
   END p;

   PROCEDURE p2 IS
   BEGIN
      sys.dbms_output.put_line('pkg.p2');
   END p2;
END pkg;
/

The pragma specification for packaged program units has to be placed in the specification.

 


Referencing a deprecated program unit

So, what happens if you are using pkg.p or p?

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Warning(3,4): PLW-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Warning(6,8): PLW-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

A different warning (PLW-06020 instead of PLW-06019) shows up including the message we placed in the pragma.


Forcing PLW-06020 to be an error

If we would like to force the user of pkg.p/p to move to pkg.p2/p2 we could now easily defined PLW-06020 to be a compiler error.

ALTER SESSION SET PLSQL_WARNINGS = 'ERROR:6020'
/

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Error(8,4): PLS-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Error(11,8): PLS-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

So the q procedure does not compile anymore.


Conclusion

The deprecated pragma is a good way to let others know that a program unit should not be used anymore…if:

  • compiler warnings are enable
  • compiler warnings are looked at

The possibility to turn a warning into an error is also helpful to force the replacement of deprecated program units (in dev environment).

The problem I see is, that not all deprecated program units have the same expiry date and therefor setting a warning type to error for this case is somehow not sufficient. What I really would like to have is a third (optional) parameter in the pragma specification where I could specify by which date THIS deprecation turns into an error.

← Older posts

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.