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

Environment: APEX 4.1.1, Oracle database 11.2.0.3.0

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;

OWNER
MATTHIASH
TESTUSER1
TESTUSER2

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;

OWNER,APPLICATION_NAME,AVAILABILITY_STATUS
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;

OWNER,MB
MATTHIASH,10.50
TESTUSER1,356.78
TESTUSER2,0.5

 

Matthias

Advertisements

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: