How to Set Up ORMB Batch Scheduling with Oracle DBMS Scheduler

Download and Apply Oracle DBMS Scheduler Patch

$ unzip Oracle_Scheduler_Integration.zip
Archive: Oracle_Scheduler_Integration.zip
 inflating: Getting Started Guide ORMB Batch Scheduling.pdf
 inflating: ORACLE_DBMS_SCHEDULER_SETUP_LINUX.pdf
 inflating: ORMB_Integration_Installer/lastDayPrevMonthJobs.properties
 inflating: ORMB_Integration_Installer/readme.txt
 inflating: ORMB_Integration_Installer/setup.sh
 inflating: ORMB_Integration_Installer/sql/oem_ouaf_package.sql
 inflating: ORMB_Integration_Installer/sql/ouaf_batch_package.sql
 inflating: ORMB_Integration_Installer/sql/setup.sql
 inflating: ORMB_Integration_Installer/sql/user_cisadm_setup.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_NEW_I.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_NEW_S.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_NEW.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_OEM_N.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_OEM_Y.sql
 inflating: ORMB_Integration_Installer/sql/user_schadm_setup_UPD.sql
 extracting: ORMB_Integration_Installer/sql/user_schadm_UPD_I.sql
 extracting: ORMB_Integration_Installer/sql/user_schadm_UPD_S.sql

$ cd ORMB_Integration_Installer
oracle@racnode1:/tmp/ORMB_Integration_Installer$ chmod 777 setup.sh
oracle@racnode1:/tmp/ORMB_Integration_Installer$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 16 10:23:42 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> alter session set container=ormbpdb;

Session altered.

SQL> show user
USER is "SYS"
SQL> @?/rdbms/admin/userlock.sql;

Package dropped.
Package created.
Package body created.
Synonym created.
Grant succeeded.
SQL> grant execute,debug on user_lock to cisadm;
Grant succeeded.

oracle@racnode1:/tmp/ORMB_Integration_Installer$ ls -ltr
total 16
-rwxrwxrwx 1 oracle oinstall 417 Mar 14 16:10 setup.sh
-rw-r--r-- 1 oracle oinstall 3485 Mar 14 16:10 readme.txt
-rw-r--r-- 1 oracle oinstall 112 Mar 14 16:10 lastDayPrevMonthJobs.properties
drwxr-xr-x 2 oracle oinstall 4096 Mar 16 10:20 sql

oracle@racnode1:/tmp/ORMB_Integration_Installer$ ./setup.sh

OUAF DBMS Scheduler Setup (4.2.x, 4.3.x)
========================================

NOTES:
1. Package USER_LOCK must be installed before running this script.
That can typically be done by executing @?/rdbms/admin/userlock.sql
2. The schema owner (e.g. CISADM) must have "create user" permission.

Hit enter to start
Enter database service (SID) name: ORMB
Enter schema owner [CISADM]: cisadm
Enter password for user cisadm: xxxxxx
Connecting to cisadm@ORMB ...
Validating user cisadm
Checking USER_LOCK package
Enter scheduler user name [SCHADM]: schadm
User SCHADM does not exist and will be created
Enter password for user SCHADM:
Confirm password for user SCHADM: xxxxxx
Enter default tablespace [CISTS_01]: CISADM
Enter temp tablespace [TEMP]:
Creating user SCHADM
Connecting to SCHADM@ORMB ...

The email server, recipient and sender addresses are required for email
notifications when threads end in error and the thread_notifications
option is in effect. These values can be set here or the defaults
can be accepted and then modified by connecting to user SCHADM and:
a) running statement "DBMS_SCHEDULER.set_scheduler_attribute(...)" to
change the email server and
b) editing scheduler job OUAF_NOTIFY and changing its email notification
settings.

Enter email server name [mailserver.company.com]: smtp.wordpress.com
Enter email recipient address [foo.bar@company.com]: james.huang@wordpress.com
Enter email sender address [do-not-reply@company.com]: schadmWwordpress.com
Use OEM_NOTIFY for thread errors (Y/N)? [N]: Y
Job OEM_NOTIFY will be used for thread error notifications

OUAF DBMS Scheduler setup completed

