Search This Blog

Total Pageviews

Friday 21 April 2017

Pluggable database database add datafile ...

How to add datafile in  Pluggable database !!!


Pluggable database add datafile ..... 


set linesize 200 pagesize 200 
col FILE_NAME for a70
col PDB_NAME for a20
select a.CON_ID,PDB_NAME,b.status pdbstatus,TABLESPACE_NAME,FILE_NAME ,trunc(BYTES/1024/1024/1024,2) Gb, trunc(MAXBYTES/1024/1024/1024,2) Max from cdb_data_files a,dba_pdbs b
where b.PDB_ID=a.CON_ID
order by 1;

 CON_ID PDB_NAME PDBSTATUS TABLESPACE_NAME FILE_NAME GB MAX
---------- -------------------- --------- ------------------------------ ---------------------------------------------------------------------- ---------- ----------
 3 ORCL NORMAL SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf .39 31.99
 3 ORCL NORMAL APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf 0 .09
 3 ORCL NORMAL APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf 0 .02
 3 ORCL NORMAL SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf 1.19 31.99
 3 ORCL NORMAL EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf 1.21 31.99
 3 ORCL NORMAL USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf .14 31.99
 3 ORCL NORMAL APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf 0 .02
 4 ORDS NORMAL SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf .25 31.99
 4 ORDS NORMAL USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf .04 31.99
 4 ORDS NORMAL SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf .72 31.99

10 rows selected.

alter session set container=ORDS; 

SQL> show con_id

CON_ID
------------------------------
4

SQL> show con_name

CON_NAME
------------------------------
ORDS

or


SQL> show con_id con_name

CON_ID
------------------------------
4

CON_NAME
------------------------------
ORDS


SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf' size 1g AUTOEXTEND ON;

Tablespace altered.

set linesize 200 pagesize 200
col FILE_NAME for a70
col PDB_NAME for a20
select a.CON_ID,PDB_NAME,b.status pdbstatus,TABLESPACE_NAME,FILE_NAME ,trunc(BYTES/1024/1024/1024,2) Gb, trunc(MAXBYTES/1024/1024/1024,2) Max from cdb_data_files a,dba_pdbs b
where b.PDB_ID=a.CON_ID
order by 1;

 CON_ID PDB_NAME PDBSTATUS TABLESPACE_NAME FILE_NAME GB MAX
---------- -------------------- --------- ------------------------------ ---------------------------------------------------------------------- ---------- ----------
 4 ORDS NORMAL SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf .25 31.99
 4 ORDS NORMAL SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf .72 31.99
 4 ORDS NORMAL USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf 1 31.99
 4 ORDS NORMAL USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf .04 31.99

Oracle DBA

anuj blog Archive