Re: Strange Temp Table Question

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://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> <#m_3143456491841872922_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Fri, 28 Feb 2020 at 12:56, Scott Canaan <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 <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://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> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

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