Re: Strange Temp Table Question

Scott,

Could you try to remove those segments using the DROP_SEGMENTS event?

EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (Doc ID 47400.1)
https://support.oracle.com/rs?type=doc&id=47400.1

In case it doesn't work, I recommend checking the trace file of your server process as well.

Best regards,
Mikhail Velikikh

On Fri, 28 Feb 2020 at 13:30, Scott Canaan <srcdco_at_rit.edu> wrote:

> 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
>
>
> --------------------------------------------------------------------------------
>
> SEGMENT_NAME
>
>
> --------------------------------------------------------------------------------
>
> SEGMENT_TYPE TABLESPACE_NAME
>
> ------------------ ------------------------------
>
> MCTR_OWNER
>
> 5.364109
>
> TEMPORARY MCTR_DATA
>
>
>
> MCTR_OWNER
>
> 5.364163
>
> TEMPORARY MCTR_DATA
>
>
>
> OWNER
>
>
> --------------------------------------------------------------------------------
>
> SEGMENT_NAME
>
>
> --------------------------------------------------------------------------------
>
> SEGMENT_TYPE TABLESPACE_NAME
>
> ------------------ ------------------------------
>
>
>
> 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 <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 <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://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> 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>
>
>
>

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