Category: Oracle Database 12 c

ORACLE 12C : Extended Data Type

With the previous Oracle database release, long characters strings of more then 4000 bytes had to be handled using CLOB or LONG data types. Starting from Oracle 12c was introduced a new system parameter: MAX_STRING_SIZE. This parameter allows string data types to be much larger when the parameter is changed from its default value STANDARD to EXTENDED. New higher limits (in bytes) are available for following types: VARCHAR2 – 32767 bytes NVARCHAR – 32767 bytes RAW – 32767 bytes To use extended version you need to set new initialization parameter MAX_STRING_SIZE and run sql script utl32k.sql. Set the init.ora parameter...

ORACLE 12C: Expdp encryption password

Starting from release 12C there is a new option to pass in silent mode encryption password . The ENCRYPTION_PWD_PROMPT parameter enables encryption without requiring the password to be entered as a command line parameter. The user is prompted for the password during export utility. expdp … ENCRYPTION_PWD_PROMPT=Y Password: <user is asked for encryption password> In previous release encryption password was explicit passed to expdp utility using parameter ENCRYPTION_PASSWORD. So could be visible via operation system command “ps –ef …” . Features:  new parameter ENCRPTION_PWD_PROMPT as default set to N  if the parameter is set to Y then user...

ORACLE 12C : approx_count_distinct parameter

The APPROX_COUNT_DISTINCT function was added, but not documented, in Oracle 11g to improve the speed of calculating the number of distinct values when gathering statistics using the DBMS_STATS package. Oracle database 12c (12.1.0.2) now includes the function in the documentation, so we are free to use it in our applications as a supported SQL function. It returns approximate number of rows that contain distinct values of expression. It’s alternative to the COUNT (DISTINCT expr) function, which returns the exact number of rows that contain distinct values of expr. For processing large amounts of data it’s significantly faster than COUNT, with...

New Feature IDENTITY Columns

In Oracle 12c when we create a table we can populate a column automatically via a system generated sequence by using the GENERATED AS ++ clause in the CREATE TABLE statement. We can use GENERATED AS IDENTITY with the ALWAYS, DEFAULT or DEFAULT ON NULL keywords and that will affect the way or when the identity column value is populated. By default the GENERATED AS IDENTITY clause implicitly includes the ALWAYS keyword i.e GENERATED ALWAYS AS IDENTITY. When the ALWAYS keyword is specified it is not possible to explicitly include values for the identity column in INSERT OR UPDATE SQL...

ORACLE 12C : Advanced Index compression

Oracle 12c rel.1 (12.1.0.2) introduced a new method for index compression called “advanced index compression” which is more effective in index compression than available methods in Oracle 11g . Creating an index using advanced index compression reduces the size of unique and non-unique indexes. Advanced index compression improves the compression ratios significantly . Advanced Index Compression is not supported for bitmap indexes and for indexes on unique single-column indexes. For a partitioned index, you can specify the compression type on a partition . You can also specify advanced index compression on index partitions even when the parent index is not...

Oracle 12c: Move datafile feature

Until the release 11g move a datafile was an operation very problematic; We had to put the tablespace offline, copy the datafile to the new destination and rename it : SQL> alter database rename datafile “A” to “B”; From Oracle release Oracle 12c, you can move a datafile from a mount point to another without having to put the tablespace offline . All this is done while the database is active. So with the command: alter database     move datafile     ‘/u01/app/oracle/oradata/FILE.dbf’  to     ‘/u02/app/oracle/oradata/ORADB12/FILE.dbf’; It is also possible to move a datafile from specific number that Oracle has assigned...

ORACLE 12c – NAS new evolution

Oracle has announced the latest generation of Network-attached Storage or NAS system namely the Oracle ZFS Storage ZS4-4. ZFS Storage ZS4-4 doubles the performance over the previous version , allowing you to access the information they need to make strategic decisions for their business even more quickly . Oracle ZFS Storage ZS4-4 also provides a high-performance protection against breaches of security with detailed , simple and convenient encryption capabilities . SOME benefits: It reduces the volume of the storage, the complexity and cost; compresses data 10x to 50x and improves performance on average 5X Reduces manual setup and tuning of...

Oracle 12c : New Feature – Privilege Analysis

In a common situation present in many database we find that some users have been granted more privileges than what was needed and these privileges , over time , have remained . This could be dangerous to the security of the data and the database itself . In Oracle 12c we have a package called DBMS_PRIVLEGE_CAPTURE through which we are able to identify the object privileges and unnecessary system that have been granted and revoke privileges that have not been used . The analysis of the privileges can be done at the database level , on a single role or...

Oracle 12C: Full Database Caching

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2) When to use it: when you have enough memory to keep all segments in memory when you are limited by I/O system and response time Full database caching can be used to cache the entire database in memory. It should be used when the buffer cache size of the database instance is greater than the entire database size. In Oracle RAC systems this feature can be used when the combined buffer caches of all instances, with some extra space to handle duplicate cached blocks between instances, is greater...

Oracle 12c Rdbms: Desupported & Deprecated Features

Below is a list of some features desupported and deprecated since release Oracle Rdbms 12C  Single character SRVCTL command The Server Control Utility (SRVCTL) command line interface (CLI), used in earlier releases, starting with Oracle Database 12c is deprecated and may be desupported in a later release.  Catalog view The following views are deprecated in Oracle Database 12c: o ALL_SCHEDULER_CREDENTIALS view. o DBA_NETWORK_ACL_PRIVILEGES view. o DBA_NETWORK_ACLS view. o DBA_SCHEDULER_CREDENTIALS view. o USER_NETWORK_ACL_PRIVILEGES view. o USER_SCHEDULER_CREDENTIALS view. o V$OBJECT_USAGE view.  Stored Outlines They are still supported for backward compatibility. Oracle recommends that you use SQL plan management instead....