We re-ran statistics gathering and these tables vanished, freeing up the space.
I’m assuming they had something to do with the 10pm Oracle job to gather
statistics that went awry a few nights ago.
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco@xxxxxxx<mailto:srcdco@xxxxxxx> | 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@xxxxxxxxx>
Sent: Friday, February 28, 2020 9:08 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxx<mailto:srcdco@xxxxxxx>> 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@xxxxxxx<mailto:srcdco@xxxxxxx> | 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@xxxxxxxxx<mailto:mvelikikh@xxxxxxxxx>>
Sent: Friday, February 28, 2020 8:25 AM
To: Scott Canaan <srcdco@xxxxxxx<mailto:srcdco@xxxxxxx>>
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxx<mailto:servicedesk@xxxxxxx> 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> @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@xxxxxxx<mailto:srcdco@xxxxxxx>> 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@xxxxxxx<mailto:srcdco@xxxxxxx> | 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>
