basics interview q s

7/29/2019 Basics Interview q s

1/16

ORACLE BASICS INTERVIEW QUESTIONS

1.What is difference between varchar and varchar2?

Varchar means fixed length char varchar2 means variable length char

2. Difference between oracle8i and oracle9i?

a. The biggest difference between Oracle8i and Oracle9i is that Oracle9i lets you resize theSGA memory areas dynamically i.e., the Database Buffer Cache DB_CACHE_SIZE or theSHARED_POOL_SIZE, etc., can be resized when the database is up and running. The sameis not possible with Oracle8i. You can get more information from OTN

b. Oracle 9i is an enhanced version of the Oracle 8i.Oracle 8i does not provided timestamping facility, which is provided in Oracle 9i.

c. you can drop a column in oracle 9i using alter table command where as this is notavailable in oracle 8i

d. Database performance ease of management scalability security availability Windows2000 integration and application areas: Internet content management commerceintegration packaged applications Business Intelligence native compilation of PL/SQLreduction in latch contention (SGA locks) and I/O improvements better Java performance(better compilation, improved garbage collection) distributed database performanceenhancements enhanced 3-tier security (integration with LDAP...) improved hostingsecurity (through use of virtual private databases), fine-grained auditing and single sign-onimproved user security (more password management features, etc.)Ability to encryptstored data row-level access control (Oracle Label Security)

e. Listed below are some of the features of 9i:1) U has automatic undo management.2) U has automatic segment space management3) U got these locally managed tablespaces which is better than the dictionarymanaged tablespaces as u can avoid fragmentation with locally managedtablespaces.4) U has a new dbms_metadata package to extract object definitions from database.5) Multi table insert is possible with 9i6) Importantly u has this spfile in 9i.7) Also u has dynamic memory management facility with 9i.8) U has tuning advisories with 9i.

9) U has multi block size parameter with 9i and many more features like these.

3.What is Pro*C? What is OCI?

Pro *CThe Pro* c/C++ precompiler takes the SQL statement that embedded in C/C++ code convertinto standard C/C++ code. When successfully precompile this code, the result is a C or C++program that we compile and used to build the application that access the OracleApplication.

OCI: - OCI refer to Oracle Call interface is set of Low Level API (Application ProgramInterface Call) used to interact with Oracle Database. By OCI one can use the operation suchas Logon, Fatch, parse, execute etc. Generally these are written in C/C++.


2/16

4.What is the difference between "NULL in C" and "NULL in Oracle?

The NULL in C treated as Zero or void. but in SQL NULL value is Non or blank represented itcan't manipulated

5. In Oracle varchar2 takes dynamic space for storage then why char is still in oracle?

The major difference between varchar2 and char is fixed length and variable length.Varchar2 have variable length mean if we declare as 20 space and its use only 5 space thememory assign only 5. But in char takes daclare space while use any number space less thandeclare

6.what are the difference between and constraints and triggers?

Constraints are used to maintain the integrity and atomicity of database .in other words itcan be said they are used to prevent invalid data entry. The main 5 constraints areNOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE KEY and CHECK

Triggers are basically stored procedures, which automatically fired when any insert, updateor delete is issued on table

Another most imp. Diff. is that trigger effected only those row after which trigger appliedbut constraint effected all row of table.

7.what is normalization? What is the advantage of normalization (briefly)

1.The process of separating data into distinct, unique sets is called normalization. This isimplemented to improve the performance of the RDBMS, such as reduces redundancy ofdata and data inconsistency.

2.Normalization is the process of removing redundant data from your tables in order toimprove storage efficiency, data integrity and scalability.

3.Database normalization is a series of steps followed to obtain a database design that allowsfor consistent storage and efficient access of data in a relational database. These stepsreduce data redundancy and the risk of data becoming inconsistent.

4.Normalization is the process used to reduce the unnecessary repetition of data i.e.,redundant data. It is performed on the data, which is redundant and makes the data in anormalized format. It is of step-by-step process IstNotmal, FormIIndNormal, formIIIrdNormal,formIVthNormal form or Boyce odd Normal form By performing this we will get the data in

