DBA Interview Questions with Answers Part8‎

Difference between locks and latches

Locks are used to protect the data or resources from the simultaneous use of them by multiple sessions which might set them in inconsistent state. Locks are external mechanism, means user can also set locks on objects by using various oracle statements.

Latches are for the same purpose but works at internal level. Latches are used to Protect and control access to internal data structures like various SGA buffers. They are handled and maintained by oracle and we can’t access or set it.

Setting the audit_trail parameter in the database to “db”, it generates lot of records in sys.aud$ table. Can you suggest any method to overcome this issue?

1. When you set audit it does audit for every single activity on the database. So it may lead into performance problem.
You have to disable every single audit(<> noaudit) before or after you set the parameter and then enable one by one based on the requirement.
2. You should monitor the growth of sys.aud$ and archive it properly or maintain the space.

How to change the topnsql of AWR Snapshot in 10g

Select * from DBA_HIST_WR_CONTROL
1898043910 +00 01:00:00.000000 +01 00:00:00.000000 DEFAULT

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(topnsql => 30);

Select * from DBA_HIST_WR_CONTROL
1898043910 +00 01:00:00.000000 +01 00:00:00.000000 30

How to detect who’s causing excessive redo generation

SELECT S1.SID, S1.SERIAL_NUM, S1.USER_NAME, S1.PROGRAM, T1.USED_UBLK, T1.USED_UREC FROM V$SESSION S1, V$TRANSACTION T1 WHERE S1.TADDR = T1.ADDR ORDER BY 5 DESC, 6 DESC, 1, 2, 3, 4;

Tracking undo generation by all session

SELECT S1.SID, S1.USER_NAME, R1.NAME, T1.START_TIME, T1.USED_UBLK , T1.USED_UREC FROM V$SESSION S1, V$TRANSACTION T1, V$ROLLNAME R1 WHERE T1.ADDR = S1.TADDR AND R1.USN = T1.XIDUSN;

Or you can collect Statistics from V$SESSTAT to AWR

How do you remove an SPFILE parameter (not change the value of, but actually purge it outright)?

Use "ALTER SYSTEM RESET ..." (For database versions 9i and up)

Syntax:

ALTER SYSTEM RESET PARAMETER SID='SID|*'

ALTER SYSTEM RESET "_TRACE_FILES_PUBLIC" SCOPE=SPFILE SID='*';

NOTE: The "SID='SID|*'" argument is REQUIRED!

Can you use RMAN to recover RMAN?

Yes, you can!

Which situation ‘Exist’ condition is better than ‘IN’

If the resultant of sub query is small then ‘IN’ is typically more appropriate where as resultant of sub query is big/large/long then ‘EXIST’ is more appropriate. The ‘Exist’ always results full scan of table where as first query can make use of index on Table.

Is Oracle really quicker on Windows than Solaris?

I found in my experience that Yes, windows perform better on comparable hardware just about any UNIX box. I am working on Windows but once I installed Solaris trying to test. I found the windows installations always outperformed the Solaris ones both on initial loading the pool cache and subsequent runs. The test package is rather large (5000+ lines), which is used in a form to display customer details. On Solaris I was typically getting an initial return time of 5 seconds and on windows, typically, 1 second. Even subsequent runs (i.e. cached) the windows outperformed Solaris. The parameter sizes for the SGA were approx. the same and the file systems are the conventional method. In both cases the disk configuration is local.

What is Difference between DBname and instance_name?

A database is a set of files (data, redo, ctl and so on) where as An instance is a set of processes (SMON, PMON, DBWR, etc) and a shared memory segment (SGA).

A database may be mounted and opened by many INSTANCES (Parallel Server) concurrently. An instance may mount and open ANY database -- however it may only open a single database at any time. There for you need unique (for the set of files).

Does DBCA create instance while creating database?

DBCA does not create instance. It create database (set of files). The instance is only feelings do a shutdown and goodbye instance and on windows it registers the necessary services that can be used to start an instance when you want.

Is there any way to create database without DBCA?

Yes, you can used oradim directly

What's the difference between connections, sessions and processes?

A connection is a physical circuit between you and the database.  A connection might be one of many types -- most popular begin DEDICATED server and SHARED server.  Zero, one or more sessions may be established over a given connection to the database as show above with sqlplus.  A process will be used by a session to execute statements.  Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal dedicated server connection).  Sometimes there is a one to many from connection to sessions (eg: like autotrace, one connection, two sessions, one process).  A process does not have to be dedicated to a specific connection or session however, for example when using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement.  When the call is over, that process is released back to the pool of processes.  

SQL>select username from v$session where username is not null;

you can see one session, me

SQL>select username, program from v$process;

you can see all of the backgrounds and my dedicated server...

Autotrace for statistics uses ANOTHER session so it can query up the stats for your

CURRENT session without impacting the STATS for that session!

SQL>select username from v$session where username is not null;

now you can see two session but...

SQL>select username, program from v$process;

Same 14 processes...

What about Fragmentation situation (LMT) in oracle 8i and up?

