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

1 comment:

  1. to check seed database file

    alter system set "EXCLUDE_SEED_CDB_VIEW"=FALSE ;

    System altered.


    show parameter EXCLUDE_SEED_CDB_VIEW

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    exclude_seed_cdb_view boolean FALSE


    set linesize 200 pagesize 200
    col FILE_NAME for a70

    SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES

    CON_ID TABLESPACE_NAME FILE_NAME
    ---------- ------------------------------ ----------------------------------------------------------------------
    2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
    2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
    4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
    4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
    4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
    4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf
    3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
    3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
    3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
    3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
    3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
    3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
    3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
    1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
    1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
    1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
    1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
    1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf

    18 rows selected.

    ReplyDelete