the Normalized formati.,e from DBMS to RDBMS.

8.How the logs are escalated?

There are different types are logs which are logged by Oracle Database Engine. A DBA has tolook in for alter log which exist background dump dust. The seviourity of the problem areanalysed and escalated based on the information provided by the alter log.

9.What is structure of Database?

1.Oracle database usually contains one database and a single instance. But, Oracle 9i, 10gRAC (Real Application Clusters) can have multiple instances to interact with a single

database for high availability.Instance is non-persistent, memory based background processes and structures.Database is persistent, disk based, data and control files


3/16

2.Physical Structure of Database:One or more data files, control file(s), Redo log file(s) and init.ora fileLogical Structure of Database:Table spaces, segments, extents, blocks

10.what is the difference between primary key, unique key, and surrogate key?

Primary Key: A column in a table whose values uniquely identify the rows in the table. Aprimary key value cannot be NULL.

Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. Therecan be one and only one row for each unique key value.

Surrogate Key: A system generated key with no business value. Usually implemented withdatabase-generated sequences.

Primary Key Unique key

1.There is only one 1. There may be more than 1Primary key for I table Unique Key in table

2.It can contain Null Value 2. It can contain Null Value1. what are the diffrent file types that are supported by SQL*Loader?

a.direct method (skips dbcache, no redo generation)

b. conventional method (just opposite of direct load)

2.How to find how many database reside in Oracle server in query?

select count(*) from v$database;

3.What spfile/init.ora file parameter exists to force the CBO to make the execution path of agiven statement use an index, even if the index scan may appear to be calculated as morecostly?

CBO (Cost Based Optimizer): Generates an execution plan for a SQL statementOptimizer_index_cost_adj parameter can be set to help CBO to decide an executionplan, which affects the speed of SQL query. We can also make necessary changes tothe following parameters to effect CBO performance:

Optimizer_search_limit & optimizer_max_permutations

4.How many memory layers are in the shared pool?

The shared pool portion of the SGA contains three major areas: library cache,dictionary cache, buffers for parallel execution messages, and control structures.

5.What are the attributes of the Virtual Indexes?

a. These are permanent and continue to exist unless we drop them.

b. Their creation will not affect existing and new sessions. Only sessions marked for

Virtual Index usage will become aware of their existence.


4/16

c. Such indexes will be used only when the hidden parameter"_use_nosegment_indexes" is set to true.

d. The Rule based optimizer did not recognize Virtual Indexes when ITested, however, CBO recognizes them. In all of my examples, I have used CBO.However, I did not carry out intensive testing in RBO and you may come across

exceptions to this view.

d. Dictionary view DBA_SEGMENTS will not show an entry for Virtual Indexes. Thetable DBA_INDEXES and DBA_OBJECTS will have an entry for them in Oracle 8i; inOracle 9i onwards, DBA_INDEXES no longer show Virtual Indexes.

e. Virtual Indexes cannot be altered and throw a "fake index" error!f. Virtual Indexes can be analyzed, using the ANALYZE command or DBMS_STATSpackage, but the statistics cannot be viewed (in Oracle 8i, DBA_INDEXES will notshow this either). Oracle may be generating artificial statistics and storing itsomewhere for referring it later.Creating Virtual Index

Creating a Virtual Index can be achieved by using the NOSEGMENT clause with theCREATE INDEX command.

6.What is meant by Virtual Indexes in Oracle?

Virtual Indexes are another undocumented feature used by Oracle. Virtual indexes,as the name suggests are pseudo-indexes that will not behave the same way thatnormal indexes behave, and are meant for a very specific purpose. A virtual index iscreated in a slightly different manner than the normal indexes. A virtual index hasno segment pegged to it, i.e., the DBA_SEGMENTS view will not show an entry forthis.

Oracle handles such indexes internally and few required dictionary tables areupdated so that the optimizer can be made aware of its presence and generate anexecution plan considering such indexes.As per Oracle, this functionality is notintended for standalone usage. It is part of the Oracle Enterprise Manger Tuning Pack(Virtual Index Wizard).

