More APEX notes

A very, very quick way of housekeeping:

BEGIN
 APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE ('&workspace_name','Y','Y');
END;
/

It will drop your code, database accounts, and tablespace associated with it.

If you ever want to create a workspace, and do some basic housekeeping:

set echo off verify off
accept apex_short_name prompt 'Enter the short APEX applicaton name: '
prompt ...Creating the APEX tablespace...
--
create tablespace &apex_short_name
 datafile '/oracle//&apex_short_name.01.dbf'
 size 10m
 autoextend on
 next 5m
 maxsize 1000m
 extent management local
 uniform size 64k;
-- User and grants
prompt ...Creating user and granting rights...
--
grant connect, resource
 to &apex_short_name._owner identified by &apex_app_db_password;
grant execute on dbms_pipe to &apex_short_name._owner;
alter user &apex_short_name._owner default atblespace &apex_short_name;
alter user &apex_short_name._owner quota unlimited on &apex_short_name;
-- APEX Workspace and schema
prompt ...Creating APEX Workspace &apex_short_name
BEGIN
   APEX_INSTANCE_ADMIN.ADD_WORKSPACE (
    p_workspace => '&apex_short_name',
    p_primary_schema => '&apex_short_name._owner',
    p_additional_schemas => null);
END;
/
--
prompt ...Creating APEX Workspace administrator account...
--
begin
wwv_flow_api.set_security_group_id(
   p_security_group_id=>APEX_UTIL.FIND_SECURITY_GROUP_ID('&apex_short_name'));
end;
/

begin
wwv_flow_fnd_user_api.create_fnd_user (
 p_user_name    => '&apex_short_name._ADMIN',
 p_web_password => '&apex_short_name._ADMIN',
 p_group_ids    => '',
 p_developer_privs=> 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
 p_default_schema=> '&apex_short_name._OWNER',
 p_account_locked=> 'N',
 p_account_expiry=> to_date('201001011300','YYYYMMDDHH24MI'),
 p_failed_access_attempts=> 0,
 p_change_password_on_first_use=> 'N',
 p_first_password_use_occurred=> 'Y',
 p_allow_access_to_schemas => '');
end;
/

commit;

For a given value of "abc" for apex_short_name, this will create:

• a tablespace ABC, with an associated datafile abc01.dbf
• a database account abc_owner with a password of your choice
• an APEX workspace abc, with an associated abc_owner schema
• an APEX Workspace administrator abc_ADMIN, with a password abc_ADMIN

Mind you: APEX Passwords are case sensitive; account names are not.