DBA Blogs

database links

Tom Kyte - Tue, 2018-06-19 07:06
how can i create database links to access remote databases. please tell me the procedure of creating database links.
Categories: DBA Blogs

How to avoid repeated function call for multiple columns' values.

Tom Kyte - Tue, 2018-06-19 07:06
Hi I'm refactoring an old procedure that calls a function for determining whether passed in values consist of only characters allowed in the front end app on top of the database. The procedure has a cursor that gathers all records it needs to ...
Categories: DBA Blogs

PL/SQL Procedure - Catching "ORA - 01013 - User Requested Cancel of Current Operation"

Tom Kyte - Mon, 2018-06-18 12:46
It may be a silly question but I am wondering if there is any way to catch this error "ORA-01013 - User requested cancel of current operation" in a PL/SQL procedure. The requirement that I have is to update a database record before exiting when t...
Categories: DBA Blogs

Ambiguous overloading when parameter is null

Tom Kyte - Mon, 2018-06-18 12:46
<code></code>I have a package with an overloaded function, wich accepts one parameter (INTEGER / VARCHAR2) and returns a booblean indicating if exists a register with the correspondient column having the same value as the parameter. In tests, w...
Categories: DBA Blogs

JDBC thin dirver vs OCI driver

Tom Kyte - Mon, 2018-06-18 12:46
Hi Tom, i had some discussions with other colleagues about which driver should / could be used for connecting to an oracle database. I searched the web but found no sufficient answer for me (only a pretty old question from this site https://askto...
Categories: DBA Blogs

Replace characters in string

Tom Kyte - Mon, 2018-06-18 12:46
What's the best way to replace every character in string on random character and every number on other random number. I think about best performance. Input: MatijZ34 Output: sWirpt77
Categories: DBA Blogs

SQL functions in control file when direct load

Tom Kyte - Mon, 2018-06-18 12:46
Hello Tom, I have 100 million records to load to a table. I am sqlldr to do so. I have been using the conventional load but is taking long. In the control file I have SQL functions to substring and other data manipulations. My question is, can I use...
Categories: DBA Blogs

How to find time taken by query at each stage while being processed by db

Tom Kyte - Mon, 2018-06-18 12:46
Hey, We have some queries that perform too badly during load test. As per DBA the explain looks good. I want to know if there is a way DBA can monitor the time taken by query at each stage like parsing, executing, returning rows, etc? Thanks!
Categories: DBA Blogs

After logon on database

Tom Kyte - Mon, 2018-06-18 12:46
<code>CREATE OR REPLACE TRIGGER LOG_T_LOGON AFTER LOGON ON DATABASE DECLARE osUser VARCHAR2(30); machine VARCHAR2(100); prog VARCHAR2(100); ip_user VARCHAR2(15); BEGIN SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_addres...
Categories: DBA Blogs

Generate a date range

Tom Kyte - Mon, 2018-06-18 12:46
Hi tom, I have one question in which suppose i take two date range '10-jun-2014' and '10-jun-2018' then i want the output like 10-jun-2014 to 10-jun-2015 10-jun-2015 to 10-jun-2016 10-jun-2016 to 10-jun-2017 10-jun-2017 to 10-jun-2018 Can...
Categories: DBA Blogs

SQLERRM:ORA-06531: Reference to uninitialized collection

Tom Kyte - Mon, 2018-06-18 12:46
<code>Hi I am facing this error. my script is like below. please suggest: / create table address_test( Addr_id number, addr_cus_id number, street_name varchar2(100), town varchar2(100), county varchar2(100), sub_county_state_province varchar2...
Categories: DBA Blogs

Global Temporary Table in a PDB

Hemant K Chitale - Sun, 2018-06-17 10:45
Where and how is the space consumption for a Global Temporary Table when created in a Pluggable Database ?

In a 12c MultiTenant Database, each Pluggable Database (PDB) has its own Temporary Tablespace. So, a GTT (Global Temporary Table) in a PDB is local to the associated Temporary Tablespace.

Let me be clear.  The "Global" does *not* mean that the table is
(a) available across all PDBs   (it is restricted to that PDB alone)
(b) available to all schemas (it is restricted to the owner schema alone, unless privileges are granted to other database users as well)
(c) data is visible to other sessions (data in a GTT is visible only to that session that populated it)

The "global" really means that the definition is created once and available across multiple sessions, each session having a "private" copy of the data.
The "temporary" means that the data does not persist.  If the table is defined as "on commit delete rows", rows are not visible after a COMMIT is issued.  If the table is defined as "on commit preserve rows", rows remain only for the life of the session.  In either case, a TRUNCATE can also be used to purge rows.


Here, I connect to a particular PDB and create a GTT and then populate it

$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

Table created.

SQL>
$sqlplus hemant/hemant@PDBHKC

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 17 23:06:28 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Sun Jun 17 2018 23:02:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

Table created.

SQL>
SQL> select distinct sid from v$mystat;

SID
----------
36

SQL>
SQL> select serial# from v$session where sid=36;

SERIAL#
----------
4882

SQL>


Another session can see that the table exists (without any corresponding "permanent" tablespace) but not see any data in it.

SQL> select temporary, tablespace_name
2 from user_tables
3 where table_name = 'MY_GTT'
4 /

T TABLESPACE_NAME
- ------------------------------
Y

SQL> select count(*) from my_gtt;

COUNT(*)
----------
0


Let's look for information on the Temporary Tablespace / Segment usage(querying from the second session)

SQL> select sid, serial#, sql_id    
2 from v$session
3 where username = 'HEMANT';

SID SERIAL# SQL_ID
---------- ---------- -------------
36 4882
300 34315 739nwj7sjgaxp

SQL> select username, session_num, sql_id, tablespace, contents, segtype, con_id, sql_id_tempseg
2 from v$tempseg_usage;

USERNAME SESSION_NUM SQL_ID TABLESPA CONTENTS SEGTYPE CON_ID SQL_ID_TEMPSE
-------- ----------- ------------- -------- --------- --------- ---------- -------------
HEMANT 4882 92ac4hmu9qgw3 TEMP TEMPORARY DATA 6 3t82sphjrt73h

SQL> select sql_id, sql_text
2 from v$sql
3 where sql_id in ('92ac4hmu9qgw3','3t82sphjrt73h');

SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
92ac4hmu9qgw3
select serial# from v$session where sid=36


SQL>


So, SID 36 is the session that populated the GTT and identified it's own SID (36) and SERIAL# (4882), which we can see as the user of the Temporary Segment when querying from the second session (SID 300).

What about the size of the temporary segment populated by SESSION_NUM (i..e SERIAL#)=4882 ?
Again, querying from the second session.

SQL> select extents, blocks, sql_id, sql_id_tempseg 
2 from v$tempseg_usage
3 where session_num=4882;

EXTENTS BLOCKS SQL_ID SQL_ID_TEMPSE
---------- ---------- ------------- -------------
4 512 92ac4hmu9qgw3 3t82sphjrt73h

SQL>


Now, let's "grow" the GTT with more rows (and then query from the other session).

SQL> insert into my_gtt select * from my_gtt;

72638 rows created.

SQL>
SQL> l
1 select extents, blocks, sql_id, sql_id_tempseg
2 from v$tempseg_usage
3* where session_num=4882
SQL> /

EXTENTS BLOCKS SQL_ID SQL_ID_TEMPSE
---------- ---------- ------------- -------------
8 1024 gfkbdvpdb3qvf 3t82sphjrt73h

SQL> select sql_text from v$sql where sql_id = 'gfkbdvpdb3qvf';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
insert into my_gtt select * from my_gtt

SQL>


So, the increased space allocation in the Temporary Segment is from the growth of the GTT. Let's grow it further.

SQL> INSERT INTO MY_GTT select * from MY_GTT;

145276 rows created.

SQL> /

290552 rows created.

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

EXTENTS BLOCKS SQL_ID SQL_ID_TEMPSE
---------- ---------- ------------- -------------
29 3712 2c3sccf0pj5g1 3t82sphjrt73h

SQL> select sql_text, executions from v$sql where sql_id = '2c3sccf0pj5g1';

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
EXECUTIONS
----------
INSERT INTO MY_GTT select * from MY_GTT
2


SQL>


So, the growth of the GTT results in increased space allocation in the Temporary Segment.

What happens if I truncate the GTT ?

SQL> truncate table my_gtt;

Table truncated.

SQL>
SQL> select extents, blocks, sql_id, sql_id_tempseg
2 from v$tempseg_usage
3 where session_num=4882;

no rows selected

SQL>
SQL> select * from v$tempseg_usage;

no rows selected

SQL>


Temp Space is released by the TRUNCATE of the GTT.

I invite you to try this with a GTT created with ON COMMIT DELETE ROWS and see what happens before and after the COMMIT.

.
.
.

Categories: DBA Blogs

Sqlldr is throwing OCI.dll exception with Oracle 12.2 Instant Client

Tom Kyte - Fri, 2018-06-15 11:06
Hi, I have downloaded Oracle 12.2 Instant Client (both SQLPlus and Tools) and on my Window 7 64 Bit system from http://www.oracle.com/technetwork/topics/winx64soft-089540.html. I have unzipped the all files to C:\oracle122, along with by tnsname...
Categories: DBA Blogs

Converting rows to columns

Tom Kyte - Fri, 2018-06-15 11:06
Hi Tom, Hope you are good. I have a requirement where I need to display rows as columns. Suppose there are 2 rows with 4 columns each then the result should display 2*4 i.e, 8 columns. Is it possible just using SQL? Thanks
Categories: DBA Blogs

Oracle Goldengate

Tom Kyte - Fri, 2018-06-15 11:06
What is the advantage of Goldengate over Stream? Oracle Goldengate has high license cost compared to Streams. So, why an organization should use Goldengate for their data replication need and not Streams? Does Goldengate has advantage, which is wo...
Categories: DBA Blogs

What is the relationship of CPU, Memories against DB performances?

Tom Kyte - Fri, 2018-06-15 11:06
Hi Tom, Frequently I get asked quite a number of times when planning for a new server setup for the creation of databases. How much CPU cores should I get? How much Memories should I get. Normally I'll answer them, just get the highest cores & me...
Categories: DBA Blogs

DBMS_FILE_TRANSFER.PUT_FILE multiple "source_file_name"

Tom Kyte - Fri, 2018-06-15 11:06
Hi I am using Datapump to export dump file from a database and while exporting the dumpfile, I am splitting that dumpfile into multiple files. Now I want to transfer those files to another server using DBMS_FILE_TRANSFER.PUT_FILE. I know ...
Categories: DBA Blogs

UTL_FILE.FCOPY not working in FOR LOOP <file read error>

Tom Kyte - Fri, 2018-06-15 11:06
Hi There, I have a PIPELINED function which retrieves me filenames which I feed to UTL_FILE.FCOPY like below: <code>DECLARE PROCEDURE copy_var_templates (p_var_report_name st_string) IS lkv_template_dir CONSTANT st_string := 'T...
Categories: DBA Blogs

Side-effects when working with associative array in pl/sql

Tom Kyte - Fri, 2018-06-15 11:06
I've noticed strange side-effect when working with associative arrays in pl/sql. Basically, it appearts, that when element of the array is passed to procedure as "in out nocopy", then after procedure finishes, Oracle copies possibly updated element b...
Categories: DBA Blogs

Extracting attributes from JSON documents

Tom Kyte - Fri, 2018-06-15 11:06
Hi all, Have question in JSON array accessing along with normal columns like below, <code> (Reports : [( 'reportname': 'abc', 'Sort order':'abc', 'sortlabel':'name', 'columns' :[ ( 'component' : 'q_test1', ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs