Oracle 18C New Feature Private Temporary Table

What is oracle private temporary table? This video explains about the global temporary table, and private temporary table concepts.
A typo error in the slide, Please read "ON COMMIT DROP DEFINITION" instead of "ON COMMIT DELETE DEFINITION"

[@SivaAcademy]

A typo error in the slide, Please read as "ON COMMIT DROP DEFINITION" instead of "ON COMMIT DELETE DEFINITION"
Thanks Prem for pointing it out.

[@arijitroy7253]

Thnx a lot. But this naming convention governed by that parameter seemed little tough! Moreover, if multiple sessions are creating PTT by same name n structure, then wat and how dba_tables going to store/maintain the metadata session wise separately?

[@VarasPassion]

Explained very well...thank you dear :)

[@SivaAcademy]

Welcome bro

[@raghud541]

So Global temp tables are like normal tables which once created will be available always, but only data will be private to the sessions?

[@tejalpatkar2511]

In 19C facing issue for GTT table
Can you please Guide

[@12ravindrareddy]

Can we create index on GTT?

[@abhishekrahagude2707]

Siva could you please provide 2-3 use case for temporary table.

[@harendrajadon1980]

Any tutorials on oracle forms a d reports. Please share links if any. Thanks

[@sadhusehrawat9501]

Isn't Private Temporary Table very much similar to Local Temporary Table of 8i?

[@kunalkumar57]

SQL> create private temporary table t(sno number) on commit preserve definition;
create private temporary table t(sno number) on commit preserve definition
*
ERROR at line 1:
ORA-00903: invalid table name

[@spr9915]

Thanks again Siva!

[@SivaAcademy]

Welcome, thank you

[@ramchinthakayala2092]

Great content. Your help would be appreciated if you explain one example using this GTT in a procedure?

[@SivaAcademy]

Sure bro, I will post an example

[@premgona5587]

I found a typo in the video..
Just correcting it...
The two modes of Private temp tables are,
1) on commit PRESERVE definition
- session specific
2) on commit DROP definition
- table will be dropped automatically, once the commit or rollback issues

[@SivaAcademy]

Thanks for informing. will try to correct it.

[@premgona5587]

I have tried in the DB with DELETE but it didn't work... It worked with DROP option...

[@SivaAcademy]

I hope you are trying in 18C DB right?

[@SivaAcademy]

Send me yours scripts and steps you tried to my mail id, I will check and let you know tomorrow. ( mail to [e-mail])

[@premgona5587]

I have tried in oracle website... LIVESQL...
create private temporary table test_18c_private_temp_table
(id number, name varchar(10))
on delete drop definition;
insert into test_18c_private_temp_table
Values(101,'Prem');
select * from test_18c_private_temp_table;
commit;
Again, if we try to query the table it will throw an error saying
"table or view doesn't exists".

[@12ravindrareddy]

When to use GTT in real time?

[@SivaAcademy]

When ever you want to store temporary data for session

[@sunilk5008]

Please make video on SQL Injection in details

[@SivaAcademy]

Sure bro, please stay tuned

[@PriyankaKumari-ul7bv]

plz add code in your blog.

[@SivaAcademy]

@priyanka, Sure, will post soon.....

[@subhashisoracle3944]

mind blowing bro nice scenario

[@SivaAcademy]

i didn't do any great job bro, just trying to make everyone understand better as much as possible by me