Struct with this question

[Vani]

Q. 2 : The following is a DML statement keyword

1. EXPLAIN PLAN
2. TRUNCATE
3. ANALYZE
4. COMMENT

Ans:2 ,can anyone explain,how is it?

[victorv]

What don't you understand ? TRUNCATE is a DML statement. It's similar to DELETE (it removes all records from a table), which means, it manipulates data.

[Mr.Hanky]

Actually no, truncate is NOT DML, i think it is DDL and there is a huge difference between truncate and delete, in my opinion.
Truncate can not be rolled back and will free up all the space that the table occupies as well as it's index. Delete does not do this and can be rolled back.

In this case EXPLAIN PLAN is the example of DML.

"The EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. Therefore, Oracle does not implicitly commit the changes made by an EXPLAIN PLAN statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, you must commit the transaction containing the statement. "

"The TRUNCATE command is used to remove all data from a table without generating a rollback. A truncate is not a transaction that can be rolled back and a commit is not required afterwords. "


MH



[Edited by Mr.Hanky on 03-13-2002 at 02:18 PM]

[Mr.Hanky]

I really hate to contradict myself but here is a quote from the quest knowledge base.

"The actual command elements that form the DML portion of SQL are few in number; they are: INSERT, UPDATE, DELETE, SELECT, and TRUNCATE. Oracle also contains operators (unary and binary) and functions. This topic addresses Oracle operators which are used to construct parts of the DML commands. "

I guess we are back to square one huh?

MH

[TheSlob]

Mr. Hanky. No I Think you're correct. Truncate is a DDL.

check this out.

http://technet.oracle.com/doc/server...9/introsql.htm

Data Manipulation Language Statements

CALL

DELETE

EXPLAIN PLAN

INSERT

LOCK TABLE

SELECT

UPDATE

Data Definition Language Statements
...
...
...
NOAUDIT

RENAME

REVOKE

TRUNCATE
...
...

[Mr.Hanky]

AHA, that is the key phrase!!

"IMPLICITLY COMMIT"

"Data manipulation language (DML) statements query and manipulate data in existing schema objects. These statements do not implicitly commit the current transaction.

Oracle implicitly commits the current transaction before and after every DDL statement. "

Thank goodness we cleared that one up.

MH

[marist89]

Don't you hate when you think you're wrong, but you're wrong?

[Mr.Hanky]

Hey Jeff, where ya been? Yep, makes me wonder how many other more serious errors are in the Quest knowledge Xpert.

I'll stick with technet

MH

[Vani]

Thanx Mr.Hanky,Mr.Jeff and Mr.Theslob.
I will go with Explain Plan as the correct answer .
This is one of the question I got from Dbasupport OCP Zone.

I am getting prepared for OCP 1Z0-023.Hope I get assistance from you all

[Vani]

Hi,
Here is one more head proning topic.
With respect Oracle 8i

If the redo log file size is 1MB,that is 2000 OS blocks; if the OS block size is 512 KB,then setting the parameter log_checkpoint_interval to 500 will initiate a checkpoint when the redo log file is 1500 blocks full.

Now my question:Is there any formula to caluculate it?.I tried most possible methods to get 1500 and Hmmmm,I could not succeed.

Help me ....