DBA Blogs

Retrieving input from a COM port, source being weighing scales

Tom Kyte - Wed, 2018-06-27 16:46
We are currently migrating a client from a Forms environment towards an APEX application. The client is used to using scales, the result of which is inserted into a Forms application: The user enters the cursor into the target cell in the Forms envir...
Categories: DBA Blogs

Wrong execution plan with domain indexes in or condition

Tom Kyte - Wed, 2018-06-27 16:46
Hello, I have a very simple query on a table with two columns indexed fulltext. If there are two AND conditions in the where clause, the optimizer uses a correct plan, taking advantage of the two indices. If instead I use the two conditions in OR, ...
Categories: DBA Blogs

Global Temporary Table -- revisited

Hemant K Chitale - Wed, 2018-06-27 09:00
Revisiting the previous case in a 12.2 PDB ....

(This time, the two sessions by "HEMANT" and "SYSTEM" have the Username as the SQL prompt)

In the previous blog post, I demonstrated how to check space allocation for a GTT.   But how does Oracle determine how much space to allocate ?

HEMANT>create global temporary table my_gtt_2
2 (id_number number, object_name varchar2(128))
3 on commit preserve rows
4 /

Table created.

HEMANT>
SYSTEM>select sid,serial# from v$session where username = 'HEMANT';

SID SERIAL#
---------- ----------
300 11923

SYSTEM>
SYSTEM>select username, session_num, sql_id, tablespace, contents, segtype, con_id, sql_id_tempseg
2 from v$tempseg_usage
3 /

no rows selected

SYSTEM>


So, the creation of a GTT does not allocate any space. A GTT definition is a logical definition and does not allocate space unless and until rows are inserted.

Let me insert a row and check the space.

HEMANT>insert into my_gtt_2 values (1, 'First Object');

1 row created.

HEMANT>commit;

Commit complete.

HEMANT>
SYSTEM>select username, session_num, sql_id, tablespace, contents, segtype, con_id, sql_id_tempseg
2 from v$tempseg_usage
3 /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
SESSION_NUM SQL_ID TABLESPACE CONTENTS SEGTYPE CON_ID SQL_ID_TEMPSE
----------- ------------- ------------------------------ --------- --------- ---------- -------------
HEMANT
11923 54zdzm1mrqpy9 TEMP TEMPORARY DATA 6 54zdzm1mrqpy9


SYSTEM>
SYSTEM>select sql_id, sql_text
2 from v$sql
3 where sql_id in ('54zdzm1mrqpy9')
4 /

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
54zdzm1mrqpy9
insert into my_gtt_2 values (1, 'First Object')


SYSTEM>
SYSTEM>select extents, blocks, sql_id, sql_id_tempseg
2 from v$tempseg_usage
3 where session_num=11923
4 /

EXTENTS BLOCKS SQL_ID SQL_ID_TEMPSE
---------- ---------- ------------- -------------
1 128 54zdzm1mrqpy9 54zdzm1mrqpy9

SYSTEM>


So, that is 1MB (128 blocks of 8KB each) for the initial extent.  Why is it so ?

SYSTEM>select extent_management, allocation_type, initial_extent, next_extent
2 from dba_tablespaces
3 where tablespace_name = 'TEMP'
4 /

EXTENT_MAN ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
---------- --------- -------------- -----------
LOCAL UNIFORM 1048576 1048576

SYSTEM>


Because, by default, a TEMPORARY TABLESPACE is created with 1MB Uniform Extents.

Can I change this ?

SYSTEM>create temporary tablespace small_temp
2 tempfile '/usr/tmp/small_temp.dbf' size 100M
3 extent management local uniform size 64K;

Tablespace created.

SYSTEM>select extent_management, allocation_type, initial_extent, next_extent
2 from dba_tablespaces
3 where tablespace_name = 'SMALL_TEMP'
4 /

EXTENT_MAN ALLOCATIO INITIAL_EXTENT NEXT_EXTENT
---------- --------- -------------- -----------
LOCAL UNIFORM 65536 65536

SYSTEM>
HEMANT>create global temporary table small_gtt
2 (id_number number, object_name varchar2(128))
3 on commit preserve rows
4 tablespace small_temp
5 /