Fragmentation is that if you have many “small” holes (regions of contiguous free space) that are too small to be the next extent of any object. These holes of free space resulted from dropping some object (or truncating them)  and the resulting free space cannot be used by any other object in that tablespace. This is a direct result of using pctincrease that is not zero and having many weird sized extents (every extents is unique size and shape). In oracle 8i and above we all are using locally managed tablespace. These would use either uniform sizing or our automatic allocation scheme. In either case it is almost impossible to get into a situation where you have unusable free space.

To see if you suffer from fragmentation you can query from DBA_FREE_SPACE (best to do an alter tablespace to ensure all contiguous made into 1 big free region). You would look any free space that is smaller then the smallest next extent size for any object in that tablespace. Check with below query:

Select * from dba_free_space

where tablespace_name = 'T' and bytes <= ( select min(next_extent)

from dba_segments where tablespace_name = 'T') order by block_id

Is there a way we can flush out a known data set from the database buffer cache?

No you don’t, in real life; the cache would never be empty. It is true that 10g introduce an alter system flush buffer_cache, but it is not really worthwhile. Having empty buffer cache is fake, if no more so than what you are currently doing.

What would be the best approach to benchmark the response time for a particular query?

run query q1 over and over (with many different inputs)

run query q2 over and over (with many different inputs)

discard first couple of observations, and last couple

use the observations in the middle

What is difference between Char and Varchar2 and which is better approach?

A CHAR datatype and VARCHAR2 datatype are stored identically (eg: the word 'WORD' stored in a CHAR(4) and a varchar2(4) consume exactly the same amount of space on disk, both have leading byte counts).

The difference between a CHAR and a VARCHAR is that a CHAR(n) will ALWAYS be N bytes long, it will be blank padded upon insert to ensure this.  A varchar2(n) on the other hand will be 1 to N bytes long, it will NOT be blank padded. Using a CHAR on a varying width field can be a pain due to the search semantics of CHAR. 

Consider the following examples:

SQL> create table t ( x char(10) );

Table created.

SQL> insert into t values ( 'Hello' );

1 row created.

SQL> select * from t where x = 'Hello';

X

----------

Hello

SQL> variable y varchar2(25)

SQL> exec :y := 'Hello'

PL/SQL procedure successfully completed.

SQL> select * from t where x = :y;

no rows selected

SQL> select * from t where x = rpad(:y,10);

X

----------

Hello

Notice how when doing the search with a varchar2 variable (almost every tool in the world

uses this type), we have to rpad() it to get a hit. If the field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER, it will not help either. 

Rman always shows date in DD-MON-YY format. How to set date format to M/DD/YYYY HH24:MI:SS in rman ?

You can just set the NLS_DATE_FORMAT before going into RMAN:

In Rman list backup how do i get time column that shows me date and time including seconds as generally it is showing only date.

Before connecting the rman target set the date format on command prompt:

export NLS_DATE_FORMAT=dd-mon-yyyy hh24:mi:ss - Linux

Set NLS_DATE_FORMAT=dd-mon-yyyy hh24:mi:ss - windows

then try to connect rman target

rman target sys/oralce@orcl3 catalog rman/rman@shaan

rman> list backupset 10453

Why not use O/S backups instead of RMAN?

There is nothing wrong with doing just OS backups. OS backups are just as valid as RMAN backups. RMAN is a great tool but it is not the only way to do it.  Many people still prefer using a scripting tool of there choice such as perl or ksh to do this.

RMAN is good if you have lots of databases.  The catalog it uses remembers lots of details for you.  You don't have as much to think about.

RMAN is good if you do not have good "paper work" skills in place.  Using OS backups, it is more or less upto you to remember where they are, what they are called and so on.  You have to do all of the book keeping RMAN would do.

RMAN provides incremental backups, something you cannot get without RMAN.

RMAN provides tablespace point in time recovery.  You can do this without RMAN but you have to do it by yourself and it can be rather convoluted.

RMAN is more integrated with OEM.  If you do OS backups, you'll have to do everything yourself.  With RMAN you may have less scripting to develop, test and maintain.

RMAN if the catalog/controlfile are damaged? what is the next step?

If you lose rman, you rebuild from the controlfiles of the backed up databases but, you should not lose the rman catalog using proper techniques of backup itself.

How to switch between Noarchivelog and archivelog in oracle 10g

connect "/ as sysdba"

alter system set log_archive_start=true scope=spfile;

alter system set log_archive_dest='......' scope=spfile;

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

connect /

-and-

connect "/ as sysdba"

shutdown immediate

startup mount

alter database noarchivelog;

alter database open;

connect /

How to Update millions or records in a table?

If we had to update millions of records I would probably opt to NOT update.

I would more likely do:

CREATE TABLE new_table as select <do the update "here"> from old_table;

index new_table

grant on new table

add constraints on new_table

etc on new_table

drop table old_table

rename new_table to old_table;

You can do that using parallel query, with nologging on most operations generating very

little redo and no undo at all in a fraction of the time it would take to update the

data.

SQL>create table new_emp as select empno, LOWER(ename) ename, JOB,

MGR, HIREDATE, SAL, COMM, DEPTNO from emp;

SQL>drop table emp;

SQL>rename new_emp to emp;

How to convert database server sysdate to GMT date?

Select sysdate, sysdate+(substr(tz_offset(dbtimezone),1,1)||’1’)*to_dsinterval(‘0

‘||substr(tz_offset( DBTIMEZONE ),2, 5)||’:00’) from dual;