Tag Archives: 12c

SQL*Plus Enhancements

12c, SQL,

PDB Support

SQL*Plus includes support for the multitenant architecture.

The SHOW command displays information about PDBs.

SQL> SHOW CON_ID

CON_ID
------------------------------
1
SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL> SHOW PDBS

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO

Command History

Before we can use the HISTORY command we have to make sure it is turned on. In the example below we attempt to use the HISTORY command with no options and we are told the HISTORY command is not enabled, so we enable it as instructed. Continue reading SQL*Plus Enhancements →

Mark data deleted or In-Database Archiving

12c, SQL,

Rather than deleting data, some applications have a concept of “mark for delete”, so the data remains present in the table, but is not visible to the application. This is usually achieved by doing the following.

  • Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
  • Add an extra predicate to every statement that checks the deleted status, like “WHERE deleted = 'N'“, to exclude the deleted rows from the SQL and then create a view on that table to display only “active” records.

In-Database Archiving is a feature added to Oracle Database 12c to allow this type of “mark for delete” functionality out-of-the-box, with fewer changes to the existing application code.

Continue reading Mark data deleted or In-Database Archiving →

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 →

12c: PLUG IN 12c NON-CDB AS PDB

12c,

In this post, I will demonstrate the conversion of a 12.1.0.1 non-CDB  to a PDB. We will plug in  12.1.0.1 non-cdb named ncdb12c into a CDB called CDB2

Current scenario:

Source  = 12.1.0.1 non-cdb  : ncdb12c
Target CDB                               : CDB2
Target PDB                              : ncdb12c

To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB. The conversion process is similar to unplug and plug of a pdb.

Overview:

– Log into ncdb12c as sys
– Get the database in a consistent state by shutting it down cleanly.
– Open the database in read only mode
– Run DBMS_PDB.DESCRIBE to create an XML file  describing the database.
– Shut down ncdb12c
– Connect to target CDB (CDB2)
– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)
– Plug-in Non-CDB (NCDB12c) as PDB(NCDB12c) into target CDB(CDB2).
– Access the PDB and run the noncdb_to_pdb.sql script.
– Open the new PDB in read/write mode.

Implementation:

Continue reading 12c: PLUG IN 12c NON-CDB AS PDB →

ACCESS EM EXPRESS FOR CDB / PDB / Non-CDB

12c, OEM,

Let’s see how to access EM express for various types of databases.

Oracle 12c has introduced multitenant architecture in which we can have different types of databases:

– CDB’s
– PDB’s
– Non CDB’s

Let’s see how to access EM express for various types of databases:

Accessing EM express for CDB’s

At the root container, data is displayed for the entire CDB, and all actions taken impact all containers.  Data pertaining to a PDB, such as a user or a SQL statement, is qualified with the container name.  The actions a user can perform at the root container include:

– viewing and setting initialization parameters;
– administering common users and privileges; and
– managing memory configuration, undo information and redo logs.

– Find out the port on which EM Express is configured:

Three methods are available:

Continue reading ACCESS EM EXPRESS FOR CDB / PDB / Non-CDB →

Playing with Oracle 12c and Virtual Box

12c, Installation, Tips, ,

I keep “playing'” with 12c, and this time I’d like to show you how easy is to install the Pre-Built VMs provided by Oracle.

As I mentioned, the process is very simple, these are the requirements to install it:

  • At least 2GB RAM. Default VM is 1G RAM, for better performance increase.
  • At least 15GB of free space (Note: virtualization works best with contiguous space so it is a good idea if on Windows to run a defrag program, and make sure you are using NTFS for your file system to handle large files on Windows. )
  • 2GHz Processor (a lesser processor will be acceptable but slower)
  • Mozilla Firefox 2.0 or higher, Internet Explorer 7 or higher, Safari 3.0 and higher or Google Chrome 1.0 or higher
  • Adobe Acrobat reader
  • Admin privileges on your box

So now that you know the minimum requirements, let’s move to the installation process:

  1. Download and install Oracle VM VirtualBox on your host system from HERE.
  2. Download the Virtual Machine from HERE. (DownloadOTN_Developer_Day_VM.ova (5,215,947,264 bytes, md5sum: 4e8ef75cdbd0fbe3d3beee8f873b2d4a)
  3. Open Oracle VM VirtualBox and Import your VM: File > Import Appliance to launch Appliance Import Wizard.
    Click Choose… to browse to the directory you re-assembled all the files in and select the OTN_Developer_Day_VM.ova.  Then click Next to begin importing the virtual machine. It will prompt you to agree to the appropriate developer licenses while importing. You will see ‘Oracle Developer Days (Powered Off)’ when it is finished importing.
  4. Test your VM: Once the import has completed, double-click the OTN Developer Days VM. Click OK to close the Virtualbox Information dialogs. When you get to the Enterprise Linux 6 screen you can now login. (Username and password is oracle.) Allow the process to complete; it is ready when you see a terminal window, which you can close.
  5. Once you are finished working in the guest VM you can shut it down via System > Shut Down; this will return the guest VM to the Powered Off state.

As you can see, the process is pretty simple right?

Have a nice day!!

12c, First Steps

12c, Installation,

I was finally able to start with 12C!! So once I finish the installation, I tried to connect to the DB and create a user. That is when I discover that you need to connect to a special Database in order to perform that.

I got an error message creating a user.  Well, I figured out how to create a new user and a few other things.  I’m working with the DB12C database that comes with the install and all the parameters, etc. that come with it.

Evidently the default install comes with a PDB called PDBORCL.  So, I have two tns entries one for the parent CBD and one for the child PDB and they look like this:

DB12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)
(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB12C)
    )
  )

PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)
(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

So, if I connect as SYSTEM/password@DB12C I’m connected to the CDB and if I connect to SYSTEM/password@pdb I’m connected to the PDB.  When I connected to the PDB I could create a new user without getting an error.

But, when I first tried connecting to the PDB I got this error, even though the database was up:

Continue reading 12c, First Steps →

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 →