Oracle Application Express (APEX) is an web application development tool that is installed by default with Oracle Database when database is created with Database Creation Assistant (DBCA).
Oracle Corp. has made available a free workspace on http://apex.oracle.com for everyone : this includes a 25 megabyte schema in an Oracle database hosted in some Oracle Corp. data center. As far as I know this is the only public SQL access to an Oracle database that is hosted by Oracle Corp:
public SQL access here means that from APEX web interface you can run SQL statements but this is the only way to run SQL statement for this database because there is no way to have some SQL*Plus or SQL Developer direct connection to this database.
Joel Kallman blog is likely the best information source where you can find implementation details about this system: in 2008 apex.oracle.com was running on a 2 CPU single server with 16 GB of RAM.
The access granted by apex.oracle.com is implemented with an Oracle database account which has the same name as the APEX workspace. This account cannot access most of the V$ views or DBA views (view names with DBA prefix). For example trying to access following views always return “ORA-00942: table or view does not exist” :
select * from sys.v_$database; select * from sys.v_$instance; select * from sys.v_$parameter; select * from sys.v_$archived_log; select * from sys.dba_data_files;
However you can access following V$ views:
sys.v_$version; sys.v_$active_instances; sys.v_$session_connect_info; sys.v_$session_longops; sys.sysfiles;
Did you know that SYSFILES is a Oracle version 5 view (according to Oracle7 Server Reference Manual) that still exists in Oracle 12 ?
Of course you also have access to ALL views (view names with ALL prefix) including:
all_users all_objects all_tables
Your database account has also execute privilege on SYS_CONTEXT function and I have used the following script to retrieve database information that is in V$DATABASE or in V$INSTANCE (or displayed by some SQL*Plus SHOW commands):
select sys_context('USERENV','HOST') from dual; select sys_context('USERENV','OS_USER') from dual; select sys_context('USERENV','DB_NAME') from dual; select sys_context('USERENV','DB_UNIQUE_NAME') from dual; select sys_context('USERENV','INSTANCE_NAME') from dual; select sys_context('USERENV','CON_NAME') from dual; select sys_context('USERENV','CON_ID') from dual;
Here are my findings about the database:
alter session set container=root;
you always get:
ORA-65090: operation only allowed in a container database
Note that in a non-CDB database you get this error message even if the specified PDB does not exist and even if you don’t have the SET CONTAINER privilege.
When running SQL scripts apex.oracle.com is using load balancing between the 2 database instances: I have noted after running 5 times in a row my script because INSTANCE_NAME has changed and archived log backups traces only exist on instance 1.
Note that apex.oracle.com is using Resource Manager to limit query resource usage: if you try to use too much resource you will likely error “ORA-00040: active time limit exceeded – call aborted”. J. Kallmann has documented this configuration on his blog. Practically this makes querying some dictionary views like ALL_OBJECTS and ALL_TABLES difficult.
I find quite surprising that on one hand Oracle Corp. has announced that starting from 188.8.131.52 release non-CDB architecture is deprecated and that on the other hand the only “public” Oracle database running 184.108.40.206 release is a non-CDB database.