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

1 comment:

Anuj Singh said...

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.

Oracle DBA

anuj blog Archive