SEO

Good Afternoon,

I am hoping someone can help me out. I want to drop a global
temporary
table in classic asp after my recordset has completed printing out
it's rows.


I tried executing the following statement and I get the following
error:


Drop Table tmm_a03_gtt_YYYJCO3_0611154125


Microsoft OLE DB Provider for ODBC Drivers error '80004005'


[Microsoft][ODBC driver for Oracle][Oracle]ORA-14452: attempt to
create, alter or drop an index on temporary table already in use


FYI; We are using Oracle 10 g.


Thanks,

Juan

On Mon, 11 Jun 2007 13:53:10 -0700, juan
<juan.oceguera@nav-international.com> wrote:

>Good Afternoon,
>
>I am hoping someone can help me out. I want to drop a global
>temporary
>table in classic asp after my recordset has completed printing out
>it's rows.
>
>
>I tried executing the following statement and I get the following
>error:
>
>
>Drop Table tmm_a03_gtt_YYYJCO3_0611154125
>
>
>Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>
>
>[Microsoft][ODBC driver for Oracle][Oracle]ORA-14452: attempt to
>create, alter or drop an index on temporary table already in use
>
>
>FYI; We are using Oracle 10 g.
>
>
>Thanks,
>
>Juan

You can't be bothered with looking up the error on tahiti? Because we
are not going to do this for you.

--
Sybrand Bakker
Senior Oracle DBA

juan wrote:
> Good Afternoon,
>
> I am hoping someone can help me out. I want to drop a global
> temporary
> table in classic asp after my recordset has completed printing out
> it's rows.

By doing so you are defeating the entire point have having a global
temporary table. Do you understand what they are? In Oracle?

The docs are at: http://tahiti.oracle.com

If you need one ... you shouldn't be dropping it.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 11, 5:56 pm, DA Morgan <damor...@psoug.org> wrote:
> juan wrote:
> > Good Afternoon,
>
> > I am hoping someone can help me out. I want to drop a global
> > temporary
> > table in classic asp after my recordset has completed printing out
> > it's rows.
>
> By doing so you are defeating the entire point have having a global
> temporary table. Do you understand what they are? In Oracle?
>
> The docs are at:http://tahiti.oracle.com
>
> If you need one ... you shouldn't be dropping it.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Daniel,

I've tried to respond several times. I am hoping that one of these
transmissions will go through.

We are running the sql via a web application and once the data has
been rendered, we wanted to administer clean up. On the web it's
common practice to discard our objects (recordsets, connections,
etc). It's my understanding that the data in GTT's is deleted once
the session is done, but the schema will persist. These schemas will
persist in the temp space...Is that correct?

What is the best practice in removing these unused GTT schemas?

Thank you in advance,

Juan

PS Please let me know if this should be addressed in a different
group.

On Tue, 12 Jun 2007 13:48:28 -0700, juan
<juan.oceguera@nav-international.com> wrote:

>It's my understanding that the data in GTT's is deleted once
>the session is done, but the schema will persist. These schemas will
>persist in the temp space...Is that correct?

Not correct. The *table* will persist. Please do not confuse a table
with a schema
>
>What is the best practice in removing these unused GTT schemas?

Tie your arms behind your back to avoid you are wasting your time in
dropping them.
>
>Thank you in advance,

--
Sybrand Bakker
Senior Oracle DBA

On Tue, 12 Jun 2007, juan.oceguera@nav-international.com wrote:

> We are running the sql via a web application and once the data has
> been rendered, we wanted to administer clean up.

This is a good thing.

> On the web it's common practice to discard our objects (recordsets,
> connections, etc).

Yes, the objects you mention are good to clean up.

> It's my understanding that the data in GTT's is deleted once the
> session is done, but the schema will persist. These schemas will
> persist in the temp space...Is that correct?

You are thinking of #tmps. In SQLServer or Sybase, you shouldn't clean
them out either. For these vendors, you should create them at the
beginning of the life of the connection in the pool. Your cleanup step
should delete the data out of them, but not drop them.

An Oracle GTT exists as a single table for all connections. Treat it
just like all other tables. Create it with the chunk of ddl scripts
that are used to create all the persistent tables. The data either goes
away on commit or stays around on commit based on your creation
statement. Your cleanup step for these should be an issuance of a
commit on the connection in the pool.

--
Galen Boyer