Table created.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
2 from v$tempseg_usage
3 where session_num=11923
4 /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE BLOCKS SQL_ID SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
TEMP 128 fd8qcczn6avw6 54zdzm1mrqpy9


SYSTEM>
HEMANT>insert into small_gtt
2 values (1, 'First Object');

1 row created.

HEMANT>commit;

Commit complete.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
2 from v$tempseg_usage
3 where session_num=11923
4 /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE BLOCKS SQL_ID SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
TEMP 128 2j3pja8qjx1sd 54zdzm1mrqpy9

HEMANT
SMALL_TEMP 8 2j3pja8qjx1sd 2j3pja8qjx1sd


SYSTEM>
SYSTEM>select sql_id, sql_text
2 from v$sql
3 where sql_id in ('54zdzm1mrqpy9','fd8qcczn6avw6','2j3pja8qjx1sd')
4 /

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
2j3pja8qjx1sd
insert into small_gtt values (1, 'First Object')


SYSTEM>


(The previous two SQLs no longer present in the cache but we can see that "54zdzm1mrqpy9" is for the first GTT and "fd8qcczn6avw6" is for the second GTT)

Thus, my existing HEMANT session has two different Temporary Segment usages being reported. That in the TEMP tablespace is 1MB for the 1 row in MY_GTT_2 and that in the SMALL_TEMP tablespace is 64KB for the 1 row in SMALL_GTT.

If I TRUNCATE a GTT (or exit the session) space is released.

HEMANT>truncate table my_gtt_2;

Table truncated.

HEMANT>
SYSTEM>select username, tablespace, blocks, sql_id, sql_id_tempseg
2 from v$tempseg_usage
3 where session_num=11923
4 /

USERNAME
--------------------------------------------------------------------------------------------------------------------------------
TABLESPACE BLOCKS SQL_ID SQL_ID_TEMPSE
------------------------------ ---------- ------------- -------------
HEMANT
SMALL_TEMP 8 0zwdmqw9fpkjv 2j3pja8qjx1sd


SYSTEM>


Thus you can have
(a) multiple GTTs (with different definitions, e.g. one for SALES data processing and one for HR data processing)
(b) in multiple TEMPORARY TABLESPACES

The GTT definitions are visible across all sessions that are in the same USER login or have been granted privileges but the data in one session is *not* visible to another session, even if COMMITTed (unlike normal "Permanent" Tables)
.
.
.


Categories: DBA Blogs

why Results cached on one instance cannot be used by another instance

Tom Kyte - Tue, 2018-06-26 22:26
Want to know why ? Each node in a RAC configuration has a private result cache. Results cached on one instance cannot be used by another instance. However, invalidations work across instances. To handle all synchronization operations between RAC i...
Categories: DBA Blogs

Does PGA have a segment that functions like buffer_cache in SGA

Tom Kyte - Tue, 2018-06-26 22:26
In direct path read, blocks are read into PGA directly from disk. Where in PGA hold the blocks? Is it work area? How long will PGA hold them? If I have a full table scan and after a while the same session does another full table scan on the same ta...
Categories: DBA Blogs

Listener Log file issue

Tom Kyte - Tue, 2018-06-26 22:26
hi, please help me to block the particular user entries in the Listener log file.because the size of file was increasing unexceptionally. that user is frequently accessing the db server.i dont want to log the entries for that particular user. ...
Categories: DBA Blogs

How to populate a TABLE type based on an OBJECT TYPE

Tom Kyte - Tue, 2018-06-26 04:06
Hi All I'm trying to create a table type based on a object type, but when I try to populate it, I get an error. I copied the syntax from several places, and still, it seems i'm doing something wrong. Here's the code <code> CREATE OR REPLACE ...
Categories: DBA Blogs

CONTEXT vs CTXCAT

