To hold data only in your session or transaction life, you can create temporary table which data will be deleted after completion of session or transaction. There’re some specific features of temporary tables. In this post I show some of them on examples

* Each session has its own data

SQL> CREATE USER user1 IDENTIFIED BY test;

User created.

SQL> CREATE USER user2 IDENTIFIED BY test;

User created.

SQL> grant connect, resource, create public synonym to user1;

Grant succeeded.

SQL> conn user1/test
Connected.
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number) on commit delete rows;

Table created.

SQL> grant select, insert, update, delete on tbl_temp to user2;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM tbl_temp FOR tbl_temp;

Synonym created.

SQL> INSERT INTO tbl_temp VALUES(1);

1 row created.

SQL> SELECT * FROM tbl_temp;

        ID
———-
         1

 

From the second session connect with USER2 and insert some data to temporary table

SQL> conn user2/test
Connected.
SQL> select * from tbl_temp;

no rows selected

SQL> insert into tbl_temp values(333);

1 row created.

SQL> select * from tbl_temp;

        ID
———-
       333

SQL>

 

Now switch to the first session and select data from tbl_temp

SQL> SELECT * FROM tbl_temp;

        ID
———-
         1

SQL>
As you see, each session has its own data for the same temporary table

 

* If temporary table is truncated from one session, only data of that session is truncated. Meanwhile, other users will be able to see their data in their own temporary table
Now, TRUNCATE tbl_temp table from the first session and query it from another session:

SQL> truncate table tbl_temp;

Table truncated.

SQL> SELECT * FROM tbl_temp;

no rows selected

SQL>

//Query the table from the second session:

SQL> select * from tbl_temp;

        ID
———-
       333

 

* If you issue command COMMIT, you’ll lose all your data because you’ve create the table using ON COMMIT DELETE ROWS functionality

SQL> commit;

Commit complete.

SQL> select * from tbl_temp;

no rows selected

SQL>

* Indexes created on temporary tables are also temporary. Let’s see it from the following example

SQL> show user
USER is “SYS”
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number) on commit preserve rows;
Table created.

SQL> create index idx_tbl_temp on tbl_temp (id);

Index created.

SQL> select num_rows, temporary from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS T
———- –
         0 Y
        
SQL>

SQL> BEGIN
  2  FOR i IN 1..100 LOOP
  3  INSERT INTO tbl_temp VALUES(i);
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(1) from tbl_temp;

  COUNT(1)
———-
       100

SQL> analyze index idx_tbl_temp compute statistics;

Index analyzed.

SQL> select num_rows from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS
———-
       100
       
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> select num_rows from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS
———-
       100
        
SQL> select count(1) from tbl_temp;

  COUNT(1)
———-
         0

SQL> analyze index idx_tbl_temp compute statistics;

Index analyzed.

SQL> select num_rows, temporary from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS T
———- –
         0 Y

Exclusive Interview with Syed Jaffar Hussain

 

He’s Senior Oracle DBA with over 16 years of IT experience which also includes above 8 years of production Oracle DBA exposure. Presently the only Oracle 10g Certified Master in Saudi Arabia with Oracle RAC Expert, Oracle 8i, 9i and 10g OCP DBA and Oracle ACE credentials.Past one year and so, he has been largely working with Oracle RAC environemnts that inlcudes successful 8 node production, 6 node development RAC setups on HPUX Itaninum with EMC DMX SAN Storage. He’s one of the regular contributor at Oracle forums, oracle freelist group and occasionally writes up at his blog. He’s currently engaged in writing an Oracle 11g RAC book

 

Exclusive Interview with Tim Hall

Tim Hall is an Oracle Certified Professional (OCP) DBA (7, 8, 8i, 9i, 10g, 11g), Oracle Certified Associate (OCA) PL/SQL Developer, Oracle ACE Director and was chosen as Oracle ACE of the Year 2006 by Oracle Magazine Editors Choice Awards. He has been involved in DBA, design and development work with Oracle databases since graduating from university in 1994.

 He has gained a wide knowledge of the Oracle software stack and has worked as a consultant for several multi-national companies on projects ranging from real-time control systems to OLTP web applications.

 Since 2000 I’ve published over 350 articles on his website (www.oracle-base.com) covering a wide range of Oracle features. He has also published two books, “Oracle Job Scheduling” and “Oracle PL/SQL Tuning“.

 

Exclusive Interview with Laurent Schneider

