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

Get a JSON from a SQL query

Tom Kyte - Fri, 2018-06-15 11:06
Hello! Just a question. Is it possible to write a query that returns a JSON code? If yes, could you give me a brief example? Thanks!
Categories: DBA Blogs

Index-Organized Materialized View with different primary key than the master table?

Tom Kyte - Fri, 2018-06-15 11:06
Dear Oracle-Team, we need a daily snapshot from the company's personal data for our software. For that reason we want to use an index-organized materialized view (with daily 'refesh complete'). Unfortunately there are two user id's for every empl...
Categories: DBA Blogs

How to replace old Exadata storage cells with the new X7-2 storage cells, without downtime

Alejandro Vargas - Fri, 2018-06-15 03:32

Lately I had to help various customers to replace their old storage cells with the new X7-2 ones.

There are huge benefits in doing this, the X7 has 25.5TB of flash, 12 x 10TB disks and 192 GB of DDR4 Memory.

X7-2 hardware

 

 

 

 

 

 

 

 

 

 

 

The question my customers asked the most was: Can we do the migration from our old storage to the new X7 without downtime and without risk?

The answer was: YES!

For doing this I've prepared and implemented a procedure that cover step by step how to migrate critical data, from production databases, while these databases are online, without downtime and without risk.

So far I've done two of these migrations in 2 weeks, one in Haifa and one in Istanbul.

The haifa migration was run on a machine without customers working on it. The Istanbul migration was implemented on a critical production database with full customer load.

Customer was very happy to see how the data was transferred in a fast and safe way without affecting the online business.

This is the power of both Exadata and ASM, a migration that only a few years ago may have imposed tremendous effort of planning and most probably required downtime, is now possible to be run online and without affecting the performance of critical workloads!

In summa

ry the steps of the migration includes the following:

1. Attach the new cells to Exadata and setup the ILOM to the final IP on customer network

2. Connect via ILOM to the new storage and setup the network to customer values

3. Upgrade the new storage servers to latest Exadata storage version

4. Customize the new servers to reflect customer preferences, mail alerts, writeback, asr server, etc

5. Create celldisks and griddisks to match existing diskgroups

6. Extend existing disk groups into the new cells and wait for first rebalance to complete

7. Once second rebalance completes, drop failgroups from the old cells and wait for second rebalance to complete

8. Once second rebalance complete flush the flashcache on the old cells, drop its griddisks and celldisks and shutdown the cells

9. Check the free space available on new cells and increase the size of the griddisks to use all of it as required

10. On the ASM instance resize all griddisks on the disk groups where you increase the size of the griddisks, wait for the third rebalance to complete.

 

 

 

 

Categories: DBA Blogs

Sizing clusters

Tom Kyte - Wed, 2018-06-13 22:46
My question is generally how to determine the size to set the <u>size</u> value in a create cluster statement. And specifically for a parent table 400 bytes wide with 15 million rows, and a child 120 bytes wide with 40 million rows. There may be a...
Categories: DBA Blogs

SQL with inline view Errors in 11g with ORA-00979 Not a Group BY expression, but runs in 12c

Tom Kyte - Wed, 2018-06-13 22:46
Hi Following SQL (correctly) errors with "ORA-00979 Not a Group BY Expression" when run on 11.2.0.4.0. But when run on 12.1.0.2.0, SQL runs without error and returns incorrect information for total_tablespace_size - All rows return 0.5 whereas i...
Categories: DBA Blogs

Leaving fake hints in queries

Tom Kyte - Wed, 2018-06-13 22:46
Hello, I'm creating a script to automatically generate plan reports usings DBMS_XPLAN.DISPLAY_CURSOR, and to do so I want to put in a standard comment in the table and query it via dba_source and v$sql. e.g. <code>select /* xplan_my_test_pkg01 ...
Categories: DBA Blogs

ORA-01704: string literal too long

Flavio Casetta - Wed, 2018-06-13 08:31
Categories: DBA Blogs

Active clone recover needed arc files

Tom Kyte - Wed, 2018-06-13 04:30
Oracle 12.2 I am running the following command: *.db_file_name_convert='/db1/ifddb1/dbf/','/db1/ifdtest1/dbf/' *.log_file_name_convert='/redo/ifddb1/redologs/','/redo/ifdtest1/redologs/' sqlplus ' / as sysdba ' <<EOT shutdown abort startu...
Categories: DBA Blogs

Use DBMS_OUTPUT or HTP depending call origin

Tom Kyte - Wed, 2018-06-13 04:30
Hey, I have this procedure: <code>procedure output(i_msg in varchar2) is begin dbms_output.put_line(i_msg); -- if call is from apex i want to use htp.p end;</code> Is it possible to switch the "output chanel" to htp when the caller is...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs