Object Browser and Table Creation

The following section introduces Object Browser and creates a sample table as example:
Object Browser is part of the SQL Developer front end provided to create, view and modify database objects through a GUI. The object browser screen is seen below:

To create a table:
To create a table click on the Create button and select Table option. The following page appears:
Enter table name and field names with data type.

Click Next> and enter primary key information:

Select populate from a new sequence and select primary key column. So, while creating the table the system also creates a new sequence and a before insert trigger to polulate the primary key column from the sequence.

The following two pages request you to enter foreign key and check constraints. Then finish to create the table.

Import/Export Database Schema

The following section shall discuss importing and exporting a database user schema in Oracle.

Exporting: The export process involves using a export tool from command line. Run the following command to generate a dump(*.dmp) file with all object definitions and data.

c:...\> exp username/password file=\path\db.dmp

To import the dump file into another database user, ececute the following command:

c:...\> imp username/password file=\path\db.dmp full=y

Seting up Oracle 10g XE

The following section is a guide to running Oracle 10g XE and creating user accounts for it.

To start SQL Developer UI go to "Start > All Programs > Oracle 10g > Go To Database Home Page" or type the following URL in your browser "http://127.0.0.1:8081/apex/"

The screen on the right shall appear.

Login as "system" and password as the password entered during installation.

The screen below should appear on successful login.


Do not manipulate the table definitions or data in the"system" account.
To create an user go to Administration > Database Users > Create
The following create user screen appears:

Provide required permissions and click on "Create". Grant DBA role privileges if required.

Internally the following SQL will be executed:

CREATE USER XPBlog IDENTIFIED by 10g;
GRANT CREATE SESSION TO XPBlog;
GRANT CREATE SYNONYM TO XPBlog;... A grant will be executed for each privilege granted.
However, if DBA is selected then the following will be executed:
GRANT DBA TO XPBlog;

"DBA" here is a role which has been granted pre-defined privileges. User defined roles can also be created.