Re: Strange Temp Table Question
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 28 Feb 2020 23:06:23 -0500
Message-ID: <CAMHX9JJH0sBx7zQygpEvOp+LCwBqQknA8MPw=P7oJ+jhdru+MA_at_mail.gmail.com>
Date: Fri, 28 Feb 2020 23:06:23 -0500
Message-ID: <CAMHX9JJH0sBx7zQygpEvOp+LCwBqQknA8MPw=P7oJ+jhdru+MA_at_mail.gmail.com>
This doesn't have to happen in a read only tablespace. RO tablespace is just an easy way to statically demonstrate a segment "left behind" without a corresponding object.
You could disable HWM brokering for inserts and each PX slave uses its own temp segment for the direct path load that later gets merged into one final segment in SEG$:
SQL> _at_sp hwm
Show SPFILE parameters from v$spparameter matching %hwm%
SID NAME VALUE ---------- ---------------------------------------- ----- * *_insert_enable_hwm_brokered FALSE* * *_px_hybrid_TSM_HWMB_load FALSE*
SQL> CREATE TABLE soe.tmp AS SELECT * FROM soe.orders WHERE 1=0;
Table created.
SQL> INSERT /*+ *APPEND ENABLE_PARALLEL_DML PARALLEL(4)* */ INTO soe.tmp SELECT * FROM soe.orders;
*(... in another session ...)*
SQL> SELECT * FROM dba_segments WHERE segment_type='TEMPORARY';
OWNER SEGMENT_NAME
PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------
------------------------------ ------------
SOE * 8.2280850 *
TEMPORARY
SOE * 8.2362386 *
TEMPORARY
SOE *8.2281874 *
TEMPORARY
SOE *8.2283922 *
TEMPORARY
The 8.2280850 just means "a range of blocks" in datafile #8, starting from block# 2280850.
-- Tanel Poder https://tanelpoder.com/conference On Fri, Feb 28, 2020 at 8:31 AM Scott Canaan <srcdco_at_rit.edu> wrote:Received on Sat Feb 29 2020 - 05:06:23 CET
> 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