Laurent Schneider is one of the most respected authors in Oracle technology, with many years of experience as a Systems Engineer and Database Engineer.

Laurent achieved the highest level of DBA certification in 2004, being the first Oracle Certified Master in Switzerland and recipient of the prestigious Oracle Technology Network ACE trophy.  
Laurent has over a decade of expertise in development – specializing in data warehousing and database modeling as well as database administration. He currently works for a successful Swiss bank as application architect/developer.

In his spare time, Laurent enjoys studying Chinese Chess strategy and has won the Swiss Championship. He lives on the sunny side of the Uetliberg with his wife Bertille, his daughter Dora and his son Loïc.

He is the author of the book “Advanced Oracle SQL Programming“

Tim Hall is an Oracle Certified Professional (OCP) DBA (7, 8, 8i, 9i, 10g, 11g), Oracle Certified Associate (OCA) PL/SQL Developer, Oracle ACE Director and was chosen as Oracle ACE of the Year 2006 by Oracle Magazine Editors Choice Awards. He has been involved in DBA, design and development work with Oracle databases since graduating from university in 1994.

 He has gained a wide knowledge of the Oracle software stack and has worked as a consultant for several multi-national companies on projects ranging from real-time control systems to OLTP web applications.

 Since 2000 I’ve published over 350 articles on his website (www.oracle-base.com) covering a wide range of Oracle features. He has also published two books, “Oracle Job Scheduling” and “Oracle PL/SQL Tuning“.

 Here is our interview:

 

 

Could u please provide answer to the following questions as follows:

1.      Brief information about yourself and your family

 I’m a DBA/Developer and have been working with Oracle for approximately 15 years.

 

 2. Your education

I did a PhD in Molecular Biology before getting a job in IT.

 

3. Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

I got my first job with Oracle completely by accident. I had no knowledge of the company or their products. I didn’t even really know what an RDBMS was.

 

4. What was the motive behind to prefer Oracle? Who you have been influenced by?

Oracle is what I’ve always done. I’ve worked with other database products (SQL Server, mySQL, DB2, MS Access), but nowhere near as much as I’ve worked with Oracle, so I guess I’m a little biased.

 

5. What would your preference of profession if not Oracle?

If I knew I would do it.

 

6. What motivates you in your job?

I’m interested in technology. I like to play around with things. Sometimes the day job can get a little mundane, but the technology always keeps you on your toes.

 

7. Do you give lectures on Oracle?

Over the last years I’ve been speaking at conferences all over the world and I’ve been doing some 2 day PL/SQL workshops in Europe for Oracle University as part of their Celebrity Series.

 

8. Have you authored any book in Oracle?

I’ve written 2 books (Oracle Job Scheduling & Oracle PL/SQL Tuning).

  

9. Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

I mostly read when I’m traveling. Over the last couple of years I’ve read the Vampire Chronicles series by Anne Rice and the Discworld series by Terry Prachett.

 

 10.  What do you think on OTN forums?

 I don’t visit the OTN forums too often. I have my own forum so that keeps me fairly busy.

 

11.  Do you refer to the documentation? And how often does it happen?

I refer to the documentation constantly. When I have a question, it’s the first place I go. When I answer a question, I check my answer against the documentation before i post it.

 

12.  What is the biggest mistake you have ever made during your DBA career?

The longer you work with a product, the easier it is to start thinking you know it inside out. Then one day someone who knows nothing about it asks you a questions and you are lost for an answer. The biggest mistake is when you start thinking you know it all. I think we all go through a period where we think we are bulletproof. With age and experience you get  a bit more humble and sit back and smile at the new kids on the block who are making all the waves.

 

13.  What was your greatest achivement as an Oracle DBA?

 My greatest achievement is the fact I am still a techie after 15 years. Many really good technical people lose their desire and move out of the technical roles into management. Being a techie takes a lot of work. It would be far easier to move into a less technical role, but that’s not what I’m about.

 

14.  What is your priority to manage the challenges you face?

 Whenever I try to plan anything, life throws a spanner in the works. So now I never plan anything big in my life. I let things happen to me. The challenge is to keep an open mind so you don’t talk yourself out of opportunities.

 

15. How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

 It’s impossible to be objective about your own achievements. I don’t feel successful or unsuccessful. I am just me, doing what I do.

 

16. What are your best skills which make you differ from others?