The virtual index wizard functionality allows the user to test a potential new indexprior to actually building the new index in the database.It allows the CBO to evaluatethe potential new index for a selected SQL statement by building an explain plan thatis aware of the potential new index.

This allows the user to determine if the optimizer would use the index, onceimplemented.

7.What are materialized views? When are they used?

Materialized view is like a view but stores both definition of a view plus the rowsresulting from execution of the view. It uses a query as the bases and the query isexecutated at the time the view is created and the results are stored in a table. Youcan define the Materialized view with the same storage parametes as any other tableand place it in any tablespace of your choice. You can also index and partition theMaterialized view table like other tables to improve performance of queries

executed aginst them.

Use of Meterialized view:-


5/16

Expensive operations such as joins and aggregations do not need to be re executed.If the query is astisfied with data in a Meterialized view, the server transforms thequery to reference the view rather than the base tables.

8.What is a functional index - explain?

Function-based indexes can use any Function or Object method that is declared asrepeatable.Queries using expressions can use the index.Ex: - CREATE INDEX sales_margin_inxON sales (revenue - cost);

Sql> SELECT ordidFROM salesWHERE (revenue - cost) > 1000;We have to enable Function-based indexes by enableing the following initializationparametersALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

9.Where we use bitmap index?

a. Bitmap indexes are most appropriate for columns having low distinct values.b. We can use bitmap index where cardinality is very low like gender column or colorcolumn.

10.What is an extent?a. An extent is the smallest unit of storage allocation comprising collection of Blocks.b. Well an extent is a chunk of a space that is used by database segments when asegment is created it allocates extents.

11.How to you move from dedicated server Process to a Shared Server Process

Use DBCA (DATABASE CONFIGUARATION ASSISTANT) toolYou will get the option toselect shared server mode.

12.What are the components of physical database structure of Oracle database?

A. Totally there are 6 files (components) of physical database structure.3 mandatory& 3 optional. Three mandatory files are

1> data file: store actual data2> control file: stores structural & status information of database.

3> redo log file: stores changed/committed dataThree optional files are4>-parameter file: stores all size related parameters note: this file ismandatory for a1st time u create a database, and then it is optional.5> archive log file: its offline copy of redo log files6>-password file: used to make normal user to behave as a super user.

b. 1. Control files2. Init file3. Log files 4. Redo/archive log files 5. data files

C. Physical components of oracle database are control files, redo log files and datafiles.Control file: control file is read in the mount state of database. Control file is a small

binary file, which records the physical structure of database, which includesDatabase name


6/16

Names and locations of datafiles and online redo log files. Timestamp of databasecreationCheckpoint information current logs sequence number.Redo log files: This files saves all the changes that are made to the database as theyoccur. This plays a great role in the database recovery.Datafiles: datafiles are the physical files, which stores data of all logical structure.

11.What are the components of logical database structure of Oracle database

Tablespace, segments, extents, data Blocks.

A logical unit of storage of database is called Tablespace.

A segment is a space allocated for a specific logical storage structure within atablespace.

Extents: Space allocated to a segments.

Datablocks: Oracle server manage the storage space in the datafiles in units is calleddata blocks or oracle blocks.

12.What are the different types of segments?

table segment, index segment, IOT, cluster, temp segment, data segment.

13.Can you name few DBMS packages and their use?

DBMS_OUTPUT.PUT_LINE ('strings');Print out the strings

DBMS_UTILITY.get_time ()get the current timeDBMS_ALERT

e.g begindbms_alert.register('myregister');end

To register interst in a named alert

14.How can be determine the size of the log files.Select sum (bytes)/1024/1024 size_in_mb from v$log;

15.How can be determine the size of the database?

A. Select sum (bytes)/1024/1024/1024 Size_in_GB from dba_data_files;

B. Select sum (bytes)/1024/1024 from v_$datafile + select sum (bytes)/1024/1024from v_$logfile will give u the total size of the database

C. Select (select sum (bytes/1024/1024/1024) from v$datafile) +( selectsum(bytes/1024/1024/1024) from v$tempfile) + (select sum(bytes/1024/1024/1024)from v$log) "Size of Database in GB" from dual.

16. How can you check which user has which Role.

