Re: ORA-00972, statement truncated in 10046 trace file

From: anthony Sanchez <anthonycsanchez_at_gmail.com>
Date: Thu, 10 Mar 2016 07:14:19 +0000
Message-ID: <CAEwv4fH_utTkP_64dk7ZWX8q5pU-bq4suirVPZWa-M85XmpOVw_at_mail.gmail.com>



Hemant, Mike, and Jure:

thank you all for your very helpful responses. I always wondered about the truncating in v$sql and now I have two alternative methods to troubleshoot ORA-00972 in the future. I really appreciate it.

I went down the AUDIT CREATE TABLE route first but was not seeing the SQL statement in AUD$ since the audit_trail init parameter was only set to "db" and not "db, extended". I did have the user setup client tracing and were were able to identify the issue was the length of a particular column and not the table name length.

Regards,
Anthony Sanchez

On Wed, Mar 9, 2016 at 8:59 PM Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> wrote:

> See Oracle Support Notes 1922923.1 and 2039794.1
>
>
>
> The CREATE TABLE text is truncated in V$SQL itself to begin with.
>
>
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *anthony Sanchez
> *Sent:* Thursday, March 10, 2016 3:12 AM
> *To:* oracle-l
> *Subject:* ORA-00972, statement truncated in 10046 trace file
>
>
>
> Hi folks,
>
>
>
> I have an app throwing ORA-00972 during a create table statement. I
> assume the table name is over 30 characters, however I'd like to see what
> the name attempting to be used is for troubleshooting purposes.
>
>
>
> The app is not returning error info that is that helpful, so I decided to
> setup a trace to get more info. While the trace did capture the error, the
> statement is being truncated/abbreviated in the trace file.
>
>
>
> PARSE ERROR #863794672:len=2610 dep=0 uid=52 oct=1 lid=52
> tim=1447938666502 err=972
>
> CREATE TABLE STAGING.wdsC ...
>
> WAIT #0: nam='SQL*Net break/reset to client' ela= 3 driver id=1413697536
> break?=1 p3=0 obj#=-1 tim=1447938666574
>
> WAIT #0: nam='SQL*Net break/reset to client' ela= 660 driver id=1413697536
> break?=0 p3=0 obj#=-1 tim=1447938667252
>
> WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=1447938667277
>
> WAIT #0: nam='SQL*Net message from client' ela= 4757 driver id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=1447938672062
>
> XCTEND rlbk=0, rd_only=1, tim=1447938672130
>
> WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=1447938672181
>
> WAIT #0: nam='SQL*Net message from client' ela= 660 driver id=1413697536
> #bytes=1 p3=0 obj#=-1 tim=1447938672860
>
>
>
> Any ideas on capturing the full statement?
>
>
>
> Oracle 11.2.0.4 on windows.
>
>
>
> Thank you,
>
>
> Anthony
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 10 2016 - 08:14:19 CET

Original text of this message