0

I have a pluggable database.I need to find locations where the data file of this pluggable db reside? Is there any command I can execute? I actually don't remember if I had specified the data file when I created the pluggable db.

Some guidance is appreciated.

diziaq
  • 6,881
  • 16
  • 54
  • 96
user3400060
  • 299
  • 3
  • 9
  • 23

2 Answers2

1

If a default template for PDB is used the PDB data files are by default created in a folder named the same as PDB database which resides in CDB database folder.

If you are connected to CDB datababase you can see all data files for both container and pluggable databases in CDB_DATA_FILESview. If you are connected to to pluggable datatabase with CDB account you can switch between CDB and PDBS using ALTER SESSION SET CONTAINER = <pdb_name> and ALTER SESSION SET CONTAINER = CDB$ROOT and respective views in data dictionary will return the data related to active container.

Is also good to know content of CON_ID column available in many views.

  • 0 - all databases combined
  • 1 - container database
  • 2 - seed for pluggable databases
  • 3 - 254 - pluggable database

You can also include CON_ID column to database objects using special function CONTAINERS: SELECT * FROM CONTAINERS(<table or view>)

Husqvik
  • 5,669
  • 1
  • 19
  • 29
  • thanks! can you tell me a sample data file name of any pluggable database? I mean what it typically starts or ends with so I can look if a data file for my pluggable db exists. – user3400060 Nov 20 '15 at 19:23
  • temp file is usually named _TEMP01.dbf. You also can search just for SYSTEM01.DBF it should find all container and pluggable databases because all databases should have SYSTEM01.DBF and SYSAUX01.DBF files. But it's much simpler just to select it from data dictionary with CDB$ROOT as active container. – Husqvik Nov 20 '15 at 19:29
  • thanks! so i am assuming temp file is the data file infact? I found it,thanks.But it was under my CDB1/DATAFILE folder.Should it not be under a folder having same name as my pluggable database? – user3400060 Nov 20 '15 at 19:33
  • It should be in both. Only UNDO tablesplace is shared between CDB and PDB databases. TEMP tablespaces are exclusive. – Husqvik Nov 20 '15 at 19:34
  • ok,is there a way I can put it under the folder having the same name as my PDB? That folder of mine is empty :( – user3400060 Nov 20 '15 at 19:36
  • Are you sure someone hasn't dropped the database or its creation failed? Are you logged in under PDB account? Then you can manipulate with the datafiles using SQL. – Husqvik Nov 20 '15 at 19:41
  • No,I don't think the pdb has been dropped.I can see its name when I query: select name from V$PDBS; – user3400060 Nov 20 '15 at 19:44
  • how can I create PDB account by the way? – user3400060 Nov 20 '15 at 19:45
  • 1
    Search and read documentation everything is there: http://www.oracle.com/technetwork/articles/database/multitenant-part1-pdbs-2193987.html – Husqvik Nov 20 '15 at 19:49
0

Login to the pluggable database and just query dba_data_files.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • thanks! looks like I did not create any datafile at the time I created my pluggable db as I don't have any folder for my pluggable db datafile by my plug db name.Can you advice? – user3400060 Nov 20 '15 at 18:12