desc dba_tab_privs (OR) Can use ROLE_TAB_PRIVS also (OR) select * fromdba_role_privs order by grantee;


7/16

17. Can you start a database without SPfile in oracle 9i?

While starting database Oracle reads spfiledb.ora, spfile.ora, initdb.ora or init.orafile. Yes it is possible to start the database using init.ora file only. The mainadvantage of using the SPFILE.ora is only to make changes to the dynamicinitialization parameters without restarting the database using the SCOPE option.

The changes will be stored in the spfile only and if you start the database using"pfile" option those changes wont be applicable to the database.

18.Do a view contain data?

A view does not contain any data of its own, but is like a window through which datafrom other tables can be viewed and changedThe answer depends on the type of view. In case of normal view, the ans is NO itonly contains query based on a base table but in case of materialized view, YES itdoes contain data and for the updated data in the base table, it needs to berefreshed.

NO: Because view is for view one or more tables data like query.

19.What is Oracle table?

A table is the most commonly used form of storing user data.A table is used to store the information in form of rows &columns in the database.Table is a segment (type of Segments), which is used to store user data. Tables canhave partitions (value wise, date period wise) and each partition can have separatetablespace for better performance. The Collection of informations stored in thestructured format that is called a table.

20.Can objects of the same schema reside in different tablespaces?

Yes, it can. For example if you specify a different tablepace (B) for indexes, theindexes of the tables that the user create would be residing in B, and the table wouldreside in the user's default tablespace A.

Yes: Schema objects can stored in different tablespace and a tablespace cancontained one or more schema objects data.

21.What is an Oracle index?

An Index is a tree structure that allows direct access to a row in a table. Indexes canbe classified based on their logical design or on their physical implementation.The Logical classification groups indexes from an application perspective, while thephysical classification is derived from the way the indexes are stored

An index is a schema object that can speed up the retrieval of rows by usingpointers. If you do not have an index, then a full table scan occurs. Its purpose is toreduce disk I/O by using an indexed path to locate data quickly. If a table is dropped,the corresponding indexes are also dropped.

It is created in existing table to locate rows more quickly&efficiently. The userscannot see the indexes; they are just used to speed up the queries

22.Can we create index on long raw column?


8/16

NO we can't create index on long raw column.

23.What is the basic element of base configuration of an Oracle database?

It consists of - one or more data files

- One or more control files- Two or more redo log filesThe database contains - Multiple users/schema's

- One or more rollback segments- One or more tablespaces- Data dictionary tables.User objects (table, indexes, views etc.)

24.What are clusters?

Groups of tables physically stored together because they share common columns andare often used together is called clusters.

25.What is the function of redo log?

The primary function of the redo log is to record all changes made to data.

26.What are the characteristics of data files?

Characterstics of Data File:- A Data file can be associated with only one database.- Once created, a data file cannot change is size.- One or more data files form a logical unit of database storage called a Tablespace.

Datafile size can be change by using ALTER DATABASE .................RESIZE command.


9/16

ORACLE DBA INTERVIEW QUESTIONS

1.What is a temporary segment

Temporary Segments are created by ORACLE when a SQL statement needs a temporary workarea to complete execution. When the statement finishes execution, the temporarysegments extents are released to the system for future use.

2.What are the uses of rollback segment

The use of RollBack Segment is Database is - to maintain read consistency between multipletransactions.

3.What process writes from data files to buffer cache?

Server Process (OR) it is an server process not DBWR(DataBase Writer)

4.What is a redo log?Redo log contains the before and after image copies of changed data.

5.What is a on-line redo log?

The On-line Redo Log is a set of tow or more on-line redo files that record all committedchanges made to the database. Whenever a transaction is committed, the corresponding

redo entries temporarily stores in redo log buffers of the SGA are written to an on-lineredo log file by the background process LGWR. The on-line redo log files are used incyclical fashion..

6.What does LGWR do ?It is one of the main background process in oracle . In oracle and unix environment you cancheck this process using ps -ef |pg command . which is writting all the uncommitedtransaction from SGA to redologfile . (or)Log Writer (LGWR) writes redo log entries generated in the redo log buffer of the SGA to on-line Redo Log File.

