DBA Blogs

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;

Pluggable database altered.


SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ WRITE

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN

***********************************************
DISCARD PDB SAVED STATE
***********************************************

SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20

SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>

CON_NAME     INSTANCE_NAME  STATE
-------------------- -------------------- --------------
PDBORCL1     orcl  OPEN


SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;

Pluggable database altered.

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected

select * from empinfo; 

"ID"    "FNAME"    "LNAME"    "DEPTNO"
1    "arv"    "red"    100
2    "sam"    "kum"    200
3    "tom"    "com"    100
4    "Hef"    "Kom"    200
5    "fam"    "ily"    300



select * from deptinfo;

"ID"    "DEPTNO"    "DEPTNAME"    "MGR"
1    100    "HR"    "Bill"
2    200    "IT"    "Greg"
3    300    "SALES"    "Phil"


-- sqlcode starts here

declare

CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;

CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;

OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('REC1.id:  '||REC1.ID||'  belongs to dept ID : '||REC2.DEPTNO||'  DEPT :'||REC2.DEPTNAME);

END LOOP;
CLOSE CUR2;

end loop;
close CUR1;
end;

Oracle 11g supports specifying a Virtual Column as the Partition Key.

A Virtual Column is a column where the value is derived on the basis of an expression on other columns or sql/plsql functions.  The actual value is not stored in the block holding the row but is computed when the row is retrieved.

For example :

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_id, invoice_date, sale_year
 from my_sales_table
/

INVOICE_ID  INVOICE_DATE  SALE_YEAR
         1  28-NOV-18          2018

The MY_SALES_TABLE can be List, Range or Hash Partitioned on SALE_YEAR.

drop table my_sales_table;

create table my_sales_table
(invoice_id  number primary key,
 invoice_date date,
 sale_year number(4) generated always as ( to_number(to_char(invoice_date,'YYYY')) ) virtual,
 customer_id number,
 sale_value number
 )
 partition by list(sale_year)
 (partition p_2018 values (2018),
  partition p_2019 values (2019),
  partition p_2020 values (2020)
  )
/

insert into my_sales_table
 (invoice_id, invoice_date, customer_id, sale_value)
 values
 (1,sysdate,100,10200)
/

select invoice_date, sale_year from my_sales_table partition (p_2018)
/

INVOICE_DATE  SALE_YEAR
28-NOV-18          2018

Thus, the SALE_YEAR value is not actually stored on disk, yet each partition has rows based on the SALE_YEAR (derived) value.