Oracle Interview Questions – Part 36
Q.Can you assign Priority to users?
YES. This is achievable with Oracle Resource Manager.
DBMS_RESOURCE_MANAGER is the packcage to administer the Database Resource Manager.
The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives. It also provides semantics so that you may group together changes to the plan schema.
Q.You want users to change their passwords every 2 months. How do you enforce this?
Oracle password security is implemented via Oracle “profiles” which are assigned to users.
PASSWORD_LIFE_TIME – limits the number of days the same password can be used for authentication
First, start by creating security “profile” in Oracle database and then alter the user to belong to the profile group.
1) creating a profile:
create profile all_users
2) Create user and assign user to the all_users profile
SQL>create user chuck identified by norris profile all_users;
3) To “alter profile” parameter, say; change to three months:
SQL>alter profile all_users set PASSWORD_LIFE_TIME = 90;
Q.What is the difference between DELETE and TRUNCATE statements?
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows.
If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
DELETE will cause all DELETE triggers on the table to fire.
TRUNCATE removes all rows from a table. A WHERE clause is not permited. The operation cannot be rolled back and no triggers will be fired.
As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.
Q.What is the default ordering of an ORDER BY clause in a SELECT statement
Q.What is tkprof and how is it used
The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.
Q.How can you determine if an index needs to be dropped and rebuilt
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
Q.How can variables be passed to a SQL routine
By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,…,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: “select * from dba_tables where owner=&owner_name;” . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
Q.You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action
No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.
Q.You see multiple extents in the temporary tablespace. Is this a problem
As long as they are all the same size this isn?t a problem. In fact, it can even improve performance since Oracle won?t have to create a new extent when a user needs one.
Q.When configuring SQLNET on the client what files need to be set up
Q.What must be installed with ODBC on the client in order for it to work with Oracle
SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.
Q.Give two examples of how you might determine the structure of the table DEPT.
Use the describe command or use the dbms_metadata.get_ddl package.
Q.Where would you look for errors from the database engine?
In the alert log.
Q.Explain an ORA-01555.
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
Q.Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Q.How can you rebuild an index?
ALTER INDEX index_name REBUILD;
Q.Explain what partitioning is and what its benefit is.
A table partition is also a table segment, and by using partitioning technique we can enhance performance of table access.
Q.Describe the use of PL/SQL tables
PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
Q.When is a declare statement needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
Q.Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning
The hit ratio is a measure of how many times the database was able to read a value from the buffers verses how many times it had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this will be a cumulative value since the database started. If you do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for that time span. Generally speaking an instantaneous reading gives more valuable data since it will tell you what your instance is doing for the time it was generated over.
Q.Discuss row chaining, how does it happen? How can you reduce it? How do you correct it
Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and won?t fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.
Q.A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check
You need to check that the user has specified the full name of the object (select empid from scott.emp; instead of select empid from emp;) or has a synonym that balls to the object (create synonym emp for scott.emp;)
Q.A developer is trying to create a view and the database won?t let him. He has the “DEVELOPER” role which has the “CREATE VIEW” system privilege and SELECT grants on the tables he is using, what is the problem
You need to verify the developer has direct grants on all tables used in the view. You can?t create a stored object with grants given through views.
Q.You want to use SQL to build SQL, what is this called and give an example
This is called dynamic SQL. An example would be: set lines 90 pages 0 termout off feedback off verify off spool drop_all.sql select ?drop user ?||username||? cascade;? from dba_users where username not in (“SYS?,?SYSTEM?); spool off Essentially you are looking to see that they know to include a command (in this case DROP USER…CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.
Q.What is PCTFREE and PCTUSED Setting?
PCTFREE is a block storage parameter used to specify how much space should be left in a database block for future updates.
For example, for PCTFREE=10, Oracle will keep on adding new rows to a block until it is 90% full. This leaves 10% for future updates (row expansion).
When using Oracle Advanced Compression, Oracle will trigger block compression when the PCTFREE is reached. This eliminates holes created by row deletions and maximizes contiguous free space in blocks.
See the PCTFREE setting for a table:
SQL> SELECT pct_free FROM user_tables WHERE table_name = ‘EMP';
PCTUSED is a block storage parameter used to specify when Oracle should consider a database block to be empty enough to be added to the freelist. Oracle will only insert new rows in blocks that is enqueued on the freelist.
For example, if PCTUSED=40, Oracle will not add new rows to the block unless sufficient rows are deleted from the block so that it falls below 40% empty.
Q.What is COMPRESS and CONSISTENT setting in EXPORT utility?
Simply: COMPRESS=n – Allocated space in database for imported table will be exactly as the space required to hold the data.
COMPRESS=y – The INITIAL extent of the table would be as large as the sum of all the extents allocated to the table in the original database.
In other words:
The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import.
If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.
If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size.
If you are using locally managed tablespaces you should always export with COMPRESS=n
Default: n. Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the exp command.
You should specify CONSISTENT=y when you anticipate that other applications will be updating the target data after an export has started.
If you use CONSISTENT=n, each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions.
If a table is partitioned, each partition is exported as a separate transaction.
Therefore, if nested tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.
Q.What is an Index Organized Table?
An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure. Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast to this, index-organized tables store rows in a B-tree index structure that is logically sorted in primary key order. Unlike normal primary key indexes, which store only the columns included in it definition, IOT indexes store all the columns of the table (an exception to this rule – is being called the overflow area).
Properties and restrictions:
•An IOT must contain a primary key
•Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables
•An IOT cannot be in a cluster
•An IOT cannot contain a column of LONG data type
•You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.
Advantages of an IOT
•As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don’t need to access the table to get additional column values.
•As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
•As the index and the table are in the same segment, less storage space is needed.
•In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
•As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.
Row overflow area
If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area.
The overflow area can contains only columns that are not part of the primary key.
If a row cannot fit in a block, you must define an overflow area.
Consequently, the primary key values of an IOT must fit in a single block.
The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (examples on source website).
Q.You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done
Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick. For example: select rowid from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no); In the situation where multiple columns make up the proposed key, they must all be used in the where clause.
Q.What is a Cartesian product
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
Q.What is difference between SQL and SQL*PLUS
SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. Its a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database(DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.
Q.Which datatype is used for storing graphics and images
LONG RAW data type is used for storing BLOB’s (binary large objects).
Q.Which is more faster – IN or EXISTS
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
Appropriate answer will be….
Result of the subquery is small Then “IN” is typicaly more appropriate. and Result of the subquery is big/large/long Then “EXIST” is more appropriate.
Q.What is a OUTER JOIN
Outer Join–Its a join condition used where you can query all the rows of one of the tables in the join condition even though they dont satisfy the join condition.
Q.What is use of a cursor variable? How it is defined.
A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. A cursor variable is reference type(like a pointer in C). Declaring a cursor variable: TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list that will eventually be returned by the cursor variable.
Q.What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSOR RETURN students%ROWTYPE
Q.What is difference between a formal and an actual parameter
The variables declared in the procedure and which are passed, as arguments are called actual, the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters
Q.Can the default values be assigned to actual parameters.
Q.Difference between database triggers and form triggers.
a) Data base trigger(DBT) fires when a DML operation is performed on a data base table.Form trigger(FT) Fires when user presses a key or navigates between fields on the screen b) Can be row level or statement level No distinction between row level and statement level. c) Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms. d) Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger. e) Can cause other database triggers to fire.Can cause other database triggers to fire,but not other form triggers.
Q.What is an UTL_FILE.What are different procedures and functions associated
with it. UTL_FILE is a package that adds the ability to read and write to operating system files Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
Q.What can cause a high value for recursive calls? How can this be fixed
A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
Q.If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it
This indicate that the shared pool may be too small. Increase the shared pool size.
Q.You have a simple application with no “hot” tables (i.e. uniform IO and access requirements). How many disks should you have assuming standard layout for SYSTEM, USER, TEMP and ROLLBACK tablespaces
At least 7, see disk configuration answer above.
Q.Describe third normal form
Something like: In third normal form all attributes in an entity are related to the primary key and only to the primary key
Q.How can you determine if an Oracle instance is up from the operating system level
There are several base Oracle processes that will be running on multi-user operating systems, these will be smon, pmon, dbwr and lgwr. Any answer that has them using their operating system process showing feature to check for these is acceptable. For example, on UNIX a ps -ef|grep dbwr will show what instances are up.
Q.Where would you look to find out if a redo log was corrupted assuming you are using Oracle mirrored redo logs
There is no message that comes to the SQLDBA or SRVMGR programs during startup in this situation, you must check the alert.log file for this information.
Q.You attempt to add a datafile and get: ORA-01118: cannot add anymore datafiles: limit of 40 exceeded What is the problem and how can you fix it
When the database was created the db_files parameter in the initialization file was set to 40. You can shutdown and reset this to a higher value, up to the value of MAX_DATAFILES as specified at database creation. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
Q.How do you set up your tablespace on installation
The answer here should show an understanding of separation of redo and rollback, data and indexes and isolation os SYSTEM tables from other tables. An example would be to specify that at least 7 disks should be used for an Oracle installation so that you can place SYSTEM tablespace on one, redo logs on two (mirrored redo logs) the TEMPORARY tablespace on another, ROLLBACK tablespace on another and still have two for DATA and INDEXES. They should indicate how they will handle archive logs and exports as well. As long as they have a logical plan for combining or further separation more or less disks can be specified.
Q.What should be done prior to installing Oracle (for the OS and the disks)
adjust kernel parameters or OS tuning parameters in accordance with installation guide. Be sure enough contiguous disk space is available.
Q.You get a call from you backup DBA while you are on vacation. He has corrupted all of the control files while playing with the ALTER DATABASE BACKUP CONTROLFILE command. What do you do
As long as all datafiles are safe and he was successful with the BACKUP controlfile command you can do the following: CONNECT INTERNAL STARTUP MOUNT (Take any read-only tablespaces offline before next step ALTER DATABASE DATAFILE …. OFFLINE;) RECOVER DATABASE USING BACKUP CONTROLFILE ALTER DATABASE OPEN RESETLOGS; (bring read-only tablespaces back online) Shutdown and backup the system, then restart If they have a recent output file from the ALTER DATABASE BACKUP CONTROL FILE TO TRACE; command, they can use that to recover as well. If no backup of the control file is available then the following will be required: CONNECT INTERNAL STARTUP NOMOUNT CREATE CONTROL FILE …..; However, they will need to know all of the datafiles, logfiles, and settings for MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.