DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: add datafile

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    I need to add a datafile to the tablespace. Please let me know the steps to do that.

  2. #2
    Join Date
    Sep 2000
    Posts
    155

    $ sqlplus system/manager@orcl

    SQL> alter tablespace tablespace_name add datafile
    '../../../name.dbf' size 1000M;

    SQL> exit;

  3. #3
    Join Date
    Aug 2000
    Posts
    163
    Thanks.
    I need to set next and max values. It doesn't let me do that.

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    Check the SQL refrence on Oracle's Technet for your database version for instruction on how to use any command you need.

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    ALTER TABLESPACE surv_data
    ADD DATAFILE '/oracle/oradata/wwwp/data/surv_data_01.dbf' size 25616 K
    AUTOEXTEND ON NEXT 5120 K MAXSIZE 2048016 K
    MINIMUM EXTENT 160 K

    min extent is valid only from 8.x+

  6. #6
    Join Date
    Aug 2000
    Posts
    163

    Wink

    OK. I added a datafile using command:
    'alter tablespace /tablespace name/ add datafile /datafile name/ size 50m;
    For some reason through GUI tool it says my datafile size is unlimited. ??? Is it right?
    How do I look it up through sqlplus?
    Also, I would like to alter it using: alter database datafile /datafile name/ autoextend off next 50m maxsize 500m and get ORA-00900: invalid SQL statement
    When I set autoextend on it works fine.
    What am I missing here?

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    <font face='courier'>
    ALTER DATABASE
    DATAFILE '/xxxx/yyyyy/wwww/wwww/xxxx_yyyy_01.dbf'
    RESIZE 9999 M
    AUTOEXTEND OFF NEXT 9999K MAXSIZE 99999K
    MINIMUM EXTENT 99K
    </font>

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    1. select file_name, bytes/1024/1024 mb, status, autoextensible
    from dba_data_files

    2. You only use next 50m maxsize 500m when autoextend is on.
    Jeff Hunter

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by mary [/i]
    [B]OK. I added a datafile using command:
    'alter tablespace /tablespace name/ add datafile /datafile name/ size 50m;
    For some reason through GUI tool it says my datafile size is unlimited. ??? Is it right?
    [/B][/QUOTE]

    No, if you set it to 50M then it is 50M. It's either a bug of your GUI tool or you've overlooked something. Are you sure it is not showing unlimited for MAXSIZE, not for file size?

    [QUOTE][i]Originally posted by mary [/i]
    [B]
    How do I look it up through sqlplus?
    [/B][/QUOTE]

    select bytes from dba_data_files where file_name = 'YOUR_NEW_FILE'

    [QUOTE][i]Originally posted by mary [/i]
    [B]Also, I would like to alter it using: alter database datafile /datafile name/ autoextend off next 50m maxsize 500m and get ORA-00900: invalid SQL statement
    When I set autoextend on it works fine.
    What am I missing here?
    [/B][/QUOTE]

    What is the point in seting the autoextent parameters (NEXT, MAXSIZE) if you switch autoextending off? Oracle obviously finds no good reason to set those two parameters if you set autoextending of at the same time.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Aug 2000
    Posts
    163
    shreddy's command will only work if autoextend is ON.
    Thank you people.

Posting Permissions

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


Click Here to Expand Forum to Full Width