Schedule Jobs With Scheduler Using Sql Developer

We will follow below steps to achieve this :

  • Create Program ( A program is to be run by schedule which will be ORMB batch job )
  • Create Chain (Steps and Rule, Including BEGIN and END rules)
  • Create Schedule (Schedule when and how many times job is execute)
  • Create Job (A job is collection of metadata which defines program to execute, where to execute as well as its related schedule)
  • Run Job (Run Job to trigger chain which will trigger set of programs<Batch Jobs> with given sequence and rules)
  • Monitoring a job

Create a Program

  1. Right Click on Programs and Select New Program

2) Put all required fields and click on ‘Apply” button to complete the creation.

Or run below sql to create the above program.

BEGIN
 DBMS_SCHEDULER.create_program(
 program_name => 'SCHADM.C1_TXNRP',
 program_action => 'BEGIN OUAF_BATCH.Submit_Job(batch_code => ''C1-TXNRP'', user_id => ''SYSUSER'');END;',
 program_type => 'PLSQL_BLOCK',
 number_of_arguments => 0,
 comments => 'C1-TXNRP',
 enabled => FALSE);

DBMS_SCHEDULER.ENABLE(name=>'SCHADM.C1_TXNRP'); 
END;

Create a Chain

1)Right Click Chain, and select New Chain.

2) Provide the name and description and click on Apply Button to create chain.

or run the below sql to create the chain:

BEGIN
 DBMS_SCHEDULER.create_chain(
 comments => 'SERVICECHGJOBS CHAIN',
 chain_name => 'SCHADM.SERVICECHGJOBS'
 );
 DBMS_SCHEDULER.enable(name=>'SCHADM.SERVICECHGJOBS');
END;

Create a Step

1)Click on created Chain and select Add Step Button

2) Fill all the required fields as shown below (Here select you created program in first step) and click on Apply. This will create the Step.

or run below sqls to create the step the same :

BEGIN
 DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 PROGRAM_NAME => '"SCHADM"."CMRVRSCH"' ); 
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'PAUSE',
 VALUE => false);
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'SKIP',
 VALUE => false);
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'RESTART_ON_FAILURE',
 VALUE => false);
 DBMS_SCHEDULER.ALTER_CHAIN(
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 STEP_NAME => '"CMRVRSCH_JOB1"',
 ATTRIBUTE => 'RESTART_ON_RECOVERY',
 VALUE => false);

END;

Create a Rule

1)Switch to Write mode.
2)Right click on any where still in workspace and select Add Rule .

below is the sql which can alternatively create this rule the same.

BEGIN
 DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
 CHAIN_NAME => '"SCHADM"."SERVICECHGJOBS"',
 comments => 'CMRVRSCH_RULE', 
 rule_name => '"CMRVRSCH_RULE"', 
 condition => 'TRUE',
 action => 'START "CMRVRSCH_JOB1"'
 ); 
END;

3) Repeat the same procedure to create all the rest rules:

4) Finally create the END RULE by right clicking the last step -“CM_PSEXT_JOB17” , and then select “Add End Rule”.

5) The final Chain view will be as below:

Create  a  Schedule

1)Right click on Schedule and select New Schedule

2) Choose “REPEATING”, and then click “Repeat Interval” to setup Daily running at 2:00AM.

3)Finally click on Apply will create Schedule.

Create a Job

1)Right click on Job and Select New Job.
2) Fill all required fields as shown below (Select your created chain and schedule respectively).
3) Enable all (job ,schedule, chain, program).

Run a Job

To manually run job execute below command.

BEGIN
DBMS_SCHEDULER.run_job(job_name => 'SERVICECHG_JOB',
use_current_session => false);
END;

Monitor a Job

To monitor a job execute below SQL statement on DB.

 select * from all_scheduler_running_jobs;
 select job_name, chain_name, step_name, state, error_code, completed from all_scheduler_running_chains where job_name = 'SERVICECHG_JOB';

Rerun The Failed Step In a Chain

SQL>exec dbms_scheduler.alter_running_chain('SERVICECHG_JOB', 'C1_TXNRB_JOB2', 'STATE', 'NOT_STARTED');
Posted in dbms_scheduler, ORMB | Tagged , , , , , , , , , , , , , , , | Leave a comment

