Tag Archives: new features

Data Pump enhancements

12c, DataPump, ,

There are quite a few useful additions, such as converting view into a table while exporting and turning off logging while import.

New Substitution Variables for File Names

Multiple files are generated by parallel exports, so each file needs to have a unique name. This is achieved using substitution variables. In previous releases the only substitution variable available was “%U”, which generated a two digit number from 01-99.
Oracle 12.2 includes additional substitution variables.

The following substitution variables are only available for export operations.

  • %d, %D : The current day of the month in DD format.
  • %m, %M : The current month in MM format.
  • %y, %Y : The current year in YYYY format.
  • %t, %T : The current date in YYYYMMDD format.

The following substitution variables are available for both export and import operations.

  • %U : Unchanged from previous releases. A two digit number incremented between 01-99.
  • %l, %L : This starts off similar to “%U”, producing a two digit number between 01-99, but it can extend up to 2147483646, so the resulting file name is not a fixed length.

The following example shows the usage of two of the new substitution variables in a parallel export. The output is edited to reduce its size.

$ expdp system@DB schemas=scott directory=TEST dumpfile=scott_%T_%L.dmp logfile=expdpscott.log parallel=4
...
....
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/scott_20180814_01.dmp
  /tmp/scott_20180814_02.dmp
  /tmp/scott_20180814_03.dmp
  /tmp/scott_20180814_04.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Wed Mar 22 16:05:41 2017 elapsed 0 00:01:07

Continue reading Data Pump enhancements →

Select Clause in 12c

12c,

Prior to Oracle12c you needed to use an inner query to get the 10 first rows of a table. Example:

Get the 10 first rows ordered by user_id in ascending order:

drop table DEMO1;

create table DEMO1 as select substr(username,1,20) username, user_id, from all_users;

select * from 
(select username, user_id from DEMO1 order by user_id asc)
where rownum <= 10;

USERNAME             USER_ID
-------------------- ----------
XS$NULL              2147483638
SYSKM                2147483619
SYSDG                2147483618
SYSBACKUP            2147483617
DVSYS                   1279990
DBFS_USER                   111
SCOTT                       109
BI                          108
SH                          107
IX                          106

 

Now with 12c, this is extremely simple!!!
Oracle 12c provides enhanced support for top-n analysis.

Continue reading Select Clause in 12c →