Tuesday, August 31, 2010

APEX Spring Cleaning

Each time you do a major upgrade APEX creates a new schema. It does not remove the older schemas, which allows you to roll back to previous versions in case something happens.

I was upgrading some older instances of APEX and realized that I still had some of these older schemas lying around and decide that it was time to do some spring cleaning (I realize that isn't exactly spring time, unless you live in Australia).

From the APEX installation guide (http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/otn_install.htm#CBHBABCC) here's how to identify and remove older versions of APEX.

Please read the documentation and understand what exactly you're doing before you do this!

- Identify old APEX schemas

SELECT username
FROM dba_users
WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%')
AND USERNAME NOT IN (
SELECT 'FLOWS_FILES'
FROM DUAL
UNION
SELECT 'APEX_PUBLIC_USER' FROM DUAL
UNION
SELECT SCHEMA s
FROM dba_registry
WHERE comp_id = 'APEX')

- Remove old schemas
Connect as SYS using the SYSDBA role then run:

DROP USER FLOWS_030000 CASCADE; -- Where "FLOWS_030000" is the username from the previous query

No comments:

Post a Comment

Post a Comment