How to Kill Oracle RAC Sessions

We know the syntax for killing a session is :

SQL>alter system kill session 'SID, SERIAL#' immediate;

In RAC environment, the syntax will be :

SQL>alter system kill session 'SID, SERIAL#,@INST_ID' immediate;

Kill all the sessions from username ‘JAMESH’:

SQL> select 'alter system kill session '||''''||sid||','||serial#||','||'@'||INST_ID||''''||' immediate;' from gv$session where USERNAME='JAMESH' order by INST_ID;

'ALTERSYSTEMKILLSESSION'||''''||SID||','||SERIAL#||','||'@'||INST_ID||''''||'IMMEDIATE;'
----------------------------------------------------------------------------------------------
alter system kill session '3839,1429,@1' immediate;
alter system kill session '3269,62522,@1' immediate;
alter system kill session '2135,2483,@1' immediate;
alter system kill session '1994,10353,@1' immediate;
alter system kill session '1144,30683,@1' immediate;
alter system kill session '1003,59576,@1' immediate;
alter system kill session '4,33789,@1' immediate;
alter system kill session '4268,11193,@1' immediate;
alter system kill session '2846,21471,@2' immediate;
alter system kill session '2987,43213,@2' immediate;
alter system kill session '3983,62058,@2' immediate;
alter system kill session '4122,56820,@2' immediate;
alter system kill session '4125,54104,@2' immediate;
alter system kill session '4263,55362,@2' immediate;
alter system kill session '4268,43052,@2' immediate;
alter system kill session '2704,41337,@2' immediate;
alter system kill session '2564,24435,@3' immediate;
alter system kill session '1851,17069,@3' immediate;
alter system kill session '857,25207,@3' immediate;
alter system kill session '4408,37890,@3' immediate;
alter system kill session '576,38233,@4' immediate;
alter system kill session '432,5600,@4' immediate;
alter system kill session '6,29398,@4' immediate;
alter system kill session '719,42236,@4' immediate;

24 rows selected.

Check sessions are killed :

SQL> select username, machine,count(*) from gv$session where USERNAME = 'JAMESH' group by username, machine;

no rows selected
Posted in SQL Scripts For DBA | Tagged , , , , , , , | Leave a comment

“TNS-12508: TNS:listener could not resolve the COMMAND given” when “set log_status off” for a Listener

Got “TNS-12508: TNS:listener could not resolve the COMMAND given” when trying to switch off listener log.

LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1521)))
TNS-12508: TNS:listener could not resolve the COMMAND given
LSNRCTL>

This is due to parameter “ADMIN_RESTRICTIONS_LISTENER = ON” in listener.ora.  Remove this parameter from listener.ora, or set this parameter to OFF, then reload the listener.  Then it should be fine to set log_status on/off.

Setting “ADMIN_RESTRICTIONS_listener_name=on” disables the runtime
modification of parameters in listener.ora. That is, the listener will refuse to
accept SET commands that alter its parameters.

Posted in SQL Net | Tagged , , , , , , | Leave a comment

ORA-28002 : the password will expire within 3 days

The password is unknown, and it is encrypted in application configurations. User doesn’t want to change it at the moment.

$ oerr ora 28002
28002, 00000, "the password will expire within %s days"
// *Cause: The user's account is about to expire and the password
// needs to be changed
// *Action: change the password or contact the DBA
//

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where username='JAMESH';

USERNAME         PASSWORD             ACCOUNT_STATUS    EXPIRY_DATE
---------------- -------------------- ----------------  ---------
JAMESH                                EXPIRED(GRACE)    18-MAR-17

Get hash password value from USER$

SQL> select password from user$ where name='JAMESH';

PASSWORD
--------------------
512795B1F6AC27B8

Reset the password with the original one:

SQL> alter user jamesh identified by values '512795B1F6AC27B8';

User altered.
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where USERNAME='JAMESH';

USERNAME         ACCOUNT_STATUS    EXPIRY_DATE
---------------- ---------------   -----------
JAMESH           OPEN              14-MAY-17
Posted in User Privileges and Roles | Tagged , , | Leave a comment

