APEX: getting a list of parsing schemas for a specific workspace

Environment: APEX 4.1.1, Oracle database

When you plan to migrate an APEX installation to a new server, this will involve the following steps:

1) install APEX on the new server (the same version as on the old server).

2) create all necessary users.

3) export/import schema data from the old server to the new server (mostly using Data Pump).

4) export/import workspaces, applications and other objects from the old APEX installation to the new APEX environment.

Now, how do you know which schemas you need to migrate from your old server? After all, this could be a shared server which also stores the data for some other applications, so then it’s probably not a good idea to just copy everything.

The following query (executed as the APEX owner, APEX_040100) returns a list of all parsing schemas for a given workspace:

select distinct(owner) from apex_applications where upper(workspace)=upper('&workspace') order by 1 asc;


If you want see which applications use these schemas, with their availability status:

select owner,application_name,availability_status from apex_applications where upper(workspace)=upper('&workspace') order by 1 asc;

MATTHIASH,Test from Matthias,Available with Edit Links
MATTHIASH,demo application,Available with Edit Links
TESTUSER1,format tool,Available with Edit Links
TESTUSER2,demo 2,Unavailable

Finally, if you want to know how much disk space (in MB) the APEX parsing schemas use, you could use this query:

select owner,round(sum(bytes)/1024/1024,2) MB from dba_extents
where owner in (select distinct(owner) from apex_applications where upper(workspace)=upper('&workspace'))
group by owner order by 1 asc;




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: