Question 47 How to generate
the AWR report
Answer
$ORACLE_HOME/rdbms/admin/awrrpt.sql
Oracle Database 12c Related questions and articles are given
below
Question 48 What is the
difference between CPU and PSU patching?
Answer
The CPU are the Critical Patch Update, we can summarize it as
the Security bugs fixes, The new name for the critical patch updates is
security patch update (SPU). the PSU contains the CPU + some technical bugs
fixed.
Question 49 How to verify
whether a parameter changed required database bounce or not?
Answer
In v$parameter we can find one column ie . ISSYS_MODIFIABLE. This
column contains three phases 1) Immediate 2) DEFERRED 3) False
Immediate : We can change the parameter in fly database i.e
Dynamic. (only need to change the value no need to bounce)
DEFERRED : We can change the parameter in fly database but this will effect
after restart the database only. (here we need to edit using spfile and bounce
the database)
False : Compulsory we need to down the database i.e Static.
DEFERRED
False
Question 50 How do we check
if a Database upgrade is successful?
Answer
COMP_NAME VERSION
STATUS
------- ------ ------
Oracle XML
Database 11.1.2.4 VALID
Oracle Text 11.1.2.4 VALID
Question 51 How to find
Database version ?
Answer
SQL> select * from v$version;
The command returns the release information,
Question 52 How to find
opatch Version ?
Answer
Even if you
Question 53 How to find out
invalid objects in the apps schema in the database?
Answer
select count( *) from dba_objects where status =’INVALID’and owner ='APPS'
Question 54 How
you will see hidden files in linux/solaris?
Answer
Question 55 How
to find that the database is 64-bit/32-bit?
Answer
$RDBMS_ORACLE_HOME/bin/file
oracle
Question 56 What is
Answer
Question 57 Which table you
will query to check the tablespace space issues?
Answer
Question 58 Which table u
will query to check the temp tablespace space issues?
Answer
dba_temp_files
Question 59 What is temp
tablespace?
Answer
Temp tablespace is used by so many application programs for
sorting and other stuff.
Question 60 How to find the
locks and what is the resolution?
Answer
|
SELECT
inst_id
|
If it’s a dead lock , we need to kill that session.
Question 61 How to kill a database session?
Answer
Question 62 How to apply a
rdbms patch?
Answer
Using opatch
Go the patch directory
Question 63 How to find
opatch is enabled or not for u r database?
Answer
If Opatch directory exists under RDBMS_ORACLE_HOME.
Question 64 What is the pre-req for
applying a rdbms patch?
Answer
Inventory should be set in file oraInst . loc @/var/opt/oracle or
/etc and Oracle home must be registered in Central inventory
Question 65 What is
Inventory?
Answer
The inventory stores information about: All Oracle software
products installed in all ORACLE_HOMES on a machine Other non-Oracle products,
such as the Java Runtime Environment (JRE)
In a R12 Application system , the RDBMS and iAS ORACLE_HOMEs and
10.1.2 are registered in the oraInventory .
Question 66 What are
different types of inventories?
Answer
The Global inventory (or Central inventory)
The Local inventory (or Home inventory)
Question 67 What is Global
inventory or central inventory?
Answer
The Global Inventory is the part of the XML inventory that contains the high-level list of all oracle
products installed on a machine. There should therefore be only one per
machine. Its location is defined by the content of oraInst . loc. The Global
Inventory records the physical location of Oracle products installed on the
machine, such as ORACLE_HOMES (RDBMS and
IAS) or JRE. It does not have any
information about the detail of patches applied to each ORACLE_HOMEs. The
Global Inventory gets updated every time you install or de-install an
ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or
Rapid Clone.
Note: If you need to delete an ORACLE_HOME, you should always do
it through the OUI de-installer in order to keep the Global Inventory
synchronized.
Question 68 What is local
inventory?
Answer
There is one Local Inventory per ORACLE_HOME. It is physically
located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for
that ORACLE_HOME. The Local Inventory gets updated whenever a patch is
applied to the ORACLE_HOME, using OUI.
Question 69. What is AWR?
Answer
AWR is a database utility to gather database and session level performance information.
Question 70 How
to enable trace at database level?
Answer
Question 71 How
to enable trace for a session?
Answer
Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at background dump dest directory
Question 72 How to enable
trace for other session?
Answer
Example To enable trace for sql session with sid 9779
SQL> exec
sys . dbms_system. set_sql_trace_in_session( 9779,8767896, true);
PL/SQL procedure successfully completed.
To disable trace
SQL> exec
sys . dbms_system. set_sql_trace_in_session( 9779,8767896, false);
Question 73 . What is the
location of init . ora ?
Answer
$RDBMS_ORACLE_HOME/dbs
Question 74. What is that trace
files contain and the utility used to read them?
Answer
Trace file contains the detail diagnostics of a sql statement
like explain plan, physical reads, logical reads, buffer gets etc. tkproof
utility is used to convert trace file into readable format.
Question 75 What is the
syntax for tkprof ?
Answer
Question 76. What is a database
link? How to create it?
Answer
If we want to access objects of another database from this database,
then we need a database link from this database to the other.
1.Login as oracle user
2.sqlplus “/as sysdba”
3. create database link connect to identified by using ”;
create database link MY1_TO_MY2 connect to apps identified by
apps using ‘MY2′;
Database link created.
SQL> select name from v$database@ MY1_TO_MY2;
NAME
———
MY2
SQL>select db_link from dba_db_links;
Add destination database tns entry in tnsnames.ora
Add destination database tns entry in tnsnames.ora
Question 78 How to find the
database/sqlplus version?
Answer
select banner from v$version;
Question 79 How to find
operating system version?
Answer
uname –a
Question 80 . While applying
a rdbms patch using opatch you are getting the error, unable to read
inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you
will do, and how you will apply the patch?
Answer
We will check the inventory directory permission, try to apply
the patch after giving 777 permissions to that inventory directory.
We can try to create to recreate the central inventory and If
still it won’t work we will apply patch with the following command:
Opatch apply no_inventory
1) How can you see the Current SCN number of
the database?
> Select current_scn from v$database;
> Select current_scn from v$database;
2) How can you see the Current log sequence
number the log writer is writing in to?
> Select * from v$log;
> Select * from v$log;
3) If you are given a database, how will you
know how many datafiles each tablespace contains?
> Select distinct tablespace_name,file_name from dba_data_files;
> Select distinct tablespace_name,file_name from dba_data_files;
4). How will you know which temporaray
tablepsace is allocated to which user?
> Select temporary_tablespace from dba_users where username=’SCOTT’;
> Select temporary_tablespace from dba_users where username=’SCOTT’;
5) If you are given a database,how will you
know whether it is locally managed, or dictionary managed?
> Select extent_management from dba_tablespaces where tablespace_name=’USERS’;
> Select extent_management from dba_tablespaces where tablespace_name=’USERS’;
6) How will you list all the tablespaces and
their status in a database?
> Select tablespace_name,status from dba_tablespaces;
> Select tablespace_name,status from dba_tablespaces;
7) How will you find the system wide 1) default permanent
tablespace, 2) default temporary tablespace 3) Database time zone?
> Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;
> Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;
8) How will you find the current users who are
using temporary tablespace segments?
> V$TEMPSEG_USAGE
> V$TEMPSEG_USAGE
9) How will you convert an existing dictionary
managed permanent tablespace to temporary tablespace?
> Not possible
> Not possible
10) Is media recovery required if a tablespace
is taken offline immediate?
> Not required
> Not required
11) How will you convert dictionary managed
tablespace to locally managed tablespace?
> Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);
> Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);
12) If you have given command to make a
tablespace offline normal, but it’s not happening.it is in transactional
read-only mode. How will you find which are the transactions which are
preventing the conversion?
> By looking at queries using by those SID (u can get script from net). I suspect question is not clear.
> By looking at queries using by those SID (u can get script from net). I suspect question is not clear.
13) If you drop a tablespace containing 4
datafiles, how many datafiles will be dropped at a time by giving a single drop
tablespace command?
> All datafiles
14) If database is not in OMF,How will you
drop all the datafiles of a tablespace without dropping the tablespace itself?
> Alter database datafile ‘PATH’ offline drop;
> Alter database datafile ‘PATH’ offline drop;
15) How will you convert the locally managed
tablespace to dictionay managed?What are the limitations?
> Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);
> Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);
SYSTEM tablespace should be dictionary
16) Which parameter defines the max number of datafile in
database?
> Db_files and MAXDATAFILES in control file
> Db_files and MAXDATAFILES in control file
17) Can a single datafile be allocated to two tablespaces?
Why?
> No. because segments cannot space multiple datafiles
> No. because segments cannot space multiple datafiles
18) How will you check if a datafile is
Autoextinsible?
> Select autoextensible from dba_data_files where file_name=’’;
> Select autoextensible from dba_data_files where file_name=’’;
19) Write command to make all datafiles of a
tablespace offline without making the tablespace offline itself?
> Alter database datafile ‘PATH’ offline normal;
> Alter database datafile ‘PATH’ offline normal;
20) In 10g, how to allocate more than one temporary tablespace
as default temporary tablespace to a single user?
> By using temporary tablespace group
> By using temporary tablespace group
21) What is the relation between db_files and
maxdatafiles parameters?
> Both will restrict no of datafiles in the database
> Both will restrict no of datafiles in the database
22) Is it possible to make tempfiles as read only?
> yes
> yes
23) What is the common column between
dba_tablespaces and dba_datafiles?
> Tablespace_name
> Tablespace_name
24) Write a query to display the names of all
dynamic performance views?
> Select table_name from dictionary where table_name like ‘v$%’;
> Select table_name from dictionary where table_name like ‘v$%’;
25) Name the script that needs to be executed
to create the data dictionary views after database creation?
> Catalog.sql
> Catalog.sql
26) Grant to the user SCOTT the RESTRICTED
SESSION privilege?
SQL> grant restricted session to scott;
Grant succeeded.
SQL> grant restricted session to scott;
Grant succeeded.
27) How are privileged users being
authenticated on the database you are currently working on? Which
initialization parameter would give me this information?
> Question not clear
> Question not clear
28) Which dynamic performance view gives you
information about all privileged users who have been granted sysdba or sysoper
roles? Query the view?
SQL> desc v$pwfile_users
SQL> desc v$pwfile_users
29) What is the purpose of the DICTIONARY table?
> To know data dictionary and dynamic performance view names
> To know data dictionary and dynamic performance view names
30) Write a query to display the file# and the
status of all datafiles that are offline?
> Select file#,status from v$datafile where status=’OFFLINE’;
> Select file#,status from v$datafile where status=’OFFLINE’;
31) Write the statement to display the size of
the System Global Area (SGA)?
> Show parameter sga
Or
> Show sga
> Show parameter sga
Or
> Show sga
32) Obtain the information about the current
database? What is its name and creation date?
> Select name,created from v$database;
> Select name,created from v$database;
33) What is the size of the database buffer
cache? Which two initialization Parameters are used to determine this value?
> Db_cache_size or db_block_buffers
34) What value should the REMOTE_LOGIN_PASSWORDFILE take if you
need to set up Operating System authentication?
> exclusive
> exclusive
35) Which initialization parameter holds this value? What does
the shared pool comprise of?
> Library cache and data dictionary cache.
> Parameter : shared_pool_size
> Library cache and data dictionary cache.
> Parameter : shared_pool_size
36) Which initialization parameter holds the
name of the database?
> Db_name
> Db_name
37) Which dynamic performance view displays
information about the active transactions in the database? Which view returns
session related information?
> V$transaction, v$session
> V$transaction, v$session
38) Which dynamic performance view is useful
for killing user sessions? Which columns of the view will you require to kill a
user session? Write the statement to kill any of the currently active sessions
in your database?
> V$session (SID, SERAIL#)
> Alter system kill session ‘SID,SERIAL#’;
> V$session (SID, SERAIL#)
> Alter system kill session ‘SID,SERIAL#’;
39) What is the difference between the ALTER
SYSTEM and ALTER SESSION commands?
> Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session
> Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session
40) Write down the mandatory steps that a DBA
would need to perform before the CREATE DATABASE command may be used to create
a database?
> Create a pfile or spfile
> Create password file
> If windows, create instance using ORADIM utility
> Create a pfile or spfile
> Create password file
> If windows, create instance using ORADIM utility
41) What does the script utlexcpt.sql create?
What is this table used for?
> It will create EXECEPTIONS table. See
below link
42) In which Oracle subdirectory are all the
SQL scripts such as catalog.sql/ catproc.sql /utlexcpt.sql etc…? Located?
> $ORACLE_HOME/rdbms/admin/
> $ORACLE_HOME/rdbms/admin/
43) Which dynamic performance view would you
use to display the OPTIMAL size of the rollback segment RBS2. Write a query to
retrieve the OPTIMAL size and Rollback segment name?
> V$undostat (but many scripts are available in google or even in my blog)
44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Extent size
> V$undostat (but many scripts are available in google or even in my blog)
44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Extent size
45) How would I start the database if only
users with the RESTRICTED SESSION privilege need to access it?
> Startup restrict
> Startup restrict
46) Which data dictionary view would you query
to find out information about free extents in your database? Write a query to
display a count of the number of free extents in your database?
> We can use scripts. Exactly its difficult to know
> We can use scripts. Exactly its difficult to know
47) Write a query to display the tablespace
name, datafile name and type of extent management (local or dictionary) from
the data dictionary?
> You need to combine dba_data_files and dba_tablespaces
> You need to combine dba_data_files and dba_tablespaces
48) Which two types of tablespace cannot be taken offline or
dropped?
> SYSTEM and UNDO
> SYSTEM and UNDO
49) When a tablespace is offline can it be
made read only? Perform the
Required steps to confirm your answer?
> Didn’t got the answer
Required steps to confirm your answer?
> Didn’t got the answer
50) Which parameter specifies the percentage
of space in each data block that is reserved for future updates?
> PCTFREE
51) write down two reasons why automatic extent allocation for an extent may fail?
> If the disk space reached max limit
> If autoextend reached maxsize limit
> PCTFREE
51) write down two reasons why automatic extent allocation for an extent may fail?
> If the disk space reached max limit
> If autoextend reached maxsize limit
52) Query the DBA_CONSTRAINTS view and display
the names of all the constraints that are created on the CUSTOMER table?
> Select constraint_name from dba_constraints where table_name=’CUSTOMER’;
> Select constraint_name from dba_constraints where table_name=’CUSTOMER’;
53) Write a command to display the names of
all BITMAP indexes created in the database?
> Select index_name from dba_indexes where index_type=’BITMAP’;
> Select index_name from dba_indexes where index_type=’BITMAP’;
54) Write a command to coalesce the extents of
any index of your choice?
> Alter tablespace <tablespace_name> coalesce;
> Don’t know for extents
> Alter tablespace <tablespace_name> coalesce;
> Don’t know for extents
55) . What happens to a row that is bigger than a single block?
What is this called? Which data dictionary view can be queried to obtain
information about such blocks?
> Row will be chained into multiple blocks. CHAINED_ROWS is the view
> Row will be chained into multiple blocks. CHAINED_ROWS is the view
56) Write a query to retrieve the employee
number and ROWIDs of all rows that belong to the EMP table belonging to user
SCOTT?
> Select rowid,empno from scott.emp;
> Select rowid,empno from scott.emp;
58) How to compile a view? How to compile a
table?
> Alter view <view_name> compile;
> Tables cannot be compiled
> Alter view <view_name> compile;
> Tables cannot be compiled
59) What is the block size of your database
and how do you see it?
> Db_block_size
> Db_block_size
60) At one time you lost parameter file accidentally and you
don’t have any backup. How you will recreate a new parameter file with the
parameters set to previous values.?
> We can recover it from alert log file which contains non-default values
> We can recover it from alert log file which contains non-default values
61) You want to retain only last 3 backups of datafiles. How do
you go for it in RMAN?
> By configuring backup retention policy to redundancy 3
> By configuring backup retention policy to redundancy 3
1. What is an instance?
Memory/SGA + background processes.
2. What is SGA?
System/Shared Global Area.
3. What is PGA (or) what is pga_aggregate_target?
Programmable Global Area.
4. What are new memory parameters in Oracle 10g?
SGA_TARGET PGA_TARGET
5. What are new memory parameters in Oracle 11g?
MEMORY_TARGET
6. What are the mandatory background processes?
DBWR LGWR SMON PMON CKPT RECO.
7. What are the optional background processes?
ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx etc.
8. What are the new background processes in Oracle 10g?
MMAN MMON MMNL CTWR ASMB RBAL ARBx
14. What process will get data from datafiles to DB cache (Database buffer cache)?
Server process
15. Which background process will writes data to datafiles?
DBWR
16. Which background process will write undo data?
DBWR
17. What are physical components of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.
18. What are logical components of Oracle database?
Blocks, Extents, Segments, Tablespaces.
19. What is segment space management?
LMTS and DMTS.
20. What is extent management?
Auto and Manual.
21. What are the differences between LMTS and DMTS?
Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.
Memory/SGA + background processes.
2. What is SGA?
System/Shared Global Area.
3. What is PGA (or) what is pga_aggregate_target?
Programmable Global Area.
4. What are new memory parameters in Oracle 10g?
SGA_TARGET PGA_TARGET
5. What are new memory parameters in Oracle 11g?
MEMORY_TARGET
6. What are the mandatory background processes?
DBWR LGWR SMON PMON CKPT RECO.
7. What are the optional background processes?
ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx etc.
8. What are the new background processes in Oracle 10g?
MMAN MMON MMNL CTWR ASMB RBAL ARBx
14. What process will get data from datafiles to DB cache (Database buffer cache)?
Server process
15. Which background process will writes data to datafiles?
DBWR
16. Which background process will write undo data?
DBWR
17. What are physical components of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.
18. What are logical components of Oracle database?
Blocks, Extents, Segments, Tablespaces.
19. What is segment space management?
LMTS and DMTS.
20. What is extent management?
Auto and Manual.
21. What are the differences between LMTS and DMTS?
Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.
21. What is a datafile?
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes are physically stored in the datafiles allocated for a database.
22. What are the contents of control file?
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.
23. What is the use of redo log files?
24. What are the uses of undo tablespace or redo segments?
25. How undo tablespace can guarantee retain of required undo data?
Alter tablespace undo_ts retention guarantee;
26. What is ORA-01555 - snapshot too old error and how do you avoid it?
27. What is the use/size of temporary tablespace?
28. What is the use of password file?
29. How to create password file?
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
30. How many types of indexes are there?
Clustered and Non-Clustered
1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index
Implicit index and explicit index.
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organisational index, cluster index.
31. What is bitmap index & when it’ll be used?
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.
32. What is B-tree index & when it’ll be used?
B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.
33. How you will find out fragmentation of index?
AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.
analyze index validate structure;
This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.
An index should be considered for rebuilding under any of the following conditions:
* The percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
34. What is the difference between delete and truncate?
Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked.
Delete will generate undo (Delete command will log the data changes in the log file whereas the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.
35. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where unique key creates a nonclustered index by default. Primary key doesn't allow NULLs, but unique key allows one NULL only.
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes are physically stored in the datafiles allocated for a database.
22. What are the contents of control file?
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.
23. What is the use of redo log files?
24. What are the uses of undo tablespace or redo segments?
25. How undo tablespace can guarantee retain of required undo data?
Alter tablespace undo_ts retention guarantee;
26. What is ORA-01555 - snapshot too old error and how do you avoid it?
27. What is the use/size of temporary tablespace?
28. What is the use of password file?
29. How to create password file?
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
30. How many types of indexes are there?
Clustered and Non-Clustered
1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index
Implicit index and explicit index.
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organisational index, cluster index.
31. What is bitmap index & when it’ll be used?
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.
32. What is B-tree index & when it’ll be used?
B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.
33. How you will find out fragmentation of index?
AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.
analyze index validate structure;
This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.
An index should be considered for rebuilding under any of the following conditions:
* The percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.
34. What is the difference between delete and truncate?
Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked.
Delete will generate undo (Delete command will log the data changes in the log file whereas the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.
35. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where unique key creates a nonclustered index by default. Primary key doesn't allow NULLs, but unique key allows one NULL only.
36. What is the difference between schema and user?
Schema is collection of user’s objects.
37. What is the difference between SYSDBA, SYSOPER and SYSASM?
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK
SYSASM can do anything SYSDBA can do.
38. What is the difference between SYS and SYSTEM?
SYSTEM can’t shut down the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.
39. How to improve sqlldr (SQL*Loader) performance?
40. What is the difference between view and materialized view?
View is logical, will store only the query, and will always get latest data.
Mview is physical, will store the data, and may not get latest data.
Schema is collection of user’s objects.
37. What is the difference between SYSDBA, SYSOPER and SYSASM?
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK
SYSASM can do anything SYSDBA can do.
38. What is the difference between SYS and SYSTEM?
SYSTEM can’t shut down the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.
39. How to improve sqlldr (SQL*Loader) performance?
40. What is the difference between view and materialized view?
View is logical, will store only the query, and will always get latest data.
Mview is physical, will store the data, and may not get latest data.
41. What are materialized view refresh types and which is default?
Complete, fast, force(default)
42. How fast refresh happens?
43. How to find out when was a materialized view refreshed?
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
44. What is materialized view log (type)?
45. What is atomic refresh in mviews?
From Oracle 10g, complete refresh of single materialized view can do delete instead of truncate. To force the refresh to do truncate instead of delete, parameter ATOMIC_REFRESH must be set to false.
ATOMIC_REFRESH = FALSE, mview will be truncated and whole data will be inserted. The refresh will go faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE (default), mview will be deleted and whole data will be inserted. Undo will be generated. We will have access at all times even while it is being refreshed.
SQL> EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C', atomic_refresh=FALSE);
46. How to find out whether database/tablespace/datafile is in backup mode or not?
Query V$BACKUP view.
47. What is row chaining?
If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks. Can occur when the row is inserted.
48. What is row migration?
An update statement increases the amount of data in a row so that the row no longer fits in its data blocks. Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves entire row to new block.
49. What are different types of partitions?
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitioning [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.
50. What is local partitioned index and global partitioned index?
A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.
51. How you will recover if you lost one/all control file(s)?
52. Why more archivelogs are generated, when database is begin backup mode?
Complete, fast, force(default)
42. How fast refresh happens?
43. How to find out when was a materialized view refreshed?
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
44. What is materialized view log (type)?
45. What is atomic refresh in mviews?
From Oracle 10g, complete refresh of single materialized view can do delete instead of truncate. To force the refresh to do truncate instead of delete, parameter ATOMIC_REFRESH must be set to false.
ATOMIC_REFRESH = FALSE, mview will be truncated and whole data will be inserted. The refresh will go faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE (default), mview will be deleted and whole data will be inserted. Undo will be generated. We will have access at all times even while it is being refreshed.
SQL> EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C', atomic_refresh=FALSE);
46. How to find out whether database/tablespace/datafile is in backup mode or not?
Query V$BACKUP view.
47. What is row chaining?
If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks. Can occur when the row is inserted.
48. What is row migration?
An update statement increases the amount of data in a row so that the row no longer fits in its data blocks. Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves entire row to new block.
49. What are different types of partitions?
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitioning [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.
50. What is local partitioned index and global partitioned index?
A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.
51. How you will recover if you lost one/all control file(s)?
52. Why more archivelogs are generated, when database is begin backup mode?
During begin backup mode datafile headers get
freezed and as result row information cannot be retrieved as a result the
entire block is copied to redo logs as a result more redo generated and more
log switch and in turn more archive logs. Normally
only deltas (change vectors) are logged to the redo logs. When in backup mode, Oracle will write complete changed blocks to the
redo log files.
Mainly to overcome fractured blocks. Most of the cases Oracle block size is equal to or a multiple of the operating system block size.
e.g. Consider Oracle blocksize is 2k and OSBlocksize is 4k. so each OS Block is comprised of 2 Oracle Blocks. Now you are doing an update when your db is in backup mode. An Oracle Block is updating and at the same time backup is happening on the OS block which is having this particular DB block. Backup will not be consistent since the one part of the block is being updated and at the same time it is copied to the backup location. In this case we will have a fractured block, so as to avoid this Oracle will copy the whole OS block to redo logfile which can be used for recovery. Because of this redo generation is more.
53. What UNIX parameters you will set while Oracle installation?
shmmax, shmmni, shmall, sem,
54. What is the use of inittrans and maxtrans in table definition?
55. What are differences between dbms_job and dbms_schedular?
Through dbms_schedular we can schedule OS level jobs also.
56. What are differences between dbms_schedular and cron jobs?
Through dbms_schedular we can schedule database jobs, through cron we can’t set.
57. Difference between CPU & PSU patches?
CPU - Critical Patch Update - includes only Security related patches.
PSU - Patch Set Update - includes CPU + other patches deemed important enough to be released prior to a minor (or major) version release.
58. What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?
59. What are the entries/location of oraInst.loc?
/etc/oraInst.loc is pointer to central/local Oracle Inventory.
60. What is the difference between central/global inventory and local inventory?
Mainly to overcome fractured blocks. Most of the cases Oracle block size is equal to or a multiple of the operating system block size.
e.g. Consider Oracle blocksize is 2k and OSBlocksize is 4k. so each OS Block is comprised of 2 Oracle Blocks. Now you are doing an update when your db is in backup mode. An Oracle Block is updating and at the same time backup is happening on the OS block which is having this particular DB block. Backup will not be consistent since the one part of the block is being updated and at the same time it is copied to the backup location. In this case we will have a fractured block, so as to avoid this Oracle will copy the whole OS block to redo logfile which can be used for recovery. Because of this redo generation is more.
53. What UNIX parameters you will set while Oracle installation?
shmmax, shmmni, shmall, sem,
54. What is the use of inittrans and maxtrans in table definition?
55. What are differences between dbms_job and dbms_schedular?
Through dbms_schedular we can schedule OS level jobs also.
56. What are differences between dbms_schedular and cron jobs?
Through dbms_schedular we can schedule database jobs, through cron we can’t set.
57. Difference between CPU & PSU patches?
CPU - Critical Patch Update - includes only Security related patches.
PSU - Patch Set Update - includes CPU + other patches deemed important enough to be released prior to a minor (or major) version release.
58. What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?
59. What are the entries/location of oraInst.loc?
/etc/oraInst.loc is pointer to central/local Oracle Inventory.
60. What is the difference between central/global inventory and local inventory?
61. What is the use of root.sh & oraInstRoot.sh?
Answer :
Changes ownership & permissions of oraInventory
Creating oratab file in the /etc directory
In RAC, starts the clusterware stack
62. What is transportable tablespace (and across platforms)?
63. How can you transport tablespaces (TTS) across platforms with different endian formats?
Answer :
RMAN
64. What is xtss (cross platform transportable tablespace)?
65. What is the difference between restore point & guaranteed restore point?
66. What is the difference between 10g/11g OEM Grid control and 12c Cloud control?
67. What are the components of Grid control?
Answer :
OMS (Oracle Management Server)
OMR (Oracle Management Repository)
OEM Agent
68. What are the new features of 12c Cloud control?
Answer :
Changes ownership & permissions of oraInventory
Creating oratab file in the /etc directory
In RAC, starts the clusterware stack
62. What is transportable tablespace (and across platforms)?
63. How can you transport tablespaces (TTS) across platforms with different endian formats?
Answer :
RMAN
64. What is xtss (cross platform transportable tablespace)?
65. What is the difference between restore point & guaranteed restore point?
66. What is the difference between 10g/11g OEM Grid control and 12c Cloud control?
67. What are the components of Grid control?
Answer :
OMS (Oracle Management Server)
OMR (Oracle Management Repository)
OEM Agent
68. What are the new features of 12c Cloud control?
69. How to find if your Oracle database is 32 bit or 64 bit?
Answer :
execute the command "file $ORACLE_HOME/bin/oracle
70. How to find opatch Version ?
Answer :
opatch is utility to apply database/RDBMS patch, in order to find opatch version execute"$ORACLE_HOME/OPatch/opatch version"
71. What are histograms?
72. How many types of histograms are there in Oracle 11g & in Oracle 12c?
73. What is Oracle Transparent Data Encryption (TDE)?
74. What is Data Redaction and how it will protect data?
75. What is the difference between instance recovery and crash recovery ?
Answer :
Crash recovery is done automatically by SMON, in case of abnormal shutdown such as shutdown abort or any such reason.
Crash recovery
is the process by which the database is moved back to a consistent and usable
state. This is done by rolling back incomplete transactions and completing (roll
forward)committed transactions that were still in memory when the crash
occurred
Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters (RAC) database.
76. What is the difference between Local and Global Index ?
77. What is ORA-04031 and ORA-04030 and what is difference between ORA-04030 and ORA-04031 ?
Answer :
ORA-04031: unable to allocate nn bytes of shared memory
ORA-04030: out of process memory when trying to allocate nn bytes
Comments
Post a Comment