12 Replies Latest reply: Feb 21, 2013 3:53 PM by 864103 RSS

    system tablespace

    864103
      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
          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.
            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
              COPY & PASTE what you did please
              • 4. Re: system tablespace
                Osama_Mustafa
                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
                  i hate myself . you are right.
                  • 6. Re: system tablespace
                    Step_Into_Oracle_DBA
                    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
                      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
                        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
                          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
                            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
                              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
                                thanks.