RE: Strange Temp Table Question

Mikhail,

   That’s interesting. They are not in a read-only tablespace. The issue is that they are in the user tablespace and one of the temporary tables is taking up 2.6G which we’d like to free up.

SQL> select owner, segment_name, segment_type, tablespace_name   2 from dba_segments
  3 where owner = 'MCTR_OWNER'
  4 and segment_type = 'TEMPORARY'
  5 order by 2
  6 ;

OWNER

MCTR_OWNER
5.364213
TEMPORARY MCTR_DATA SQL> Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: Mikhail Velikikh <mvelikikh_at_gmail.com> Sent: Friday, February 28, 2020 8:25 AM To: Scott Canaan <srcdco_at_rit.edu>
Cc: oracle-l_at_freelists.org
Subject: Re: Strange Temp Table Question

CAUTION: This message came from outside RIT. If you are unsure about the source or content of this message, please contact the ITS Service Desk at 585-475-4357 or servicedesk_at_rit.edu<mailto:servicedesk_at_rit.edu> before clicking links, opening attachments or responding.

Hi Scott,

I think you encountered segments with segment_type='TEMPORARY'. Those are usually created when some CTAS/ALTER TABLE MOVE/ALTER INDEX REBUILD operation is run.

Could you show the dba_segments output for your temporary segments and check if those are in a read-only tablespace?

Please take a look at the next two MOS notes and the example below which is based on the second note:

Object's segment is Temporary (Doc ID 2531121.1) https://support.oracle.com/rs?type=doc&id=2531121.1

Temporary segments are not cleaned by SMON (Doc ID 793832.1) https://support.oracle.com/rs?type=doc&id=793832.1

SQL> create tablespace my_ts datafile size 100m;

Tablespace created.
SQL> create table my_table(x int);

Table created.
SQL> create index my_table_i on my_table(x) tablespace my_ts;

Index created.
SQL> insert into my_table values (123);

1 row created.
SQL> commit;

Commit complete.
SQL> alter tablespace my_ts read only;

Tablespace altered.
SQL> alter index my_table_i rebuild tablespace users;

Index altered.
SQL> _at_pt "select * from dba_segments where tablespace_name='MY_TS'"

OWNER                         : MVELIKIKH
SEGMENT_NAME                  : 136.130
PARTITION_NAME                :
SEGMENT_TYPE                  : TEMPORARY
SEGMENT_SUBTYPE               :
TABLESPACE_NAME               : MY_TS
HEADER_FILE                   : 136
HEADER_BLOCK                  : 130
BYTES                         : 65536
BLOCKS                        : 8
EXTENTS                       : 1
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
RETENTION                     :
MINRETENTION                  :
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
RELATIVE_FNO                  : 136
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
INMEMORY                      : DISABLED
INMEMORY_PRIORITY             :
INMEMORY_DISTRIBUTE           :
INMEMORY_DUPLICATE            :
INMEMORY_COMPRESSION          :
CELLMEMORY                    :


-----------------

Best regards,
Mikhail Velikikh

[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>

Virus-free. www.avast.com<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>

On Fri, 28 Feb 2020 at 12:56, Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> wrote: We have an Oracle 19c (19.5.0.0.0) database that has three temporary tables that start with “5.” as the table names (5.364109, 5.364163, 5.364213). They show up in dba_segments and dba_extents, but not in dba_objects or dba_tables. If we try to select from or describe them we get and ORA-04043 (object does not exist).

Two questions: 1) what created these? And 2) how do we get rid of them?

Thank you,

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

[https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>

Virus-free. www.avast.com<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>

--
http://www.freelists.org/webpage/oracle-l