Server Data Migration

Multitenant database in Oracle 12c

By | Blog, Oracle Tips, Technology Tips & Tricks
One of the new features of Oracle 12c is multitenant databases. Multiple databases can be plugged in a big container database. Container database (CDB) is the primary database that comprises 0, 1 or many pluggable databases (PDB). This holds the controlfiles, datafiles, undo, tempfiles, redo logs and also the data dictionary for those objects that are owned by the root container. It is also called CDB$ROOT Pluggable Database (PDB) – these are the databases stored within the primary container database. Each PDB is a set of schemas, objects and non-schema objects. PDB is made up of only datafiles and tempfiles to handle its own objects which includes its own data dictionary containing information about only those objects specific to the PDB. PDBs appear to the end users as a non-CDB database. Seed PDB – A default PDB that the system uses as a template to provision other user-created PDBs. It is called PDB$SEED   Advantages of Multitenant database architecture Multitenant architecture reduces the requirement to create multiple instances. It offers the ability to have 252 PDBS per multitenant CDB. This increases hardware utilization and so reduce capital expenses. Upgrade and patching is made very simple. All underlying databases inside it (more…)
Data Migration Support

Invisible Columns in Oracle 12c

By | Oracle Tips, Technology Tips & Tricks
Invisible column is a new feature in Oracle 12c.  Invisible columns provide a convenient way to change a table’s structure without having to edit the existing applications using that table.   Invisible columns can be created at the time of table creation using CREATE TABLE command                                 SQL> create table inviscol_test                                 2  (compno number,                                 3  compname varchar(20),                                 4  compcode varchar(10) invisible);                                 Table created.   existing visible columns can be modified to invisible using ALTER TABLE command                                 SQL> alter table inviscol_test modify (compname invisible);                                 Table altered.   How does it work?   The insert statement where we don’t specify the column names will fail.                                 SQL> insert into inviscol_test values (101,'ABCD','A101');                                 insert into inviscol_test values (101,'ABCD','A101')                                 *                                 ERROR at line 1:                                 ORA-00913: too many values                                 Explicit column names should be included in the insert statement.                                 SQL>  insert into inviscol_test(compno, compname, compcode) values                                 (101,'ABCD','A101');                                 1 row created.   If invisible columns are not listed in the insert statement they are set to NULL                                 SQL>  insert into inviscol_test values (201);                                 (more…)
Oracle

Oracle 12c New Feature – Multiple Indexes On The Same Column Or Set of Columns

By | Oracle Tips, Technology Tips & Tricks | No Comments
Starting with Oracle 12c, multiple indexes on same column or same set of columns can be created, as long as only one index is visible, and the indexes are physically different. It is not possible to create two bitmap indexes on the same column or set of columns. Example: Create a table ind_test1 SQL> create table ind_test1(id number, dept  varchar(20), start_dt date); Table created. Insert rows SQL>  insert into ind_test1 values(111, 'TEST', to_date('01/01/2000','mm/dd/yyyy')); 1 row created. SQL>  insert into ind_test1 values(222, 'DEVL',  to_date('10/25/2011','mm/dd/yyyy')); 1 row created. SQL>  insert into ind_test1 values(333, 'TEST',to_date('05/22/1999','mm/dd/yyyy')); 1 row created. Select * from ind_test1 table SQL> select * from ind_test1; ID DEPT                 START_DT ---------- -------------------- --------- 111 TEST                 01-JAN-00 222 DEVL                 25-OCT-11 333 TEST                 22-MAY-99     Create the first index on start_dt column. By default, Oracle creates B-tree index. SQL>  create index idx1_ind_test1 on ind_test1(start_dt); Index created.   Now, create a second invisible index on the same column SQL> create index idx2_ind_test1 on ind_test1(start_dt) invisible; create index idx2_ind_test1 on ind_test1(start_dt) invisible * ERROR at line 1: ORA-01408: such column list already indexed   Index failed because these 2 are the same type of indexes. You can create a bitmap index on the same column. SQL>  (more…)