ORACLE Interview Questions -4

1. Which keyword should be used in the CREATE INDEX command to create a function –based index? A. CREATE FUNCTION INDEX… B. CREATE INDEX … ORGANIZATION INDEX C. CREATE INDEX… FUNCTION BASED … D. None of the above
2. Fill in the blank: The parameter MAXTRANS specifies the maximum number of concurrent transactions per ____________ _______.
3. The initialization parameter file has LOG_CHECKPOINT_ INTERVAL = 60; What does this mean?
4. You have specified the LOGFILE clause in the CREATE DATABASE command as follows. What happens if the size of the log file redo0101.log, which already exists, is 10MB?
5. Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the data files?
6. Which parameter specifies the number of transaction slots in a data block?
7. Describe a parallel server configuration.
8. When validating a constraint, why would you specify the EXCEPTIONS clause?
9. When you issue the command ALTER SYSTEM ENABLE RESTRICTED SESSION, what happens to the users who are connected to the database?
10. What is the difference between groupby and orderby?
11. If the application is running very slow? At what points you need to go about the database in order to improve the performance?
12. Which event trigger from the following cannot be created at the database level?
13. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE CLAUSE. What does this mean?
14. What packages are associated with the LogMiner utility?
15. Choose the right hierarchy, from largest to smallest, from this list of logical database structures.
16. Which prefix for the data dictionary views indicate that the contents of the view belong to the current user?
17. Profiles cannot be used to restrict. a. CPU time used b. Total time connected to the database c. Maximum time a session can be inactive d. Time spent reading blocks
18. What is accomplished when you issue the following statement? ALTER USER JOHN DEFAULT ROLE ALL; a. John is assigned all the roles created in the database b. Future roles granted to John will not be default roles c. All of John’s roles are enabled, except the roles with passwords d. All of John’s roles are enabled when connecting to the database
19. Which storage parameter is applicable only to rollback segments? A. PCTINCREASE B. MAXEXTENTS C. TRANASACTIONS D. None of the above.
20. Which of the following statements may require a temporary segment? a. CREATE TABLE b. CREATE INDEX c. UPDATE d. CREATE TABLESPACE

ORACLE Interview Questions -3

1. Which memory structure records all database changes made to the instance?
2. What are the recommended INITIAL and NEXT values for a temporary tablespace, to reduce fragmentation?
3. Which background process is responsible for writing the dirty buffers to the database files?
4. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
5. When does Oracle stop adding rows to a block?
6. Which parameter in the ORAPWD utility is optional?
7. Which environment variable or registry entry variable is used to represent the instance name?
8. Which optional component in the database creation process sets up functions and procedures to store, access, and analyze data needed for Geographical Information Systems (GIS)?
9. What clause is in the ALTER TABLE command is used to reorganize a table?
10. What is the minimum number of redo log files required in a database?
11. What is the maximum number of database writer processes allowed in an Oracle instance?
12. How can you fix the MAXEXTENTS reached error for a rollback segment?
13. If you need to have your archive log files named with the log sequence numbers as arch_0000001, arch_0000002, and so on (zero filled, fixed width), what should be the value of the LOG_ARCHIVE_ FORMAT parameter?
14. Which memory structures are shared? Name two.
15. Which component of the SGA contains the parsed SQL code?
16. A table is created with an INITRANS value of 2. Which value would you choose for INITRANS of an index created on this table?
17. What is row migration?
18. When a table is updated, where is the before image information (which can be used for undoing the changes) stored?
19. Which file is used to record all changes made to the database and is used only when performing an instance recovery?
20. Which password parameter in the profile definitions can restrict the user from using the old password for 90 days?
21. When a SELECT statement is issued, which stage checks the user’s privileges?
22. What will happen if ARCn could not write to a mandatory archive destination?
23. If you issue ALTER TABLESPACE USERS OFFLINE IMMEDIATE, which of the following statements is true? A. All data files belonging to the tablespace must be online. B. Does not ensure that the data files are available. C. Need not doe media recovery when bringing the tablespace online. D. Need to do media recovery when bringing the tablespace online.
24. How many codds rule should oracle satisfies
25. What is the recommended configuration for control files?
26. What is the process of upgrading oracle forms /reports from older version to new versions
27. What can cause the Snapshot too old error?
28. What does OFA stand for?

ORACLE Interview Question- 2

1. If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?
2. Which command should be issued before you can execute the CREATE DATABASE command?
3. How many control files are required to create a database?
4. How many data files can be specified in the DATAFILE clause when creating a database?
5. How many ARCn processes can be associated with an instance?
6. User SCOTT has opened a SQL * Plus session and left for lunch. When you queired the V$SESSION view, the STATUS was INACTVE. You terminated SCOTT’s session in V$SESSION?
7. The database block size is 4KB. You created a tablespace using the following command. CREATE TABLESPACE USER_DATA DATAFILE ‘C:/DATA01.DBF’ ; If you create an object in the database without specifying any storage parameters, what will be the size of the third extent that belongs to the object?
8. When does the SMON process automatically coalesce the tablespaces?
9. Which SHUTDOWN option will wait for the users to complete their uncommitted transactions?
10. Name the stages of processing a DML statement. What stages are part of processing a query?
11. What is the difference between object library and pl/sql library?
12. What are the valid status codes in the V$LOGFILE view?
13. Which data dictionary view provides the names of the control files?
14. How would you drop a tablespace if the tablespace were not empty?
15. Explain truncate with restore option with example
16. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
17. How do you make a database read-only?
18. Which command is used to enable the auto-extensible feature for a file, if the file is already part of a tablespace?
19. How many panes are there in the Enterprise Manager console?
20. Which data dictionary view shows that the database is in ARCHIVELOG mode?
21. Who owns the data dictionary?
22. I have a server with 2GB RAM.Who is the optimal size for the shared pool size The shared pool reserved size is 1/10 to shared pool size Is there a mathematic type for compute the shared pool size ?
23. What happens if you issue the following command? ALTER DATABASE ADD LOGFILE (‘/logs/file1’ REUSE, ‘/logs/file2’ REUSE)
24. Using SQL*Plus, list two options which show the value of the parameter DB_BLOCK_SIZE
25. Which two values from the V$SESSION view are used to terminate a user session?
26. What is the minimum number of extents a rollback segment can have?