Tom Kyte - Tue, 2018-06-26 04:06
Hi Tom, First off all I am newbie to this Oracle Text thing so please bear with me. I am currently evaluating the possibility and interest in changing the use of context indexes to ctxcat indexes in a big database that I use (the text columns ...
Categories: DBA Blogs

Oracle Undocumented Parameter - _PUSH_JOIN_PREDICATE

Tom Kyte - Tue, 2018-06-26 04:06
Hi, I have a long standing question regarding one of the undocumented parameters of Oracle namely "_push_join_predicate". I personally experienced scenarios where a query (involving multiple big joins) would show up - "Pushed Predicate" in t...
Categories: DBA Blogs

Os Authentication :Eat the cake and have it too

Tom Kyte - Tue, 2018-06-26 04:06
Hi Tim, Thanx for all your help to the Oracle Community. Is it possible to have a DB user identified externally and also have a DB password so he/she can log both ways Authenticated from os as well as using account name and password FOR...
Categories: DBA Blogs

Version control of pl/sql codes

Tom Kyte - Tue, 2018-06-26 04:06
Hi, I wanted to know that if there is any way by which we can create versions of the latest copy of pl/sql stored procedures/packages each time we compile. If yes , could you please provide a sample code for the same as currently we do not have...
Categories: DBA Blogs

Backup and Restore records from DB Objects (Table)

Tom Kyte - Sun, 2018-06-24 15:26
Wanted to check if there is some easy way to create a backup of the tables/schema and use some script that will load back the data from the backup file. I have DBCS access so can run the sh script as well. Please let me know if there is a simpl...
Categories: DBA Blogs

Spot the diffs between two database schemas inside SQL and PL/SQL code

Tom Kyte - Sun, 2018-06-24 15:26
Hi : I have two Oracle databases (say, PROD and TEST), and in a given schema (present in both DBs) I must assure that the same SQL code (inside views) and/or PL/SQL code (triggers, procs, funcs, packages) exists, disconsidering the non-functional dif...
Categories: DBA Blogs

Making API calls from Oracle database

Tom Kyte - Sun, 2018-06-24 15:26
In our office, Environment 1: we have an oracle database with Oracle APEX installed. Environment 2: We have a PCI complaint application with some NON-PCI APIs exposed via Kong We want to call the Non-PCI APIs from oracle database and were to...
Categories: DBA Blogs

Cancelling long running queries

Tom Kyte - Sun, 2018-06-24 15:26
Dear Tom, I'm wondering about how a 'cancel' in Oracle works. For example, I have a long running query and I define a timeout on application level. Once the timeout is reached, the application sends a 'cancel'. What i observed is, that the canc...
Categories: DBA Blogs

materialized view problem while refreshing

Tom Kyte - Sun, 2018-06-24 15:26
Hi We have have an ORACLE 8.1.7 database on suse linux 7.2 and we have a materialized view with joins and created a primary key constraint on the mview. The refresh mode and refresh type of the created mview is refresh fast on demand. ...
Categories: DBA Blogs

converting TIMESTAMP(6) to TIMESTAMP(0)

Tom Kyte - Fri, 2018-06-22 08:26
Currently I have a column with datatype TIMESTAMP(6) but now i have a requirement to change it to TIMESTAMP(0). Because we cannot decrease the precision, ORA-30082: datetime/interval column to be modified must be empty to decrease fractional sec...
Categories: DBA Blogs

Mail Restrictions using UTL_SMTP

Tom Kyte - Fri, 2018-06-22 08:26
Hi Tom, I have a requirement to send email to particular domain mail id?s. But My Mail server is global mail server we can send mail to any mail ids. Is there any options in Oracle to restrict the mail send as global. For example: My mail host is...
Categories: DBA Blogs

Oracle Partner PaaS Summer Camps VIII - August 27 - 31, 2018

The Oracle PaaS Summer Camp is a one week training for cutting-edge software consultants, engineers and enterprise-level professionals. The #PaaSSummerCamp brings together the world’s leading...

We share our skills to maximize your revenue!
Categories: DBA Blogs

DBA_HIST_SQLSTAT and GV$SQL

Tom Kyte - Wed, 2018-06-20 19:46
Hi, I was trying to create a dashboard comparing historical executions and current executions of multiple SQL statements. I have noticed some differences between stats in GV$SQL and DBA_HIST_SQLSTAT. Could you please help us to understand below po...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs