Jijo K Jose

Create TableSpace in Oracle XE 11g using SQLPLUS

To create a user first you need to create a tablespace to create a tablespace use the following query

create tablespace <tablespace_name> datafile '<path>' size 32m autoextend on next 32m maxsize 2048m extent management local;

<tablespace_name> : Your new tablespace name

<path> : Path to the dbf file (database file)

Example

create tablespace MYTABLESPACE datafile '/data/oracle/oradata/tpol/tools01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;

Create User  in Oracle XE 11g using SQLPLUS

If you have no privilege for create .dbf (database) file then you can skip the above query and you can create the user using the system tablespace.

Create user

SQL> CREATE USER jijokjose IDENTIFIED BY jijokjose DEFAULT TABLESPACE SYSAUX TEMPORARY TABLESPACE "TEMP";

User created.

SQL>

Change Quota to the user

SQL> ALTER USER jijokjose QUOTA UNLIMITED ON SYSAUX;

User altered.

SQL>

Grant privilege to the user

SQL> GRANT connect, create procedure, create session, create table, create type, create view, create synonym,create sequence, create trigger, resource TO jijokjose;

Grant succeeded.

SQL>

or

SQL> GRANT DBA TO jijokjose;

Grant succeeded.

SQL>

Connect the user to the database using SQLPLUS

Open command prompt and type the following command and then enter your username and password

H:\>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 23 10:31:52 2013

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

Enter user-name: jijokjose@xe
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

SQL>