Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    11

    Unanswered: add datafile without specifying pathname

    how to add a datafile without specify the path name , like

    ALTER TABLESPACE <tablespace name> ADD DATAFILE SIZE 10M;

    if we specify like this, the datafile should stored in specified path.
    now my question is where we have to specify the path name?.
    regards,
    ji.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Lookup correct syntax...

    You need to specify full path in the DDL statement.

    Lookup the correct syntax in the fine Oracle® Database SQL Reference.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2009
    Posts
    11

    hai frnd

    Quote Originally Posted by LKBrwn_DBA
    You need to specify full path in the DDL statement.

    Lookup the correct syntax in the fine Oracle&#174; Database SQL Reference.
    ************************************************** *******
    thanks LKBrwn_DBA ,
    but i got the answer. That was,

    1. >alter system set dba_create_file_dest='fully specified path name where to be stored'
    2.>alter tablespace <tablespace name> add datafile <datafilename> size --m;


    If by doing so like this, it automatically create a datafile in the specified path with its own name. later we can rename the datafile by offline the tablespace. That will make by,

    1.>alter tablespace <tablespacename> offline normal;
    2. rename the datafile in the physical path. i.e., oracle->10.2.0->....>kk>01_9dfd.dbf. here 01_9dfd.dbf is the system specified default datafile name. Now rename to your own name as you wish.
    3.Then,
    >alter tablespace <tablespace> rename datafile 'fully specified old name' to 'fully specified new name'.

    regards,
    suji.
    Last edited by sujitha; 07-08-09 at 11:14.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Ooops...

    YES, dba_create_file_dest will do it -- forgot about that one.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Guwahati, India
    Posts
    33
    Nothing will work once Oracle start using the file (Store Data) .

    Do not try anything like those in production system. Its very dangerous !

    Any changes in the datafile should be done with utmost care
    G J Shankar Nath
    IT Consultant, Vedswasti Services Pvt. Ltd
    Guwahati-781007
    India

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Work security?

    Quote Originally Posted by sujitha
    ************************************************** *******
    but i got the answer. That was,

    1. >alter system set dba_create_file_dest='fully specified path name where to be stored'
    2.>alter tablespace <tablespace name> add datafile <datafilename> size --m;


    If by doing so like this, it automatically create a datafile in the specified path with its own name. later we can rename the datafile by offline the tablespace. That will make by,

    1.>alter tablespace <tablespacename> offline normal;
    2. rename the datafile in the physical path. i.e., oracle->10.2.0->....>kk>01_9dfd.dbf. here 01_9dfd.dbf is the system specified default datafile name. Now rename to your own name as you wish.
    3.Then,
    >alter tablespace <tablespace> rename datafile 'fully specified old name' to 'fully specified new name'.

    regards,
    suji.
    Why go through all that trouble if you can do it in ONE command by just supplying the full path?
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by LKBrwn_DBA
    Why go through all that trouble
    Hm... billable hours?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2009
    Posts
    11

    Smile fine

    Quote Originally Posted by LKBrwn_DBA
    Why go through all that trouble if you can do it in ONE command by just supplying the full path?
    .

    yes, you said write. But the simples never makes curious things.
    It just for funny trial.

    by
    suji.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •