Answer / sreemanth reddy

hi
You can not specify a tablespace with global temporary
tables. GTT's are built in the TEMP tablespace.
Global temporary tables have three major benefits:

1. Non-interference between private sets of data.

2. Ease of getting rid of 'scratch' data. In a heap table
you either rollback, or delete it. But in a GTT, you can
truncate explicitly, without affecting anyone else (or allow
the implicit "truncate on commit / exit" effect to do
the same thing).

3. Decreased redo generation as, by definition, they are
non-logging.

However:

Mixing temporary tables (GTTs) with permanent tables usually
causes some grief to the CBO. It has no information
about the number of rows in the GTT, and therefore guesses
(badly).

Even if you analyze table .. or
dbms_stats.gather_table_stats() you don't get stats on the
temporary table.

Set the init parameter dynamic_sampling to at least 2 for
GTTs to be sampled at run-time.

Note: All DDL includes two implicit commits so any rows in a
GTT specified with ON COMMIT DELETE ROWS will empty the
table.

What are all the ddl commands?

0 Answers  

---

How do you modify a table in sql?

0 Answers  

---

How to find 3rd highest salary of an employee from the employee table in sql?

0 Answers  

---

What is user define exception and example

1 Answers   HP, KP,

---

I have a CURSOR then why we need BULK COLLECT again?

1 Answers  

---

---

How do you write a subquery?

0 Answers  

---

Does sql*plus have a pl/sql engine?

0 Answers  

---

Can you do multiple joins in sql?

0 Answers  

---

What is pseudo column ?

6 Answers   ABB,

---

what is csv? : Sql dba

0 Answers  

---

What is orm in sql?

0 Answers  

---

How will you debug your procedure? If your procedure is around 2000 lines and the expected output is 10 and we get only output 5.So how will you debug it? Somebody pls give the correct answer?

2 Answers   Fidelity,

---