“SELECT MEDIA FROM V$BACKUP_PIECE_DETAILS” Query Takes Long Time and Uses Huge Temp Space after Upgrading to OEM 13.2

The below query from OEM agent keeps failing, after upgrading Enterprise Manager Cloud Control and a monitoring agent to 13.2, this query is run in the 11.2.0.4 target database:

SELECT MEDIA
 FROM V$BACKUP_PIECE_DETAILS
 WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1

Also it uses huge temp space :

SQL> select USERNAME,TABLESPACE,BLOCKS*8/1024/1024 from V$TEMPSEG_USAGE order by BLOCKS desc ;

USERNAME   TABLESPACE                       BLOCKS*8/1024/1024
---------- ------------------------------- ------------------
DBSNMP     TEMP                             30.649414

It is the exact same issue as per (Doc ID 2201982.1). The below actions are taken :

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Finally flush the cursor out of memory for next running :

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS      VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- --------------- ---------- ---------- ------------- ------------- -----------
00000001418FA048 4087355773 1896464546      213        5          1             1             213

SQL> exec dbms_shared_pool.purge('00000001418FA048,4087355773','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='dx4nqvbtu06bx';

no rows selected

Have a check of the new execution time. We can see the new execution plan is created, and the execution time is only 0.25 second.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions,ELAPSED_TIME , ELAPSED_TIME/executions/1000000 TimePerExecution from v$sqlarea where sql_id='dx4nqvbtu06bx';

ADDRESS            HASH_VALUE  PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME TIMEPEREXECUTION
----------------   ----------  --------------- ---------- ------------ ----------------
00000001418FA048   4087355773   75529090       2          504858       .252429
Posted in OEM, OEM13c, Performance Tunning, RMAN Restore and Recovery | Tagged , , , , , | Leave a comment

How To Flush an Object Out The Library Cache by Using DBMS_SHARED_POOL Package

We know how to flash all shared pool by :

SQL>alter system flush shared_pool;

Sometime, we only want to flush certain objects, like a cursor for testing purpose to want the cursor to have different execution plan.  DBMS_SHARED_POOL can do so.

dbms_shared_pool.purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);


name: There are two kinds of objects:   
   PL/SQL objects, triggers, sequences, types and Java objects which are specified by name. 
   SQL cursor objects which are specified by a two part number. The value for this identifier 
       is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view.  

  flag: This is an optional parameter. If the parameter is not specified,   
        the package assumes that the first parameter is the name of a   
        package/procedure/function and will resolve the name. Otherwise,   
        the parameter is a character string indicating what kind of object   
        to purge the name identifies. The string is case insensitive.   
        The possible values and the kinds of objects they indicate are   
        given in the following table:   

        Value Kind of Object to keep   
        ----- ----------------------   
            P package/procedure/function   
            Q sequence   
            R trigger   
            T type   
           JS java source   
           JC java class   
           JR java resource   
           JD java shared data   
            C cursor   

  heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.   
         1<<0 | 1<         Default is 1 i.e heap 0 which means the whole object will be purged.
SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='dx4nqvbtu06bx';

ADDRESS          HASH_VALUE PLAN_HASH_VALUE EXECUTIONS LOADS      VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- --------------- ---------- ---------- ------------- ------------- -----------
000000025E3EE2A0 4087355773 1896464546      22         2          2             1             22


