OracleMultitenant

Drop a PDB whose datafiles are gone

For a PDB being decommissioned whose storage was already removed, so starting the CDB or opening the PDB fails with ORA-01157 and ORA-01110 on its datafiles. Takes the missing files offline and drops the PDB. If the files should still exist, stop and remount or restore them instead: this deletes the PDB for good.

Changes data12c+Run on CDB root

Not yet verified. How scripts are tested

This script changes the database. Check the names and values before you run it.

ora-fix-hung-pdb.sql

1-- Run as SYSDBA, starting in the CDB root. Replace <PDB_NAME> and <file#>.2-- Only for a PDB you're decommissioning: these steps can't be undone.3 4-- 1. State of the CDB and its PDBs5SELECT name, open_mode FROM v$database;6SELECT name, open_mode FROM v$pdbs ORDER BY name;7 8-- 2. The PDB's datafiles. V$DATAFILE still lists them when the files are missing.9SELECT d.file#, d.status, d.name10FROM v$datafile d11JOIN v$pdbs p ON p.con_id = d.con_id12WHERE p.name = UPPER('<PDB_NAME>')13ORDER BY d.file#;14 15-- 3. Take each missing file offline, inside the PDB. Repeat for every file# from step 2.16ALTER SESSION SET CONTAINER = <PDB_NAME>;17ALTER DATABASE DATAFILE <file#> OFFLINE DROP;18 19-- 4. Back in the root: close the PDB (skip if step 1 showed it MOUNTED), then drop it.20ALTER SESSION SET CONTAINER = CDB$ROOT;21ALTER PLUGGABLE DATABASE <PDB_NAME> CLOSE IMMEDIATE;22DROP PLUGGABLE DATABASE <PDB_NAME> INCLUDING DATAFILES;23-- If the drop complains that it can't delete files that are already gone,24-- use KEEP DATAFILES instead: there's nothing left to delete.25 26-- 5. Check it's gone27SELECT name, open_mode FROM v$pdbs ORDER BY name;

-- Run as SYSDBA, starting in the CDB root. Replace <PDB_NAME> and <file#>.
-- Only for a PDB you're decommissioning: these steps can't be undone.

-- 1. State of the CDB and its PDBs
SELECT name, open_mode FROM v$database;
SELECT name, open_mode FROM v$pdbs ORDER BY name;

-- 2. The PDB's datafiles. V$DATAFILE still lists them when the files are missing.
SELECT d.file#, d.status, d.name
FROM v$datafile d
JOIN v$pdbs p ON p.con_id = d.con_id
WHERE p.name = UPPER('<PDB_NAME>')
ORDER BY d.file#;

-- 3. Take each missing file offline, inside the PDB. Repeat for every file# from step 2.
ALTER SESSION SET CONTAINER = <PDB_NAME>;
ALTER DATABASE DATAFILE <file#> OFFLINE DROP;

-- 4. Back in the root: close the PDB (skip if step 1 showed it MOUNTED), then drop it.
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE <PDB_NAME> CLOSE IMMEDIATE;
DROP PLUGGABLE DATABASE <PDB_NAME> INCLUDING DATAFILES;
-- If the drop complains that it can't delete files that are already gone,
-- use KEEP DATAFILES instead: there's nothing left to delete.

-- 5. Check it's gone
SELECT name, open_mode FROM v$pdbs ORDER BY name;

Save it as ora-fix-hung-pdb.sql and run it with SQL> @ora-fix-hung-pdb.

Open in denrepo

More Oracle scripts: Multitenant

Pluggable databases and their stateEvery PDB with its open mode, whether it's restricted, when it opened and its size. RESTRICTED = YES usually means a plug-in violation.
Tablespace usage across all PDBsThe tablespace report for every container at once, fullest first, so you don't have to switch into each PDB.
Unresolved plug-in violationsWhy a PDB opened in restricted mode: patch mismatches, parameter conflicts and missing options, with Oracle's suggested fix in the message.
Open PDBs and keep them open after restartOpens every PDB, then saves the state so they reopen automatically when the CDB restarts. The first query shows what's currently saved.