Creating a Tablespace Remotely

Printable View

03-02-2006, 12:08 PM
dre1988

Creating a Tablespace Remotely

version: Oracle 9.2

i need to create a tablespace whose datafile resides on the database server. however, i can only access the database remotely via sql*plus (no OEM login). is there a way to create the tablespace like this even though the path to the datafile applies only to the server?

example:
CREATE TABLESPACE ESB_GRANITE_CTS_DATA
DATAFILE
'/path_on_server' SIZE 200M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

thanks!
03-02-2006, 12:16 PM
tamilselvan

You can.
I don't see any problem.

Tamil
03-02-2006, 12:23 PM
dre1988

how is it done? i'm trying to run this script from my desktop. how do you let oracle know that the path to the .dbf file is on the server?
03-02-2006, 12:46 PM
slimdave

You've already logged into the database server to execute the command, right? Are you getting an error or something?
03-02-2006, 12:49 PM
dre1988

Quote:

Originally Posted by slimdave

You've already logged into the database server to execute the command, right? Are you getting an error or something?

slimD,

i'm connected to the DB server via SQLPlus from my desktop. i've got a tnsnames entry for the DB and the system login to it. that's it. i have no operating system account for the server at this point or an OEM login to its management server instance. this is the error:

ERROR at line 1:
ORA-01119: error in creating database file '/path_on _server/file.dbf'
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory
03-02-2006, 12:50 PM
slimdave

is that spave in the name deliberate ora typo?
03-02-2006, 12:58 PM
dre1988

it's deliberate to protect the innocent.
03-02-2006, 02:29 PM
slimdave

so does that path exist? does the oracle user have permission to create a file there?
03-02-2006, 03:31 PM
dre1988

the path exists but the file does not. the oracle user is system. i am not sure if system is set up on the server with permission to create files in the path.
03-02-2006, 05:50 PM
mike9

Quote:

Originally Posted by dre1988

i am not sure if system is set up on the server with permission to create files in the path.

when do you plan to check it?
03-03-2006, 06:20 AM
thomasp

I'm definitely sure you're putting the path wrong.
Please check that your mentioned path exists.
03-03-2006, 09:27 AM
WilliamR

Quote:

Originally Posted by dre1988

slimD,

i'm connected to the DB server via SQLPlus from my desktop. i've got a tnsnames entry for the DB and the system login to it. that's it. i have no operating system account for the server at this point or an OEM login to its management server instance. this is the error:

ERROR at line 1:
ORA-01119: error in creating database file '/path_on _server/file.dbf'
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory

Just to be clear, SQL commands you enter from any client application, whether it is SQL*Plus, OEM, TOAD or anything else, are executed on your behalf by the database server. When you enter a CREATE TABLESPACE command and specify a path, a process owned by oracle (not you) will look on the server and attempt to create the file.
03-04-2006, 03:33 AM
maxdbo

hi dre1988
run a query on sql as

SQL > select FILE_NAME, TABLESPACE_NAME from dba_data_files;

you will get the location where current data files are use the same path to create ur tablespace , job done .

HTH
MAX