7.Name init.ora parameters which effects system performance.

These are the Parameters for init.ora

DB_BLOCK_BUFFERSSHARED_POOL_SIZESORT_AREA_SIZEDBWR_IO_SLAVESROLLBACK_SEGMENTSSORT_AREA_RETAINED_SIZEDB_BLOCK_LRU_EXTENDED_STATISTICS

SHARED_POOL_RESERVE_SIZE

8.What is a database instance and Explain


10/16

An Oracle instance is a combination of background processes and memory structures (SGA).

9.What are the Advantages of Using DBCA

These are a few of the advantages of using DBCA:

You can use its wizards to guide you through a selection of options providing an easy meansof creating and tailoring your database. It allows you to provide varying levels of detail. Youcan provide a minimum of input and allow Oracle to make decisions for you, eliminating theneed to spend time deciding how best to set parameters or structure the database.Optionally, it allows you to be very specific about parameter settings and file allocations.

It builds efficient and effective databases that take advantage of Oracle's new features.

It uses Optimal Flexible Architecture (OFA), whereby database files and administrative files,

including initialization files, follow standard naming and placement practices

10.How do you find whether the instance was started with pfile or spfile

There are 3 different ways :-

1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will returnNULL if you are using PFILE

2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you areusing pfile and not spfile

3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:By Default oracle will look into the default location depends on the o/s. Like in unix, oraclewill check in $oracle_home/dbs directory and on windows it will check inoracle_home/database directory, and the content of pfile is just text based, but spfilecontent is in binary format, that is understandable by oracle very well.

Also oracle server always check the spfile or pfile with these sequence :-

SPFILE.ORA, SPFILE.ORA, PFILE.ORA,PFILE.ORA

11.What is an index segmentOracle creates the index segment for an index or an index partition when you issue theCREATE INDEX statement. In this statement, you can specify storage parameters for theextents of the index segment and a tablespace in which to create the index segment. (Thesegments of a table and an index associated with it do not have to occupy the sametablespace.) Setting the storage parameters directly affects the efficiency of data retrievaland storage

12.What is public database link

You can create a public database link for a database. All users and PL/SQL subprograms inthe database can use a public database link to access data and database objects in thecorresponding remote database.


11/16

When many users require an access path to a remote Oracle database, an administrator cancreate a single public database link for all users in a database.CREATE PUBLIC DATABASE LINK ...;

13.What is row chaining

if any of the empty row is not sufficient to hold the row. then row is placed in multipleblocks. it happenes when the block size is small and rows are of large size. then it causechaining. Due to chaining performance degrades and will cause more IOs

14.What is the use of control file

Control file is binary file which is having all the information realted to database. db_name,maxlogfiles, maxdatafiles, tablespaces information. Without this u cannot open yourdatabase. Init.ora parameter file showing the location of the controlfile. (or)control file is a binary file which contains db_name,dbcreated,checkpointinformation,datafiles and logfiles.

15.What is a tablespaceTablespace is nothing but a logical object comprisiing of one or more data files whichactually stores the data. (or) A tablespace is a collection of one or more datafile.

16.What is SYSTEM tablespace and when is it createdSYSTEM TABLESPACE USAGE NOTES:

Username - Name of the userCreated - User creation date

Profile - Name of resource profile assigned to the userDefault Tablespace - Default tablespace for data objectsTemporary Tablespace - Default tablespace for temporary objects

Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

select USERNAME,CREATED,PROFILE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACEfrom dba_users

order by USERNAME

Objects in SYSTEM TSOBJECTS IN SYSTEM TABLESPACE NOTES:

Owner - Owner of the objectObject Name - Name of objectObject Type - Type of objectTablespace - Tablespace nameSize - Size (bytes) of object

Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEMtablespace


12/16

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES

from dba_segmentswhere TABLESPACE_NAME = 'SYSTEM'and OWNER not in ('SYS','SYSTEM')order by OWNER, SEGMENT_NAME

17.How to define data block size

The primary block size is defined by the Initiaization parameter DB_BLOCK_SIZE.

18.What is an Oracle sequence

