UTL_CALL_STACK Oracle Database 12C release 1 (12.1)

New package UTL_CALL_STACK is added in Oracle 12C. The package gives more control for format display of call stack, error stack and error backtrace.

CALL STACK – it’s showing call stack from where it was started to where the call stack was examined. In previous release DBMS_UTILITY.FORMAT_CALL_STACK could be used to display it in constant format.

ERROR STACK – it shows full oracle error chain.Β  In previous release DBMS_UTILITY.FORMAT_ERROR_STACK could be used to display it in constant format.

ERROR BACKTRACE – The backtrace is a trace from where the exception was thrown to where the backtrace was examined. In previous release DBMS_UTILITY.FORMAT_ERROR_BACKTRACE could be used to display it in constant format.

Continue reading

Install and configure Apex 5.0.X embedded PL/SQL

This article presents how to install and configure Apex for version 5.0.X for Oracle 11G

Prepare software to installation

Download installation package from Oracle site and unzip.

Download apex_5.0.X.zip to directory /tmp and unzip it from
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

cd /tmp
unzip <downloaded software>

After unzip is completed a new directory will be created /tmp/apex so go to this directory and log into database as SYSDBA. Always use SYSDBA account for running all scripts.

cd /tmp/apex
sqlplus / as sysdba

Pre-installation steps

It’s recommended to do backup of the database and disable the Oracle XMLDB HTTP server by setting the HTTP port to 0.

EXEC DBMS_XDB.SETHTTPPORT(0);

Continue reading

Install Oracle in silent mode 12C Release 1 (12.1) on OEL6

This article presents how to install Oracle 12C Release 1 in silent mode.

Silent mode installation allows to configure necessary Oracle components without using graphical interface nor any interaction with end user. It’s very useful method especially when you want to prepare standard installation using shell scripts.

Read following article to install OEL6 Linux: Install Oracle Linux 6 64 bit(for comfort set 4G memory for your virtual machine). During OEL6 installation I drop user oracle and both group dba and oinstall.

Software

Software for 12CR1 is available on OTN or edelivery

Database software

linuxamd64_12102_database_1of2.zip 
linuxamd64_12102_database_2of2.zip

Requirements

Be sure you fulfil following:

  • Oracle Linux 6 with the Unbreakable Enterprise kernel: 2.6.39-200.24.1.el6uek.x86_64 or later
  • Oracle Linux 6 with the Red Hat Compatible kernel: 2.6.32-71.el6.x86_64 or later

Continue reading

Define SecureFile LOBs during import Oracle Database 12C release 1 (12.1)

In Oracle 12C it’s possible to define LOB storage method during import time independent from export settings.

It gives more flexibility during importing data.

impdp .. TRANSFORM=LOB_STORAGE:SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE
  • DEFAULT – no lob storage clause is set for CREATE TABLE
  • NO_CHANGE – use settings from dump file
  • BASICFILE – creates LOBs as basicfile
  • SECUREFILE – creates LOBs as securefile
  • Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM

Example

Imports data and sets LOBs as SECUREFILE

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
TRANSFORM=LOB_STORAGE:SECUREFILE

Have a fun πŸ™‚

Tomasz

Compress tables during import Oracle Database 12C release 1 (12.1)

In Oracle 12C it’s possible to specify during import compressions settings for a table independent from export settings.

It gives more flexibility during importing data.

impdp .. 
TRANSFORM=TABLE_COMPRESSION_CLAUSE:NONE|"<compression type>"
  • NONE – compression ignored during import, taken from tablespace settings
  • <compression type> – valid compression clause:
    • NOCOMPRESS : Disables table compression.
    • COMPRESS : Enables basic table compression.
    • ROW STORE COMPRESS: Same as COMPRESS.
    • ROW STORE COMPRESS BASIC : Same as COMPRESS.
    • ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as OLTP compression.
    • COLUMN STORE COMPRESS FOR {QUERY|ARCHIVE} : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.
  • Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM

Example

Import data with dedicated compression type

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp
TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS"

Have a fun πŸ™‚
Tomasz

ENCRYPTION_PWD_PROMPT=Y Oracle Database 12C release 1 (12.1)

There is new option to pass in silent mode encryption password in Oracle 12C during exporting sensitive data. In new version user is asked about password during execution of export.

expdp ... ENCRYPTION_PWD_PROMPT=Y ...
...
Password: <user is asked for encryption password>

In previous version encryption password was explicit passed to expdp using parameter ENCRYPTION_PASSWORD. So could be visible via ps command or in a expdp parameter script.

Features:

  • new parameter ENCRPTION_PWD_PROMPT as default set to N
  • if the parameter is set to Y then user is asked for encryption password during export
  • the password is not visible via commands like PS nor stored in scripts
  • old 11g ENCRYPTION_PASSWORD and ENCRYPTION_PWD_PROMPT=Y can’t be used in the same time and generate an error

Have a fun πŸ™‚
Tomasz