Oracle SQL / PLSQL Developer – Technical Question Answers – Part 5

Question: What are the Pct Free and Pct Used

Answer: Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table eg.:: Pctfree 20, Pctused 40

Question: What is Row Chaining

Answer: The data of a row in a table may not be able to fit the same data block.Data for row is stored in a chain of data blocks.

Question: What is a 2 Phase Commit
Answer: Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit Phase ::

Commit all participants to coordinator to Prepared, Read only or abort Reply

Question: What is the difference between deleting and truncating of tables

Answer: Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.

Question: What are mutating tables

Answer: When a table is in state of transition it is said to be mutating eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.

Question: What are Codd Rules
Answer: Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.

Question: What is the Difference between a post query and a pre query

Answer: A post query will fire for every row that is fetched but the pre query will fire only once.

Question: Deleting the Duplicate rows in the table

Answer: We can delete the duplicate rows in the table by using the Rowid

Question: Can U disable database trigger? How?
Answer: Yes. With respect to table
ALTER TABLE TABLE
[ DISABLE all_trigger ]

Question: What is pseudo columns ? Name them?
Answer: A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

   * CURRVAL
    * NEXTVAL
    * LEVEL
    * ROWID
    * ROWNUM

Question: How many columns can table have?