This discussion is archived
12 Replies Latest reply: Feb 21, 2013 1:53 PM by 864103 RSS

system tablespace

864103 Newbie
Currently Being Moderated
Hi guys ,

oracle 11g ASM RAC under OL 5 2 nodes


today i get system tablespace was full 85 % ???

can i add datafile ??? or oracle not recommend to do that ???

or just resize it ??? how to resize system tablespace ??? i do it but it give me error ???

ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"DATA/medi/datafile/system.259.763062381"
  • 1. Re: system tablespace
    LaserSoft Journeyer
    Currently Being Moderated
    861100 wrote:
    Hi guys ,

    oracle 11g ASM RAC under OL 5 2 nodes


    today i get system tablespace was full 85 % ???

    can i add datafile ??? or oracle not recommend to do that ???

    or just resize it ??? how to resize system tablespace ??? i do it but it give me error ???

    ERROR at line 1:
    ORA-01516: nonexistent log file, data file, or temporary file
    "DATA/medi/datafile/system.259.763062381"
    You can give a single "?" instead of "????"
    Is it ASM? use "+DATA" instead of "DATA"
  • 2. Re: system tablespace
    Paul M. Oracle ACE
    Currently Being Moderated
    how to resize system tablespace ??? i do it but it give me error ???
    Please post the exact command you executed.
    ERROR at line 1:
    ORA-01516: nonexistent log file, data file, or temporary file
    "DATA/medi/datafile/system.259.763062381"
    Shouldn't be '+DATA/medi/datafile/system.259.76306238' ?
  • 3. Re: system tablespace
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    COPY & PASTE what you did please
  • 4. Re: system tablespace
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Error: ORA-01516
    Text: nonexistent log file, datafile, or tempfile %s
    ---------------------------------------------------------------------------
    Cause: An attempt was made to use ALTER DATABASE to rename a log file, data file, or temporary file; or to change attributes of a data file
    or temporary file (for example, resize, autoextend, online or offline); or to re-create or move a data file.
    The attempt failed because the specified file is not known to the database's control file or is not of a type supported by the request.
    Action: Specify the name or number of an existing file of the correct type, as appropriate.
    Check the relevant V$ table for a list of possible files.
  • 5. Re: system tablespace
    864103 Newbie
    Currently Being Moderated
    i hate myself . you are right.
  • 6. Re: system tablespace
    Step_Into_Oracle_DBA Explorer
    Currently Being Moderated
    861100 wrote:
    today i get system tablespace was full 85 % ???

    can i add datafile ??? or oracle not recommend to do that ???
    You can Add new datafile or can resize the existing one
    >
    or just resize it ??? how to resize system tablespace ??? i do it but it give me error ???
    To resize the existing datafile OR add new datafile.

    SQL> set lines 200
    SQL> col file_name for a75
    SQL> col TABLESPACE_NAME for a25
    SQL>
    SQL> Select tablespace_name,FILE_ID,file_name,autoextensible,round(bytes/1024/1024/1024,2) "GB",round(maxbytes/1024/1024/1024,2) "Max in GB" from 
    
    dba_data_files  where tablespace_name in ('SYSTEM') order by file_id;
    
    TABLESPACE_NAME              FILE_ID FILE_NAME                                                                   AUT         GB  Max in GB
    ------------------------- ---------- --------------------------------------------------------------------------- --- ---------- ----------
    SYSTEM                             1 +DATA/mydb/datafile/system.271.800414389                            YES        .32         32
    
    SQL> Alter database datafile 1 resize 350m;
    
    Database altered.
    
    SQL> Alter tablespace system add datafile '+DATA' size 10m;
    
    Tablespace altered.
    
    SQL> Select tablespace_name,FILE_ID,file_name,autoextensible,round(bytes/1024/1024/1024,2) "GB",round(maxbytes/1024/1024/1024,2) "Max in GB" from 
    
    dba_data_files where tablespace_name in ('SYSTEM') order by file_id;
    
    TABLESPACE_NAME              FILE_ID FILE_NAME                                                                   AUT         GB  Max in GB
    ------------------------- ---------- --------------------------------------------------------------------------- --- ---------- ----------
    SYSTEM                             1 +DATA/mydb/datafile/system.271.800414389                            YES        .34         32
    SYSTEM                             6 +DATA/mydb/datafile/system.279.807764051                            NO         .01          0
    Please keep forum clean by Marking your Post as Answered or Helpful if Your question is answered.

    Thanks & Regards,
    SID
    (StepIntoOracleDBA)

    http://stepintooracledba.blogspot.in/
  • 7. Re: system tablespace
    864103 Newbie
    Currently Being Moderated
    thanks man , but it's already answer , one more thing i found some articals it said oracle not recommend
    to add new datafile to system tablespace just resize it ? it right
  • 8. Re: system tablespace
    Step_Into_Oracle_DBA Explorer
    Currently Being Moderated
    Kindly take a look
    System tablespace is 99% full
    Please keep forum clean by Marking your Post as Answered or Helpful if Your question is answered.

    Thanks & Regards,
    SID
    (StepIntoOracleDBA)

    http://stepintooracledba.blogspot.in/
  • 9. Re: system tablespace
    EdStevens Guru
    Currently Being Moderated
    861100 wrote:
    thanks man , but it's already answer , one more thing i found some articals it said oracle not recommend
    to add new datafile to system tablespace just resize it ? it right
    Well, generally there shouldn't be a NEED to resize it. The fact that you are suggests you are allowing application objects to be created in the system tablespace, which is a no-no.
  • 10. Re: system tablespace
    jgarry Guru
    Currently Being Moderated
    861100 wrote:
    thanks man , but it's already answer , one more thing i found some articals it said oracle not recommend
    to add new datafile to system tablespace just resize it ? it right
    Which version are you on (11g is a marketing label, a version would be like 11.2.0.4)? Is your data file autoextend? Are you sure you are not talking about sysaux or some auditing thing? Which article?
  • 11. Re: system tablespace
    977635 Newbie
    Currently Being Moderated
    Well, system tablespace can also extend due to auditing data which is turned on by default if you create the database using DBCA.
    I personally create a separate SYSAUD tablespace just for the auditing data and then move the auditing tables to the new tablespace using DBMS_AUDIT_MGMT package.

    Also, if the OP performs a lot of exports and they are interrupted, the metadata tables used by DP can be large and numerous.
    I've been to a site that I've cleaned out at least 4G of large tables due to datapump jobs.
  • 12. Re: system tablespace
    864103 Newbie
    Currently Being Moderated
    thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points