I guess I am quite driven when I’m interested in something. I tend to throw myself into things. I’m not obsessed by money or possessions. I just want to be involved.

 

17. What’s your major weakness?

I find it hard to motivate myself to do things I’m not interested in. It takes a lot of mental effort to motivate me to cut the grass in my garden.

 

18.  Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

If I’m interested in something I never question my motives. If something goes wrong it is almost fun as it leads me down a new path.
19.  What is the next success you would like to attain and your efforts to this end?

I have no plans or desires. I just want to carry on doing what I want to do, when I want to do it.

 

20.  How do you balance your daily life with your career?

I have never considered trying to balance anything. Things just fall into place if you let them.

 

21.  Please describe your one day summary of activities?

At the moment I am traveling a lot, so there is never a normal day. I might be teaching, speaking at a conference, traveling, or just at home playing with the computer.

When I’m doing regular work, my day is like every other DBA/Developer in the world.
 

22.  How many hours do you work and sleep in a day?

Every day is different. I’m on the computer from the time I get up until the time I go to bed, unless something forces me not to be.

 

23.  Where and how do you spend your daily, weekly and annual holidays?

 When I’m not in front of the computer I visit my family and do some exercise (Swimming, Yoga, Karate, Gym)

 

24.  Do you think about Oracle during vacations?

If possible I’m online during holidays. I always check my mails and answer questions on my forum if I can.

 

25.  Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?

I like swimming, Yoga, Karate and I go to the gym most weeks. Sometimes it’s only possible to solve problems if you can distance yourself from them. Exercise distracts your conscious mind so your subconscious can do the work for you.

 

26.  What’s the best funny event you have ever faced in your life?

I find almost everything amusing. My typical response to most situations is to make fun of them and laugh.

 

27.  Do you keep pets? If so, the name please? 

I like animals in their natural surroundings. I don’t really agree with the concept of pets. I don’t have a problem with others having pets. Each to their own. 

 

28.  What’s your favorite meal and non-alcoholic drink?

If it’s bad for me I like to eat it. If it’s good for me, I probably don’t like it.

 

29.  What foreign languages do you know?

I only speak English. Most countries where English is the first language are pretty rubbish at learning other languages. It’s the curse of speaking such a widely available language.

 

30.  What’s your average typing speed?

I have no idea. I type quite fast, but if I think about it I will slow down to a crawl.

 

31.  Have you ever get involved in politics?

I have the unenviable trait of always seeing both side to the story. I would make a terrible politician.

 

32.  What are your hobbies? 

Playing with technology.

 

33. How do you spend your free time?

Playing with technology.

 

34.  What’s your biggest ambition?

I never think about that sort of thing. I just do stuff.

 

35.  What would be your advice to the beginners in Oracle?

Read the manuals. Always check everything people say to you. Things change and most people aren’t too good at adapting to change.

 

36.  Would you like your children to follow in your footsteps or take a different path in life?

I have no children. If I ever have any I want them to do what they want to do, not what other people expect them to do.

 

37.  Do you have any followers of you?

I guess lots of people know who I am because of my website and the Oracle ACE Director program, but I’m just a regular guy. Followers implies there is something to follow.

 

38.  What is your vision on the future of Oracle?

 
Oracle will continue to expand and every year my skills will be a smaller and smaller subset of the total product set. That’s the way it should be.

 

39.  Could you please take a photo in your office near to your desktop?

No way. My home office is a mess. I’m overly neat at work, but at home I’m a slob.

Here, I’ve collected all my “Step by Step Guides” which help you to understand basic concepts very easily

Install VMware – Step by step

Installing CentOS on VMware – Step by Step 

Step by Step Installing Oracle Database 10g Release 2 on Linux (CentOS) and AUTOMATE the installation using Linux Shell Script

Create Database Manually – Step by Step instruction

Last week, I’ve taken my first Interview from world-known Oracle Expert, Laurent Schneider 

 Laurent Schneider is one of the most respected authors in Oracle technology, with many years of experience as a Systems Engineer and Database Engineer.

Laurent achieved the highest level of DBA certification in 2004, being the first Oracle Certified Master in Switzerland and recipient of the prestigious Oracle Technology Network ACE trophy.  
Laurent has over a decade of expertise in development – specializing in data warehousing and database modeling as well as database administration. He currently works for a successful Swiss bank as application architect/developer.

In his spare time, Laurent enjoys studying Chinese Chess strategy and has won the Swiss Championship. He lives on the sunny side of the Uetliberg with his wife Bertille, his daughter Dora and his son Loïc.

