Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62

    Unanswered: alter tablespace add datafile

    Hi,
    Here is a question, I did alter the tablespace system by adding new datafile.
    Is it necessary that i should restart the database to get this change effective?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    no, it is usable after the command has successfully completed

  3. #3
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    But after doing that also im not able to create table, getting the following error

    SQL> conn scott@mydb
    Enter password: *****
    Connected.
    SQL> create table abc(cc date);
    create table abc(cc date)
    *
    ERROR at line 1:
    ORA-01536: space quota exceeded for tablespace 'SYSTEM'


    see the following query results.

    SQL> select default_tablespace, temporary_tablespace from dba_users where username='SCOTT';

    DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------
    SYSTEM SYSTEM




    SQL> select * from dba_data_files where tablespace_name='SYSTEM';

    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------
    /data3/omass/oracle/oradata/omass/system01.dbf
    1 SYSTEM 293601280 35840 AVAILABLE
    1 YES 3.4360E+10 4194302 1280 293593088 35839

    /data3/omass/oracle/oradata/omass/system02.dbf
    8 SYSTEM 1048576 128 AVAILABLE
    8 YES 5242880 640 128 1040384 127


    Any suggestions?

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1.Have you got a quota specified for scott on that tablespace. Check in dba_ts_quotas using a privileged user to see who is allowed to use the tablespace and how much they can use.
    2.You shouldnt really be using the system tablespace or was it just to test the new datafile?

  5. #5
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    SQL> select * from dba_ts_quotas
    2 /
    no rows selected

    Oooops!!

    No, the SCott is in SYSTEM table space

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Still not sure of the error but is there much point in adding such a small datafile (1m extending to 5m). Do alter database datafile '...' autoextend on next 10m maxsize 100m if you want it to grow upto a 100m.

    Alan

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You should NOT have any other schema's default tablespace SYSTEM !!!

    sql> select tablespace_name from dba_tablespaces;

    sql> alter user scott default tablespace tablespacenamefromabove temporary tablespace temporarytablespacenamefromabove;

    sql> ALTER USER scott QUOTA UNLIMITED ON tablespacefromabove;

    HTH
    Gregg

  8. #8
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    Thanx .
    I removed scott to another tablespace.Now its working

Posting Permissions

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