Oracle DBA Interview Questions

kill -9

kill -9 (process)

Sp file is static and p file is dynamic

SP file is more advantage than P file SP File: SP file is available from above 9i versions.SP File is a server side file,Dynamic file,no need to restart when ever changes happen.u cant edit the SP f...

Before 11g sga_target from 11g onwards memory_target is mandatory

mandatory parameter are:
1.db_cache_size
2.log_buffer_size
3.shared_pool_size
4.sga_max_size

No its cannot possible. In any version we have by default one spfile. If Pfile is exitsted then you startup database.

In Oracle 10g, the database is started automatically using pfile (if there is no spfile) when we issue the startup command.

We dont need to specify to Oracle to use pfile always.(Linux OS)

Inner layer

Server process

Data always copied in buffer cache from data file by only Server Process not DBWR.

a database link is a path to another database with the help of which a user can access the schema objects of later database.

Database link is an implementation of chaining.The database link behaves like a database but has no persistent storage.Instead,it points to data stored remotely.

alter table move tablespace USERS

using alter table command we can move one table from one schema to other

Code
SQL>create TABLE t1 
unrecoverable AS SELECT * FROM other_schema.t1;
 

SQL> select a.datafile_size + b.temp_size + c.redo_size + d.controlfile_size "Total_size in GB" from ( select sum(bytes)/1024/1024/1024 as datafile_size from dba_data_files) a, ( select nvl(sum(by...

select sum ( bytes) from dba_data_files ;
select sum(bytes) from v$log;
select sum ( bytes ) from dba_temp_files ;
Total sum of all the three will give the database size .

view does not consume space and materialized view consume space

A view is a simple select statement executed each time user runs query against it A materialized view is a physical persistent object used for performance improvement of summary data or replication. ...

Code
SQL> ALTER DATABASE backup controlfile TO trace AS <PATH> 

we can do this by suding RMAN:
configure control file auto backup on;

Code
SELECT STATUS FROM v$instance

select instance_name, status, database_status, startup_time from v$instance;

The transportabel tablespaces enable us to transport data objects across different platforms.

Backing up or recovering a particular user/schema objects or data objects or tablespaces or entire databases is possible, which cannot be done using other backup techniques.

Moving data using transportable tablespace can be much faster than performing either export/import or unload/load of the same data.Bcz transporting a tablespace only requires the copying of datafiles & integrating the tablespace structural information.

SCN is generated by oracle when a transaction is successfully commited. This is referred to as SystemChangeNumber. We can think of it as a transaction id generated when we make some transaction in an ...

SCN no is a number which is generated by the oracle, when we made any changes to the data that statements will be recorded in redo log files and every committed statement will be given this SCN no by oracle.

We can also try the explainplan and tkprof utilities.

I think we should check the indexing structure.May be for that it took more time.

control file is used to startup the database from nomount stage to mount stage. Firstly, when the database is started it looks for the path of control file in the parameter file and the database is now in the nomount stage. Now the database looks for the control files to startup the database.

Control file contain metadata of the database.And also containing as follows,
Name of the database & when was database created.
Name & location of datafiles,redolog files.
Backup information(RMAN).
SCN information stored into it.

Using the storage options can bring further performance gains by using the techniques available for regular tables, including parallel option, partitioning or nologging for the table that holds the materialized view data.DRDPW

Materialized views are useful for fast REPLICATION purpose..

Schema is a set of objects own by a Particular user..

Schema is collection of database object.

SQLNET.ORA :: It is the optional network configuration file.it contain network configuration details.
LISTINER.ORA : It is the listener file for creating at server side
TNSNAMES.ORA:client side configuration file.it was matched with listener file.it for remote access.

SQLNET.ora tells you what path to take in resolving the name of an instance (among other things). In the context of this question, the NAMES.DIRECTORY_PATH parameter specifys the order of the naming methods used for client name resolution lookups. (TNSNAMES, ONAMES...)

Schema is a set of objects own by particular user.
Tables,indexes,Triggers,constraints etc..are schema objects..

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users. Objects may define areas ...