Oracle HowTo

oracle.md

Checking UNDO Tablespaces per PDB:

SELECT b.name, a.con_id, a.tablespace_name, a.status FROM cdb_tablespaces a,v$pdbs b WHERE  a.tablespace_name LIKE 'UNDO%' and a.con_id=b.con_id;

Disabling Local UNDO Tablespaces for PDB (must be sysdba)

show con_name
shut immediate
startup UPGRADE
ALTER DATABASE LOCAL UNDO off;
shut immediate
startup
SELECT * from database_properties WHERE property_name='LOCAL_UNDO_ENABLED';

Droping UNDO Tablespace for each PDB (PDB is the name of pluggable database in the example below)

ALTER PLUGGABLE DATABASE ALL OPEN;
SELECT b.name ,a.con_id, a.tablespace_name, a.status FROM cdb_tablespaces a,v$pdbs b WHERE a.tablespace_name LIKE 'UNDO%' and a.con_id=b.con_id ORDER BY a.con_id;
ALTER SESSION SET CONTAINER=PDB;
show con_name
ALTER TABLESPACE UNDOTBS1 OFFLINE;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Configuring EM on HTTP

exec dbms_xdb_config.sethttpsport(0);
exec dbms_xdb_config.sethttpport(5500);

Initial PDB Setup Script

CREATE PLUGGABLE DATABASE <DB_NAME> ADMIN USER <username> IDENTIFIED BY <password> FILE_NAME_CONVERT=('/opt/oracle/oradata/SWUPP/pdbseed/','/opt/oracle/oradata/SWUPP/<DB_NAME>');
ALTER PLUGGABLE DATABASE <DB_NAME> OPEN;
ALTER PLUGGABLE DATABASE <DB_NAME> SAVE STATE;

ALTER SESSION SET CONTAINER=<DB_NAME>;
GRANT ALL PRIVILEGES TO <username>;
ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;
SELECT resource_name,limit FROM dba_profiles WHERE PROFILE = 'DEFAULT';

ALTER TABLESPACE UNDOTBS1 OFFLINE;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
ALTER SESSION SET CONTAINER=CDB$ROOT;

Change Account Password

ALTER USER <username> IDENTIFIED BY <password>;

Account Unlock

ALTER USER <username> IDENTIFIED BY <password> ACCOUNT UNLOCK;