He is the author of the book “Advanced Oracle SQL Programming”

Thank you Laurent for your time and consideration

Here’s our Interview :

Could u please provide answer to the following questions as follows:

• Brief information about yourself and your family

I was born in Geneva, Switzerland in 1971. At that time Unix was a baby and Oracle not even existed.

After my studies I move to Zurich to start working in Software Engineering.

I have two kids, Dora (7) and Loïc (6).

• Your education

I have done my college in Geneva. Later I learned COBOL and other oddities in a specialized school to obtain the diploma of Programmer Analyst in Business Computing

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

I was a Unix system admin and there was a vacancy as Data warehouse project leader in Zurich Financial Services. This was in 2000. I achieved the OCP 8.0 in the same year and 4 years later, I was the first OCM in my country

• What was the motive behind to prefer Oracle? Who you have been influenced by?

I did some database programming in 1986, with Ashon-Tate Dbase 3+. It was about managing the library of my chess club and later the various teams. The program has been used for maybe 10 years.

In the middle nineties, I did some clipper in a non-profit organization to manage donations for Rwanda.

In 2000 for a mobile company I wrote a program with Sybase as database system. It was about retrieving financial quotes from your mobile.

Oracle University was quite effective, passing a dozen of OCP, OCM, OCE, OCA helped to gain a good overview of the features.

Thomas Kyte and Steven Feuerstein have been my Oracle heroes this decade.

• Do you give lectures on Oracle?

Yes, I have been speaking in multiple locations, inclusive San Francisco, Stockholm, Baton Rouge and Zurich. I mostly spoke SQL MODEL – a 10g new feature and XML in the SELECT query.

• Have you authored any book in Oracle?

Yes, one book called Advanced Oracle SQL Programming, the expert guide to writing queries, ISBN 0977671585. It is mostly a book for developers and anyone who want to write queries, join, aggregation and analytics.

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

On my bookshelf I have started to read Applied Mathematics for Database Professionals from Lex de Haan and Toom Kooppelaars. This was my price for winning the SQL Competition from Lucas Jellema. I have just started the two 11g books I have. I received Oracle 11g new features from the co-author and my best friend Lutz Hartmann. In addition I started the book Beginning Oracle Database 11g Administration that Iggy Fernandez sent me for finding the 1^st solution in the NCOUG competition

• What do you think on OTN forums?

I wrote more than 5000 posts. I am no longer a regular, I am currently quite busy with my new job. It is a large community which could be very useful if you have problem with an Oracle Product…

• Do you refer to the documentation? And how often does it happen?

All the time… and I send correction too or bug to Metalink. Just check the contributor list in the SQL book

• What was your greatest achievement as an Oracle DBA?

My Blog, my ACE trophy, my OCM certificate and my book. Moreover, the trust and respect from my peers

• What is the next success you would like to attain and your efforts to this end?

I want to obtain investment banking skills to be able to understand what I am currently programming. I am looking forward for more training.

I wish I could do same language course to improve my pronunciation in English

• How do you balance your daily life with your career?

At home, I am not doing Oracle. I am there for the kids. In the evening, I can have a beer with a colleague or two, and I play chess

• Where and how do you spend your daily, weekly and annual holidays?

With family, we go swimming, walking and cycling in summer, and skiing in winter

• What’s the best funny event you have ever faced in your life?

The free hugs campaign in New Orleans

• What’s your favorite meal and non-alcoholic drink?

I like pasta with tomatoes sauce. I like Swiss fondue. I like German beer and Swiss wine.

• What foreign languages do you know?

English and German. My mother tongue is French.

 

• What are your hobbies? 

Chess, Adventures with kids

• What would be your advice to the beginners in Oracle?

Buy a book from Tom Kyte and a book from Steven Feuerstein and start learning.

• Would you like your children to follow in your footsteps or take a different path in life?

My girl wants to an astronaut and my boy is unsure if he wants to be a clown or a policeman

• Do you have any followers of you?

I enjoy people sending me comments on my blog http://laurentschneider.com

• What is your vision on the future of Oracle?

Promising, I suppose Oracle will remain a key player in the IT industry for a few decades

• Could you please take a photo in your office near to your desktop?

No, I am not allowed to do this; it is against the bank secrecy.

• What do you think I have forgotten to ask you?

Thanks for the interview, and all the best to your readers