APEX: getting a list of parsing schemas for a specific workspace
April 23, 2012 Leave a comment
Environment: APEX 4.1.1, Oracle database 184.108.40.206.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;
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
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;