Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Question

Good afternoon, I'm running a procedure and returning a cursor, so going the ORA-01652 error: unable to extend temp segment by 128 in tablespace TEMP

C# , ASP.NET

OracleCommand cmd = connection.CreateCommand(); OracleDataReader dr;

        DataTable dt = new DataTable();

        try
        {
            OpenConn();

            cmd.CommandText = "UNITIZA_RELATORIOS.getComparaZila";
            cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter par1 = new OracleParameter("vElemServico", OracleType.VarChar);
            par1.Value = elemServico;
            par1.Direction = ParameterDirection.Input;

            OracleParameter par2 = new OracleParameter("vdataInicio", OracleType.VarChar);
            par2.Value = dataInicio;
            par2.Direction = ParameterDirection.Input;

            OracleParameter par3 = new OracleParameter("vdataFim", OracleType.VarChar);
            par3.Value = dataFim;
            par3.Direction = ParameterDirection.Input;

            OracleParameter par4 = new OracleParameter("vOrdem", OracleType.VarChar);
            par4.Value = ordem;
            par4.Direction = ParameterDirection.Input;

            OracleParameter par5 = new OracleParameter("vCodAneel", OracleType.VarChar);
            par5.Value = codigoAneel;
            par5.Direction = ParameterDirection.Input;

            OracleParameter par6 = new OracleParameter("nCodigoError", OracleType.Number);
            par6.Size = 1;
            par6.Direction = ParameterDirection.Output;

            OracleParameter par7 = new OracleParameter("return_value", OracleType.Cursor);
            par7.Direction = ParameterDirection.ReturnValue;

            cmd.Parameters.Add(par1);
            cmd.Parameters.Add(par2);
            cmd.Parameters.Add(par3);
            cmd.Parameters.Add(par4);
            cmd.Parameters.Add(par5);
            cmd.Parameters.Add(par6);
            cmd.Parameters.Add(par7);

            dr = cmd.ExecuteReader();              

            dt.Load(dr);

            return dt;

Note: The error only when acontence consultation over 1800 lines

score 0 · Answer 1 · edited May 23 '17 at 12:14

This indicates your process is chewing up all the temporary tablespace...perhaps inefficient joins or query syntax. Have a look at the explain plan and see why it needs all that space. It maybe easier to optimize the query than the other solution:

From here Create a new datafile by running the following command:

alter tablespace TABLE_SPACE_NAME add datafile 'D:\oracle\Oradata\TEMP04.dbf' size 2000M autoextend on;

Error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

1 Answers1