-3

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

Andrei Pislariu
  • 82
  • 1
  • 7
  • 3
    The schema cannot be owned by anyone. It's a logical container for the segments owned by different schemas (users). See ["Overview of Tablespaces"](http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm#i2006) section in the Database Concepts guide. Therefore you cannot say `CREATE TABLESPACE somechema.tablespace_name...`, because the correct syntax is `CREATE TABLESPACE tablespace_name...` – YasirA May 16 '13 at 13:27
  • 1
    "*In schema A I create another user: schema C*" that is nonsene. You don't create a schema "*in a schema*". A user/schema is something global to the Oracle instance. The same for tablespaces. They are not created "in" a schema. Again they are global to the Oracle instance. –  May 16 '13 at 13:44
  • a_horse_with_no_name: I don't want to add schemaA to schemaC. I want to create it from schemaC. I am not connected with system user, because schemaC has almost the same permitions. – Andrei Pislariu May 16 '13 at 14:09

1 Answers1

2

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

Robe Elckers
  • 967
  • 1
  • 6
  • 19
  • Thanks Robe and Yasir. I was confused about the structure. I thought that the tablespace is an element from a schema not an individual element of the database. – Andrei Pislariu May 17 '13 at 07:16