Search code examples
oracle-databasetablespace

create tablespace for other user oracle

I am using Oracle 11g and SQL Developer.

In my database I have 2 schemas: schema A (pretty similar as system) and schema B. In schema A I create another user: schema C. Here I want to add a tablespace immediately after what I create it.

What I want: to create a script which is creating a tablespace in schema C through schema A.

I am in schema A and I try next code: 

CREATE TABLESPACE schemaC.tableSpace_DATA DATAFILE 
  '/u01/app/oracle/oradata/Test/fileName_DATA.DBF' SIZE 10M 
  AUTOEXTEND ON 
  NEXT 56K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;

but I got the error ORA-02180: "invalid option for CREATE TABLESPACE". The problem is here: schemaC.tableSpace_DATA because if I remove schemaC then the tablespace is created, but in schemaA, not in schemaC.

Any suggestions please?:D

Solution

  • I think you need to do it the other way around:

    1. Create the tablespace
    2. Create the new schema and set the default tablespace to the newly created tablespace.

    See http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm