DBA FAQ
Database Administration
Question: Name the script is used to create base data dictionary tables?
Ans : $ORACLE_HOME/rdbms/admin/sql.bsq script.
Question: Which views are used for finding the database version and options ?
Ans: For database options , we can query the view called v_$option and for database version v_$version.
Question : Is it possible to set up iSQL*Plus for SYSOPER and SYSDBA privileges ?
Ans: Yes, User must be authenticated by the iSQL*Plus application server.
Question: What information is stored in Product_Component_Version?
Ans : This view stored the information about oracle database versions and all the components used in database along with their releases.
Question: I want to change the INITIAL and NEXT parameter of an existing object.How can I do that?
Ans: For an existing object , we can not change the parameter directly.To change the same , we have to drop the table and recreate with the new parameter value.
Question: What statement to execute to coalesce free space in a tablespace?
Ans: ALTER TABLESPACE COALESCE;
Question: Which data dictionary view displays the name of database’s datafiles and redo log files?
Ans: v$datafile and v$logfile views for finding datafiles and redo log file information.
Question : How can one determine the size of buffers in the buffer cache of SGA?
Ans :
Question: How we can rename a database user ?
Ans : we can not rename a database user(schema) directly. To do that , we have to create a new schema with new name and import the objects using FROMUSER and TOUSER parameter But if it is needed then you can take export of the user which has to be changed and create a new user with new name. Import the exported objects by using FROMUSER and TOUSER . Finally you can drop the previous user.
Question: Ho we can re size the data files if experiencing problem with the tablespace size?
Ans :An Oracle DBA, can modify an existing data files using the following statement
ALTER DATABASE
DATAFILE ‘datafilename’ RESIZE 100M;
Question: what is the difference between REDO and UNDO ?
Ans : Redo records contain information used to modify a database block (or page, depending on what your background is in) from a previous coherent state to a new coherent state. In other words, Redo records contain the information required in order to apply data deltas or changes to a database block.
Undo records contain information used to reverse (or Undo) the changes made to a database block, reversing it from one coherent state to a previous coherent state. This information is used for the purpose of rolling back transactions and, in the case of Oracle, providing other sessions with a point-in-time snapshot of what a database block looked like before changes were applied to it.
Question :Which oracle package can be used for sending mails.
Ans : UTL_MAIL package is used for sending mails in Oracle 10g
Question: How can schedule job in oracle 10G?
Ans : DBMS_SCHEDULER package is the best utility to schedule any jobs. Oracle 10G user earlier DBMS_JOB package for backward compatibility.
In addition, we can create customize scripts(shell or Perl) and schedule task through corn jobs.
Question : How can I get the amount of physical reads by any query?
Ans : Physical reads can be find out using the following query
SELECT begin_interval_time,
filename,
phyrds
FROM
dba_hist_filestatxs
natural join
dba_hist_snapshot;
Question: What is the difference between a database and an instance?
Ans : The oracle database comprises of the set of files to sore application data and metadata of the database.
An oracle instance is the software environment that oracle uses to manipulate the data in the database.
To manipulate data of the database ,the instance must open the database.A database can be opened (or mounted) by more than one instance,
however, an instance can open at most one database.
Question: What is the difference between V$ and V_$ views ?
Ans : v$ views are dynamic performance view and actual dynamic performance views are identified by the v_$.V$ views are Public synonyms of V_$.We should access the v$ views not v_$.
Question: What is the difference between V$ and GV$ views
Ans : Oracle has a corresponding GV$ (global V$) view for Every V$ view.
In addition to the V$ information, each GV$ view contains an extra column named INST_ID of data type NUMBER.The INST_ID column displays the instance number from which the associated V$ view information was obtained.
Question: How do you find out the UNIX process id of a database session?
Ans : Querying v$session view. This view contains two column called OSUSER and PRECESS , which stores information about the Operating system user and session process id.
Question: How to check the Instance Name, Host Name, and Instance Startup time of an Oracle Database?
Ans : Querying the dictionary view v$instance
SQL> select instance_name,host_name,startup_time from v$instance;
INSTANCE_NAME HOST_NAME STARTUP_T
—————- —————————— ———
orcl GNINMUMPC065 15-OCT-09
Question: How to determine redo log size?
Ans: Redo log size can be calculated on basis of redo log switches.Generally it is recommended to size your redo log file in a way that Oracle performs a log switch every 15 to 30 minutes.Optimal redo log size can the queried using the following statement
select optimal_logfile_size from v$instance_recovery
Oracle database 10G Redo Logfile Size Advisor is a great advisory utility to determine optimal online redo log file size.You can also check messages in the alert log in order to determine how fast Oracle is filling and switching logs. Suppose if your database redo log file size is set to 1MB. It means that Oracle switches the logs every 1 minute. So you will need to increase the size of redo log file to 30MB so that Oracle switches per 30 minutes
Question :How to determine redo log size?
Ans : Redo log size can be calculated on basis of redo log switches
Question : How to resolve the following database issue ?
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 241 not archived, no available destinations
ORA-00312: online log 3 thread 1:
‘E:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG’
Ans : alter system set db_recovery_file_dest_size=3G scope=both will resolve the issue
Question : How we can check that that database is running using spfile ?
Ans : Issuing the following query
select * from v$parameter where name=’spfile’
or
At SQL prompt : show parameter spfile command
Question : How we can find out grougth of a table ?
Ans : Suppose we have to find out grougth of EMP table available in SCOTT schema. Following query will find out the grougth of the table
SELECT segment_name,BLOCKS*2048/1024 “KB”
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(’SCOTT’) AND SEGMENT_NAME = UPPER(’EMP’)
MINUS
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 “KB”
FROM DBA_TABLES
WHERE OWNER=UPPER(’SCOTT’) AND TABLE_NAME = UPPER(’EMP’);
Question : How we can find total Size of a tablespace ?
Ans : Total size of a tablespace is depends on the size of the datafiles available under the tablespace. Following query find out the total size of a tablespace.
SELECT SUM(bytes)/1024/1024 “TOTAL SIZE(MB)” from dba_Data_files
Question :How can find Free Space of tablespace ?
Ans : SELECT SUM(bytes)/1024/1024 “FREE SPACE(MB)” from dba_free_space
Question : How can find total size ,free space and used space in tablespace ?
Ans : SELECT t.total “TOTAL SIZE ( MB)”,
f.free “FREE SPACE(MB)”,(free.free/tot.total)*100 “FREE (%)” ,
(1-f.free/t.total)*100 “USED (%)”
FROM (SELECT SUM(bytes)/1024/1024 free from dba_free_space) f ,
(SELECT SUM(bytes)/1024/1024 total from dba_Data_files) t