SQL> exec dbms_shared_pool.purge ('000000025E3EE2A0,4087355773','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value,PLAN_HASH_VALUE, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='dx4nqvbtu06bx';

no rows selected
Posted in Performance Tunning, SQL Scripts For DBA | Tagged , , , , , | Leave a comment

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated

This warning will be seen in alert log if we issue the same command in 12c :

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Instead we can use below command to eliminate the WARNING:

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.
Posted in 12c new features, DataGuard | Tagged , , | Leave a comment

No standby redo logfiles available for T-1

One single instance 12c Oracle database alert log shows below information;

 RFS[1]: No standby redo logfiles available for T-1

DataGuard shows ORA-16857 error:

DGMGRL> show database verbose "TESTSTY";

Database - TESTSTY

 Role: PHYSICAL STANDBY
 Intended State: APPLY-OFF
 Transport Lag: 7 minutes 9 seconds (computed 50 seconds ago)
 Apply Lag: 7 minutes 9 seconds (computed 50 seconds ago)
 Average Apply Rate: (unknown)
 Active Apply Rate: (unknown)
 Maximum Apply Rate: (unknown)
 Real Time Query: OFF
 Instance(s):
 TESTSTY

Database Warning(s):
 ORA-16857: standby disconnected from redo source for longer than specified threshold

DGMGRL> validate database "TESTSTY"
...
..
.
Warning: standby redo logs not configured for thread 1 on TESTSTY

Checked both primary and standby database, the standby redo logs have been created. It is strange to see the thread id is different between primary and secondary database.

— on standby :

SQL>select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS,CON_ID from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024/1024 ARC STATUS     CON_ID
---------- ---------- -------------------- --- ---------- ----------
 4         0          1                    YES UNASSIGNED 0
 5         0          1                    YES UNASSIGNED 0
 6         0          1                    YES UNASSIGNED 0
 7         0          1                    YES UNASSIGNED 0

— On Primary

SQL>select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS,CON_ID from v$standby_log;

GROUP#     THREAD#    BYTES/1024/1024/1024 ARC STATUS     CON_ID
---------- ---------- -------------------- --- ---------- ----------
 4         1          1                    YES UNASSIGNED 0
 5         1          1                    YES UNASSIGNED 0
 6         1          1                    YES UNASSIGNED 0
 7         1          1                    YES UNASSIGNED 0

Drop all standby online redo logs on both primary and standby databases, and recreate then again by specify “thread 1” explicitly.

-- for standby db which is under recovery, recovery needs to be stopped first
SQL>alter database recover managed standby database cancel;

SQL>alter database add standby logfile thread 1 group 4 size 1024m;

It will create standby online redo logs for one under +FRA, and another one under from “db_create_file_dest” parameter, if “db_create_online_log_dest_x” are not defined.

Finally restart the recovery process, everything is fine.

SQL>alter database recover managed standby database disconnect nodelay;

Database altered.

Only see this thread issue for single instance database in 12c, which is a new feature or bug ?

Posted in 12c new features, CDB/PDB, DataGuard, ORA- ERRORS | Tagged , , , , , , | Leave a comment

ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Trying to drop a temporary tablespace which belongs to a default temporary tablespace group.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME          TABLESPACE_NAME
------------------- ------------------------------
TEMP_OLTP           TEMP
TEMP_OLTP           TEMP2
TEMP_OLTP           TEMP3

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group


SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Change default temporary tablespace from  temporary tablespace group TEMP_OLTP to a temporary tablespace TEMP.:

SQL> alter pluggable database default temporary tablespace temp;

Pluggable database altered.

Now drop temporary tablespaces:

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME                      TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_OLTP                        TEMPA

Make temporary tablespace group as default again:

SQL> alter pluggable database default temporary tablespace TEMP_OLTP;

Pluggable database altered.
Posted in ORA- ERRORS | Tagged , , | Leave a comment

Manage Temporary Tablespace in a 12cR1 Container Database (CDB) and Pluggable Database (PDB)

By default, both CDB and PDB have a TEMP tablespace:

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT

SQL> select CON_ID,NAME from v$tablespace where name='TEMP';

CON_ID      NAME
---------- -------------
 1         TEMP
 2         TEMP
 3         TEMP

Default Temporary Tablespace in PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP OLTP_TEMP;

Tablespace altered.

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE OLTP_TEMP;

Pluggable database altered.

Check the default Temporary Tablespace for PDB:

SQL> show con_id con_name

CON_ID
------------------------------
3

CON_NAME
------------------------------
PDB1

SQL> select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                            DESCRIPTION
---------------------------------------- ---------------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE                  OLTP_TEMP                                Name of default temporary tablespace

Check the default Temporary Tablespace for CDB as below, we can see CDB is still using its default temporary tablespace TEMP.

SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT

SQL> select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                           DESCRIPTION
---------------------------------------- ---------------------------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE                  TEMP                                     Name of default temporary tablespace
Posted in 12c new features, CDB/PDB | Tagged , , , , , | Leave a comment