This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database. Either we can set it to STANDARD or.EXTENDED The default value is STANDARD MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2 MAX_STRING_SIZE = EXTENDED means maximum size can be […]

 Row limiting clause clause allows sql queries to limit the number of rows returned and to specify a starting row for the return set. 1. Fetch first N rows: SQL> select * from test2 fetch first 5 rows only; OWNER OBJECT_NAME STATUS ——– ——————— ——- SYS I_CCOL1 VALID SYS I_PROXY_ROLE_DATA$_1 VALID SYS C_OBJ# VALID SYS […]

Introduction: Oracle in-memory concept has been introduced in oracle 12c. This feature enables tables, partitions, materialized views be stored in memory using column format, which delivers fast SQL processing for the Analytical purpose. To understand Database In-Memory feature and its benefits we first need to understand the unique “dual format” architecture that enables Oracle Database […]

If you wish to setup listener for a particular PDB in the multitenant database, then follow as below. Create a listener and start it. LISTENER_DBATEST = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbatest-host)(PORT = 1573)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1573)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_PDB_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_PDB_LISTENER=SUBNET […]

With oracle 12c Multitenant architecture, New terminologies for users has been introduced. LOCAL USER and COMMON USER. SEE ALSO: Oracle 12c Multitenant Architecture COMMON USER: 1.A common user is a DB user, which work perform an activity in all the containers including root container of the CDB. 2.A common user can only be created in […]

There are two undo modes in oracle 12.2 Multitenant database 1. Local undo mode 2. Shared undo mode Local undo mode: In this mode, each container ( i.e PDB ) in multitenant will have their own active undo tablespace. Share Undo mode: In this mode, There will be only one undo tablespace for the instance. […]

In Oracle 12.2, We can split partitions or subpartitions online without impacting the DML statements. SQL> select partition_name,read_only,high_value from dba_tab_partitions where table_name=’ORDER_TAB’; PARTITION_NAME READ HIGH_VALUE ——————————– —- ——————————————————————————– CREATED_2105_P10 NO TO_DATE(‘ 2015-11-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA CREATED_2105_P11 NO TO_DATE(‘ 2015-12-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA CREATED_2105_P12 NO TO_DATE(‘ 2016-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA CREATED_2105_P8 NO TO_DATE(‘ […]

Till now we can make a table read only, But what about setting few partitions of a table to read-only. Thanks to Oracle 12.2 Release, We can do this. This is usually helpful, If requirement is to make the historical data not editable. SQL> col PARTITION_NAME for a32 SQL> select partition_name,read_only from dba_tab_partitions where table_name=’ORDER_TAB’; PARTITION_NAME […]

In previous Releases, To move a table to a different tablespace or segment, we need to take downtime for this activity And after moving the table, we have to rebuild/recreate the indexes. Now with Oracle 12.2, We can move the table online to a different segment or tablespace, without impacting the DML activities and without […]

You may use SQL*Plus to test queries with bind variables. Here is what you do before 12.2: SQL> variable text char SQL> exec :text:=’X’ PL/SQL procedure successfully completed. SQL> select * from DUAL where DUMMY=:text; D – X Now in 12.2 is so simple SQL> variable text char=’X’ SQL> select * from DUAL where DUMMY=:text; […]