A sequence is a database object created by a user that can be used to generate unique

integers. A typical usage of sequences is to generate primary key values which are uniquefor each row.It is generated and incremented (or decremented) by an internal Oracle routine. It can beused by multiple users and for multiple tables too. A sequence can be used instead ofwriting an application code for sequence-generating routine.

19.Explain the relationship among database, tablespace and data file.What is schemaDatabases, tablespaces and datafiles are closely related, but they have importantdifferences:

--- A Oracle Database consists of one or more tablespaces--- Each Table space in an Oracle database consists of one or more files called datafiles.--- A database's data is collectively stored in the datafiles that constitute each tablespace ofthe database

(or)When a database user is created, a corresponding schema with the same name is created forthat user. A schema is a named collection of objects that include Tables, Triggers,constraints, Indexes, Views etc. A user can only be associated with one schema, and that isthe same name as the user's. Username and schema are often used interchangeably.

20.DIFFERENTIATE BETWEEN Latches vs Enqueues

Enqueues are another type of locking mechanism used in Oracle. An enqueue is a moresophisticated mechanism which permits several concurrent processes to have varyingdegree of sharing of "known" resources. Any object which can be concurrently used, can beprotected with enqueues. A good example is of locks on tables. We allow varying levels ofsharing on tables e.g. two processes can lock a table in share mode or in share update modeetc.

One difference is that the enqueue is obtained using an OS specific locking mechanism. Anenqueue allows the user to store a value in the lock, i.e the mode in which we arerequesting it. The OS lock manager keeps track of the resources locked. If a process cannot

be granted the lock because it is incompatible with the mode requested and the lock isrequestedwith wait, the OS puts the requesting process on a wait queue which is serviced in FIFO.


13/16

Another difference between latches and enqueues is that in latches there is no orderedqueue of waiters like in enqueues.

Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors).Since all waiters are concurrently retrying (depending on the scheduler), anyone might getthe latch and conceivably the first one to try might be the last one to get.

21.What is a latch?

Latches are low level serialization mechanisms used to protect shared data structures in theSGA. The implementation of latches is operating system dependent, particularly in regard towhether a process will wait for a latch and for how long.

A latch is a type of a lock that can be very quickly acquired and freed. Latches are typicallyused to prevent more than one process from executing the same piece of code at a giventime. Associated with each latch is a cleanup procedure that will be called if a process dieswhile holding the latch. Latches have an associated level that is used to prevent deadlocks.

Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at alevel that is equal to or less than that level (unless it acquires it nowait).

What is difference between Logical Standby Database and Physical Standby database?

The primary functional difference between logical and physical standby database setups isthat logical standby permits you to add additional objects (tables, indexes, etc) to thedatabase, while physical standby is always an exact structural duplicate of the masterdatabase. The downside, though, is that logical standby is based on newer technologies(logical standby is new in Oracle 9.2) and tends to be generally regarded as moretempramental than physical standby.

22.What is the frequency of log Updated..?

1.COMMIT or ROLLABCK2.time out occurs (3 secs)3 1/3 of log is full4.1 mb of redo5. Checkpoint occurs23.How do you pin an object.

Use dbms_shared_pool procedure.EXECUTE DBMS_SHARED_POOL.KEEP(OBJECTNAME);

24.How do you rename a database?---------------------------------------------------------------------------------- To change the name of the database-- For this script to run properly do the following:

-- Backup the Control fiel to TraceALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- Shutdown the database to make the changesSHUTDOWN IMMEDIATE;


14/16

-- Edit the trace file and change the CREATE CONTROLFILE command-- CREATE CONTROLFILE REUSE SET DATABASE "NEW_SID_NAME" RESETLOGS-- (note the SET keyword)

Change the name in Control file and Init.ora

The first line of Control file should be "CREATE CONTROLFILE REUSE SET DATABASE ""RESETLOGS ARCHIVELOG"

- modify the db_name parameter in the init.ora

-- Startup the datbase nomount with changed PFileSTARTUP NOMOUNT;

-- Execute the create controlfile command.@create_control.sql;

-- Cancel base recovery of the database

Recover database USING BACKUP CONTROLFILE until cancel;CANCEL

