DBA Interview Questions with Answers
Can one switch to another database user without a password?
Users normally use the "CONNECT" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to bridge Oracle's security, but look at this example:
SQL> CONNECT / as sysdba
SQL> SELECT password FROM dba_users WHERE username='SCOTT';
F894844C34402B67
SQL> ALTER USER scott IDENTIFIED BY anything;
SQL> CONNECT scott/anything
OK, we're in. Let's quickly change the password back before anybody notices.
SQL> ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';
User altered.
How do you delete duplicate rows in a table?
There is a several method to delete duplicate row from the table:
Method1:
DELETE FROM SHAAN A WHERE ROWID >
(SELECT min(rowid) FROM SHAAN B
WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID);
Method2:
delete from SHAAN t1
where exists (select 'x' from SHAAN t2
where t2.EMPLOYEE_ID = t1.EMPLOYEE_ID
and t2.EMPLOYEE_ID = t1.EMPLOYEE_ID
and t2.rowid > t1.rowid);
Method3:
DELETE SHAAN
WHERE rowid IN
( SELECT LEAD(rowid) OVER
(PARTITION BY EMPLOYEE_ID ORDER BY NULL)
FROM SHAAN );
Method4:
delete from SHAAN where rowid not in
( select min(rowid)
from SHAAN group by EMPLOYEE_ID);
Method5:
delete from SHAAN
where rowid not in ( select min(rowid)
from SHAAN group by EMPLOYEE_ID);
Method6:
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;
What is Automatic Management of Segment Space setting?
Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps. It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.
ASSM can be specified only with the locally managed tablespaces (LMT). The CREATE TABLESPACE statement has a new clause SEGMENT SPACE MANAGEMENT. Oracle uses bitmaps to manage the free space. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap.
CREATE TABLESPACE myts DATAFILE '/oradata/mysid/myts01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;
What is COMPRESS and CONSISTENT setting in EXPORT utility?
If COMPRESS=Y, the INITIAL storage parameter is set to the total size of all extents allocated for the object. The change takes effect only when the object is imported.
Setting CONSISTENT=Y exports all tables and references in a consistent state. This slows the export, as rollback space is used. If CONSISTENT=N and a record is modified during the export, the data will become inconsistent.
What is the difference between Direct Path and Convention Path loading?
When you use SQL loader by default it use conventional path to load data. This method competes equally with all other oracle processes for buffer resources. This can slow the load. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. If load speed is most important to you, you should use direct path load because it is faster.
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).
CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2 (50)) ORGANIZATION INDEX;
What are a Global Index and Local Index?
When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table. Local keyword in the index partition tells oracle to create a separate index for each partition of the table. TheGlobal clause in create index command allows you to create a non-partitioned index or to specify ranges for the index values that are different from the ranges for the table partitions. Local indexes may be easier to manage than global indexes however, global indexes may perform uniqueness checks faster than local (portioned) indexes perform them.
What is difference between Multithreaded/Shared Server and Dedicated Server?
Oracle Database creates server processes to handle the requests of user processes connected to an instance.A dedicated server process, which services only one user processA shared server process, which can service multiple user processes
Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters.
Can you import objects from Oracle ver. 7.3 to 9i?
We can not import from lower version export to higher version in fact. But not sure may be now concept is changed.
How do you move tables from one tablespace to another tablespace?
Method 1:
Export the table, drop the table, create the table definition in the new tablespace, and then import the data (imp ignore=y).
Method 2:
Create a new table in the new tablespace with the "CREATE TABLE x AS SELECT * from y" command:
CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM real_table;
Then drop the original table and rename the temporary table as the original:
DROP TABLE real_table;
RENAME temp_name TO real_table;
Note: After step #1 or #2 is done, be sure to recompile any procedures that may have been
invalidated by dropping the table. Prefer method #1, but #2 is easier if there are no indexes, constraints, or triggers. If there are, you must manually recreate them.
Method 3:
If you are using Oracle 8i or above then simply use:
SQL>Alter table table_name move tablespace tablespace_name;
How do see how much space is used and free in a tablespace?
SELECT * FROM SM$TS_FREE;
SELECT TABLESPACE_NAME, SUM(BYTES) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
Can view be the based on other view?
Yes, the view can be created from other view by directing a select query to use the other view data.
What happens, if you not specify Dictionary option with the start option in case of LogMinor concept?
It is recommended that you specify a dictionary option. If you do not, LogMiner cannot translate internal object identifiers and datatypes to object names and external data formats. Therefore, it would return internal object IDs and present data as hex bytes. Additionally, the
MINE_VALUE andCOLUMN_PRESENT functions cannot be used without a dictionary.What is the Benefit and draw back of Continuous Mining?
The continuous mining option is useful if you are mining in the same instance that is generating the redo logs. When you plan to use the continuous mining option, you only need to specify one archived redo log before starting LogMiner. Then, when you start LogMiner specify theDBMS_LOGMNR.CONTINUOUS_MINE option, which directs LogMiner to automatically add and mine subsequent archived redo logs and also the online catalog.Continuous Mining is not available in Real Application Cluster.
What is LogMiner and its Benefit?
LogMiner is a recovery utility. You can use it to recover the data from oracle redo log and archive log file. The Oracle LogMiner utility enables you to query redo logs through a SQL interface. Redo logs contain information about the history of activity on a database.
Benefit of LogMiner?
1. Pinpointing when a logical corruption to a database; suppose when a row is accidentally deleted then logMiner helps to recover the database exact time based and changed based recovery.
2. Perform table specific undo operation to return the table to its original state. LogMiner reconstruct the SQL statement in reverse order from which they are executed.
3. It helps in performance tuning and capacity planning. You can determine which table gets the most update and insert. That information provides a historical perspective on disk access statistics, which can be used for tuning purpose.
4. Performing post auditing; LogMiner is used to track any DML and DDL performed on database in the order they were executed.
What is Oracle DataGuard?
Oracle DataGuard is a tools that provides data protection and ensures disaster recovery for enterprise data. It provides comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruption. Dataguard maintains these standsby databases as transitionally consistent copies of the production database. Then, if the production database becomes failure Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
What is Standby Databases
A standby database is a transitionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to9 standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
Similar to a primary database, a standby database can be either a single-instance Oracle database or an Oracle Real Application Clusters database. A standby database can be either a physical standby database or a logical standby database:
Difference between Physical standby Logical standby databases
Provides a physically identical copy of the primary database on a block-for-block basis. The database schema, including indexes, is the same. A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data, received from the primary database and applies the redo to the physical standby database.
Logical Standby database contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database though SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.
If you are going to setup standby database what will be your Choice Logical or Physical?
We need to keep the physical standby database in “recovery mode” in order to apply the received archive logs from the primary database. We can open “physical stand by database to “read only” and make it available to the applications users (Only select is allowed during this period). Once the database is opened in “Read only” mode then we can not apply redo logs received from primary database.
We do not see such issues with logical standby database. We can open up the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.
If the primary database needed to support pretty large user community for the OLTP system and pretty large “Reporting Group” then better to uselogical standby as primary database instead of physical database.
What are the requirements needed before preparing standby database?
· OS Architecture of primary database secondary database should be same.
· The version of secondary database must be the same as primary database.
· The Primary database must run in Archivelog mode.
· Require the same hardware architecture on the primary and all standby site.
· Does not require the same OS version and release on the primary and secondary site.
· Each Primary and secondary database must have its own database.
What are “Failover” and “Switchover” in case of dataguard?
Failover is the operation of bringing one of the standby databases online as the new primary database when failure occurs on the primary database and there is no possibility of recover primary database in a timely manner. The switchover is a situation to handle planned maintenance on the primary database. The main difference between switchover operation and failover operation is that switchover is performed when primary database is still available or it does not require a flash back or re-installation of the original primary database. This allows the original primary database to the role of standby database almost immediately. As a result schedule maintenance can performed more easily and frequently.
When you use WHERE clause and when you use HAVING clause?
HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.
What is a cursor and difference between an implicit & an explicit cursor?
A cursor is a PL/SQL block used to fetch more than one row in a Pl/SQl block. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However, queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.
Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
Explain the difference between a data block, an extent and a segment.A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?Disable the foreign key constraint to the parent, drop the table, re-create the table, and enable the foreign key constraint.
How to Unregister database from Rman catalog
First we start up RMAN with a connection to the catalog and the target, making a note of the DBID in the banner:
C:\>rman catalog=rman/rman@shaan target=HRMS/password@orcl3
connected to target database: W2K1 (DBID=691421794)
connected to recovery catalog database
Note the DBID from here. Next we list and delete any backupset recorded in the repository:
RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE BACKUP DEVICE TYPE SBT;
RMAN> DELETE BACKUP DEVICE TYPE DISK;
Next we connect to the RMAN catalog owner using SQL*Plus and issue the following statement:
SQL> CONNECT rman/rman@shaan
SQL> SELECT db_key, db_id FROM db
WHERE db_id = 1487421514;
DB_KEY DB_ID
---------- ----------
1 691421794
The resulting key and id can then be used to unregister the database:
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 691421794);
PL/SQL procedure successfully completed.
