Oracle Application Express 11g

Question

I am new to Oracle and want to practice SQL.

I downloaded the Application Express Edition 11g for Windows x64. I followed instructions and created a workspace and user.

However when I try to create a table I get an error "ORA-20000: User xxx has no privileges on the schema. Error checking privileges.

However when I go and look at the user in the administration is says that I am the workspace administrator. I also cannot seem to log in as SYSTEM or SYS using the password I created when I installed.

I am very confused.

Answer 1

There are two terms you are dealing with:

• a database, which contains tables and data - tables belong to users such as SYS or SYSTEM - you should not use them at all, except for unlocking existing user (HR or SCOTT; whichever it is) in order to use it for practicing
• Application Express, which is a tool you use to access the database and create applications which will manage data stored in your tables. Once you log on as admin, you can create workspaces (which are mapped to database users/schemas) and developers (who will create applications). This "admin" user is very different from database owners (sys/system), so you can't log on to Apex using sys as username and its password

As you installed the 11g XE database (which has built-in Apex version 4.x, I think - it is not Apex 11g, it doesn't exist), at the operating system command prompt run SQL*Plus and connect as SYS:

C:\>sqlplus sys@xe as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sub Lis 6 20:26:19 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> 

List users in the database:

SQL> select username, account_status from dba_users order by username;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
ANONYMOUS                      OPEN
APEX_PUBLIC_USER               LOCKED
APEX_040000                    LOCKED
APPQOSSYS                      EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
DBSNMP                         EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
FLOWS_FILES                    LOCKED
HR                             LOCKED
IMPORTER                       OPEN
MDSYS                          EXPIRED & LOCKED
MIKE                           OPEN
ORACLE_OCM                     EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
SCOTT                          OPEN
SYS                            OPEN
SYSTEM                         OPEN
XDB                            EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED

19 rows selected.

SQL>

See the HR (human resources) user? It is locked. We'll unlock it and change its password (to "hr") so that you could use it in Apex, as it already contains several tables full of data.

SQL> alter user hr account unlock;

User altered.

SQL> alter user hr identified by hr;

User altered.

SQL>

Connect as HR, just to see what it contains:

SQL> connect hr/hr@xe
Connected.
SQL> select * From tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
DEPT                           TABLE
EMPLOYEES                      TABLE
EMP_DETAILS_VIEW               VIEW
INSTRUCTOR                     TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
PRODUCT                        TABLE
REGIONS                        TABLE
RIGHTS                         TABLE
TEACHES                        TABLE
USERS                          TABLE

14 rows selected.

SQL>

If you want to create your own tables, do it in HR schema. As I said, leave SYS and SYSTEM alone; they are special, you don't use them for everyday purposes. If you do something unusual, you might destroy the database.

OK; now run Apex, connect as admin, navigate to "Manage workspaces" and:

• create workspace - follow the wizard. When asked, "re-use existing schema" answer "Yes" and select HR from list of values
• manage developers and users - create user. Follow the wizard. As a workspace, select previously created workspace, set the password

Once you're done, logout of admin and logon as newly created user - provide workspace name, username and password - that should let you in. Once you're in Apex - as a developer - you can create your first page (an interactive report might be a good choice).

Good luck!

[EDIT: built-in Apex on 11gXE]

• click Start - All programs - Oracle database 11g Express Edition - Get started
• a page opens, offering several red buttons; the last of them is "Application Express"; click it
• login asks for database user with DBA privileges; enter username = SYS (or SYSTEM) and its password
• you're now redirected to "create Apex workspace" page. Items' labels are underlined; it means that they contain help (feel free to click). The first item is "Database user" radio button which offers "Create new" and "Use existing" - you'd choose "Use existing"
• for "Database username", choose "HR" from select list. HR is now unlocked, right? If it is expired, make sure you altered it in order to set the password (alter user hr identified by hr)
• Application Express username (and password) represents username related to Apex (i.e. not the database user) - you'll use it to log on to Apex

If you want to see which workspaces already exist, connect to the database as SYS (using SQLPlus at the operating system command prompt):

C:\>sqlplus sys@xe as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Ned Lis 7 12:40:34 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from all_users where username like 'APEX%';

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------
APEX_040000                            47 29.05.14
APEX_PUBLIC_USER                       45 29.05.14

SQL> -- describe table that contains info about workspaces
SQL> desc apex_040000.apex_workspaces
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WORKSPACE                                 NOT NULL VARCHAR2(255)
 SOURCE_IDENTIFIER                                  VARCHAR2(8)
 SCHEMAS                                            NUMBER
 LAST_PURGED_SESSION                                DATE
 ALLOW_APP_BUILDING_YN                              VARCHAR2(1)
 ALLOW_SQL_WORKSHOP_YN                              VARCHAR2(1)
 ALLOW_WEBSHEET_DEV_YN                              VARCHAR2(1)
 ALLOW_TEAM_DEVELOPMENT_YN                          VARCHAR2(1)
 ALLOW_TO_BE_PURGED_YN                              VARCHAR2(1)
 SESSIONS                                           NUMBER
 APPLICATIONS                                       NUMBER
 APPLICATION_PAGES                                  NUMBER
 APEX_USERS                                         NUMBER
 APEX_DEVELOPERS                                    NUMBER
 APEX_WORKSPACE_ADMINISTRATORS                      NUMBER
 FILES                                              NUMBER
 SQL_SCRIPTS                                        NUMBER
 TRANSLATION_MESSAGES                               NUMBER
 FILE_STORAGE                                       NUMBER
 LAST_LOGGED_PAGE_VIEW                              DATE
 PAGE_VIEWS                                         NUMBER
 WORKSPACE_ID                              NOT NULL NUMBER

SQL> col workspace format a10
SQL> select workspace, source_identifier, apex_developers
  2  from apex_040000.apex_workspaces;

WORKSPACE  SOURCE_I APEX_DEVELOPERS
---------- -------- ---------------
HR         HR                     1
INTERNAL                          0

SQL>

If HR workspace already contains, it means that you've already created it.

In web browser, connect as admin (use address http://127.0.0.1:8080/apex/apex_admin). (Workspace name is "internal"; you don't have to enter it). Username = admin, password = ... huh, not sure. Try SYS password. If you can't connect, you can change that password. Locate file named APXCHPWD.SQL ("Apex change password") on your disk; for example, it'll be in C:\oraclexe\app\oracle\product\11.2.0\server\apex\apxchpwd.sql directory. In SQLPlus, connected as SYS, execute that file:

SQL> show user
USER is "SYS"
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\apex\apxchpwd.sql
Enter a value below for the password for the Application Express ADMIN user.


Enter a password for the ADMIN user              []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.


Commit complete.

SQL>

Admin will now have password you've just typed, so - back to Apex admin, connect; you'll have to change that password so - do it, and connect again.

You'll now see "Manage Workspaces" button. In there, there are numerous links which do stuff. If you want, you can remove the HR workspace (following the "Remove Workspace" link) and create it back again.

The next step is to create a developer - use "Manage Developers and Users" link. Once you do that, you should be able to log on to Apex as developer, using "HR" as workspace name, developer username and password as credentials.

In order to be able to simultaneously adjust Apex as admin and connect as a developer, open a new connection using a different browser (for example, Chrome for one thing, Internet Explorer for another) - if you use the same browser (but different tabs or even a new browser session entirely), it won't work - new connection will terminate previous one.

I hope that the above will get you started.

Apparently, built-in Apex version is 4.0.2 (quite old one, at least 5-6 years), but will do at the beginning. Upgrade to a more recent version isn't difficult - download it and run SQL file which does everything for you. Just follow the Installation Guide.

I'd also suggest you to read & follow 4.0 Apex User's Guide, so that I wouldn't have to create a tutorial here, on Stack Overflow :)