-- Open resetlogs the databaseALTER DATABASE OPEN RESETLOGS;

-- Rename GLOBAL_NAME toALTER DATABASE RENAME GLOBAL_NAME TO ;

-- Create SPFile, IF required give NAME and PATH of the PFILECREATE SPFILE FROM PFILE;

select name from v$database;

(or)

You can change Database my by simple do some steps.

1. Alter Database backup control file to trace;2. Above step will create a text control file.3. Change the Database name there and in init.ora file.4. create new control file by running script (from backup control file)5. Startup mount6.open database resetlogs.

And your database name will be changed

25.What is the use of redo log informationRedo log information are used to recover database if it get currept.

26.Explain Oracle memory structure.The Oracle RDBMS creates and uses storage on the computer hard disk and in random accessmemory (RAM). The portion in the computers RAM is called memory structure. Oracle hastwo memory structures in the computers RAM. The two structures are the Program GlobalArea (PGA) and the System Global Area (SGA).

The PGA contains data and control information for a single, user process. The SGA is thememory segment that stores data that the user has retrieved from the database or data thatthe user wants to place into the database


15/16

27.What are memory structures in Oracle?The basic memory structures associated with Oracle include:

System Global Area (SGA), which is shared by all server and backgroundprocesses and holds the following:o Database buffer cacheo Redo log buffero Shared poolo Large pool (if configured) Program Global Areas (PGA), which is private to each server and backgroundprocess; there is one PGA for each process. The PGA holds the following:o Stack areaso Data areas

28.State new features of Oracle 9i.

1. Dynamic Memomry Management2. Default tablespace at the db levele3. temporary tablespace at the dba level4. Undo tablespace managemnet (auto/manual)

29.What does database do during mounting process?During database mount process, Oracle would check for the existence of controlfilesmentioned in init.ora file but it wont check the contents of the controlfile which is doneduring the opening of database.

30.What is the view name where i can get the space in MB for tables or views?

select segment_name,sum(bytes) from dba_segmentswhere segment_name='TABLE_NAME'AND OWNER='OWNER of the table'group by segment_name

31.How to DROP an Oracle Database?

You can do it at the OS level by deleting all the files of the database. The files to be deletedcan be found using:

1) select * from dba_data_files;2) select * from v$logfile;3) select * from v$controlfile;4) archive log list5) initSID.ora6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is alsoremoved. (or)Go To Dbca And Click On Delete Database

32.What is Parallel Server ?

Multiple instances accessing the same database (Only In Multi-CPU environments)


16/16

33.What are the basic element of Base configuration of an oracle Database ?

It consists of

one or more data files.one or more control files.two or more redo log files.The Database containsmultiple users/schemasone or more rollback segmentsone or more tablespacesData dictionary tablesUser objects (table,indexes,views etc.,)The server that access the database consists ofSGA (Database buffer, Dictionary Cache Buffers, Redo log buffers, Shared SQL pool)SMON (System MONito)

PMON (Process MONitor)LGWR (LoG Write)DBWR (Data Base Write)ARCH (ARCHiver)CKPT (Check Point)RECODispatcherUser Process with associated PGS

34.What is a deadlock ? Explain .

Two processes wating to update the rows of a table which are locked by the other process

then deadlock arises.In a database environment this will often happen because of not issuing proper row lockcommands. Poor design of front-end application may cause this situation and theperformance of server will reduce drastically.These locks will be released automatically when a commit/rollback operation performed orany one of this processes being killed externally.

(OR)

A deadlock is a condition where two or more users are waiting for data locked by eachother. Oracle automatically detects a deadlock and resolves them by rolling back one of the

statements involved in the deadlock, thus releasing one set of data locked by thatstatement. Statement rolled back is usually the one which detects the deadlock. Deadlocksare mostly caused by explicit locking because oracle does not do lock escalation and doesnot use read locks. Multitable deadlocks can be avoided by locking the tables in same orderin all the applications, thus precluding a deadlock. (OR)System waiting for an event that may or may not happens and can be rectified using roundrobin algorithm which implemented internally by operating systems

basics interview q s

Documents