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?

 

 

 

 
 

 

 
 

 

 
 
 

 

Answer: The number of columns  in a table can range from 1 to 254. 
 
 

 

 
 
 
Question:  What is clustered index?

 

Answer:  In an indexed cluster, rows are stored together based on their cluster key values. Can not apply for HASH. 
 
 

 

 
 
 
Question:  What are attributes of cursor?

 

Answer:    %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT 
 
 

 

 
 
 
Question: Can you use select in FROM clause of SQL select?
Answer:  Yes.