Tuesday, January 12, 2010

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.

1 comment:

mubashir said...

Its great to see your work ,it shows your experience in databases keep it up....

sincerely Mubashirs