juan wrote:
> On Jun 11, 5:56 pm, DA Morgan <damor...@psoug.org> wrote:
>> juan wrote:
>>> Good Afternoon,
>>> I am hoping someone can help me out. I want to drop a global
>>> temporary
>>> table in classic asp after my recordset has completed printing out
>>> it's rows.
>> By doing so you are defeating the entire point have having a global
>> temporary table. Do you understand what they are? In Oracle?
>>
>> The docs are at:http://tahiti.oracle.com
>>
>> If you need one ... you shouldn't be dropping it.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor...@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
> Daniel,
>
> I've tried to respond several times. I am hoping that one of these
> transmissions will go through.
>
> We are running the sql via a web application and once the data has
> been rendered, we wanted to administer clean up.

This may make sense in SQL Server or other Ingres based architectures
but it is bad practice in Oracle. You should not be building and
dropping objects in a session: Essentially ever.

I am really concerned that you don't understand what a GTT is. Have
you read the docs? Do you understand where they are built and how
they work? Do you understand that what you are trying to do is beat
the system to death while depriving the optimizer of the information
it need to do a good job?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 12, 9:07 pm, Galen Boyer <galen_bo...@yahoo.com> wrote:
> On Tue, 12 Jun 2007, juan.ocegu...@nav-international.com wrote:
> > We are running the sql via a web application and once the data has
> > been rendered, we wanted to administer clean up.
>
> This is a good thing.
>
> > On the web it's common practice to discard our objects (recordsets,
> > connections, etc).
>
> Yes, the objects you mention are good to clean up.
>
> > It's my understanding that the data in GTT's is deleted once the
> > session is done, but the schema will persist. These schemas will
> > persist in the temp space...Is that correct?
>
> You are thinking of #tmps. In SQLServer or Sybase, you shouldn't clean
> them out either. For these vendors, you should create them at the
> beginning of the life of the connection in the pool. Your cleanup step
> should delete the data out of them, but not drop them.
>
> An Oracle GTT exists as a single table for all connections. Treat it
> just like all other tables. Create it with the chunk of ddl scripts
> that are used to create all the persistent tables. The data either goes
> away on commit or stays around on commit based on your creation
> statement. Your cleanup step for these should be an issuance of a
> commit on the connection in the pool.
>
> --
> Galen Boyer

Thanks Galen.

On Jun 12, 9:08 pm, DA Morgan <damor...@psoug.org> wrote:
> juan wrote:
> > On Jun 11, 5:56 pm, DA Morgan <damor...@psoug.org> wrote:
> >> juan wrote:
> >>> Good Afternoon,
> >>> I am hoping someone can help me out. I want to drop a global
> >>> temporary
> >>> table in classic asp after my recordset has completed printing out
> >>> it's rows.
> >> By doing so you are defeating the entire point have having a global
> >> temporary table. Do you understand what they are? In Oracle?
>
> >> The docs are at:http://tahiti.oracle.com
>
> >> If you need one ... you shouldn't be dropping it.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor...@x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org
>
> > Daniel,
>
> > I've tried to respond several times. I am hoping that one of these
> > transmissions will go through.
>
> > We are running the sql via a web application and once the data has
> > been rendered, we wanted to administer clean up.
>
> This may make sense in SQL Server or other Ingres based architectures
> but it is bad practice in Oracle. You should not be building and
> dropping objects in a session: Essentially ever.
>
> I am really concerned that you don't understand what a GTT is. Have
> you read the docs? Do you understand where they are built and how
> they work? Do you understand that what you are trying to do is beat
> the system to death while depriving the optimizer of the information
> it need to do a good job?
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel,

Yes, I do know what a GTT is and what they are used for. Thanks for
taking time to respond.

On Jun 12, 4:01 pm, sybra...@hccnet.nl wrote:
> On Tue, 12 Jun 2007 13:48:28 -0700, juan
>
> <juan.ocegu...@nav-international.com> wrote:
> >It's my understanding that the data in GTT's is deleted once
> >the session is done, but the schema will persist. These schemas will
> >persist in the temp space...Is that correct?
>
> Not correct. The *table* will persist. Please do not confuse a table
> with a schema
>
>
>
> >What is the best practice in removing these unused GTT schemas?
>
> Tie your arms behind your back to avoid you are wasting your time in
> dropping them.
>
>
>
> >Thank you in advance,
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Thanks for taking time to respond.