RSS Feed (xml)
(A) What is Manifest?
Assembly metadata is stored in Manifest. Manifest contains all the metadata needed to do the following things (See Figure Manifest View for more details):
· Version of assembly
· Security identity
· Scope of the assembly
· Resolve references to resources and classes.
· The assembly manifest can be stored in either a PE file (an .exe or .dll) with Microsoft intermediate language (MSIL) code or in a stand-alone PE file that contains only assembly manifest information.
(B)Where is version information stored of an assembly ?
Version information is stored in assembly in manifest.
(I)Is versioning applicable to private assemblies?
Versioning concept is only applicable to global assembly cache (GAC) as private assembly lie in their individual folders.
(B) What is GAC ?
Twist :- What are situations when you register .NET assembly in GAC ?
GAC (Global Assembly Cache) is used where shared .NET assembly reside. GAC is used in the following situations :-
· If the application has to be shared among several application.
· If the assembly has some special security requirements like only administrator scan remove the assembly. If the assembly is private then a simple delete of assembly the assembly file will remove the assembly. Note :- Registering .NET assembly in GAC can lead to the old problem of DLL hell, where COM version was stored in central registry. So GAC should be used when absolutely necessary.
(I) What is the concept of strong names ?
Twist :- How do we generate strong names ?
Twist :- What is use the of SN.EXE ?
Twist :- How do we apply strong names to assembly?
Twist :- How do you sign an assembly?
Strong name is similar to GUID(It is supposed to be unique in space and time) in COM components. Strong Name is only needed when we need to deploy assembly in GAC. Strong Names helps GAC to differentiate between two versions. Strong names use public key cryptography (PKC) to ensure that no one can spoof it. PKC use public key and private key concept. Following are the step to generate a strong name and sign a assembly :-
Go to “Visual Studio Command Prompt”. See below figure “Visual studio Command Prompt”. Note the samples are compiled in 2005 but 2003 users do ot have to worry about it. Same type of command prompt will be seen in2003 also.
After you are in command prompt type sn.exe -k “c:\test.snk”.
After generation of the file you can view the SNK file in a simple notepad.
After the SNK file is generated its time to sign the project with this SNK file
Click on project -- properties and the browse the SNK file to the respective folder and compile the project.
Click on Use a key file to sign the assembly with strong name
(I)How to add and remove an assembly from GAC?There are two ways to install .NET assembly in GAC:-vUsing Microsoft Installer Package. You can get download of installer fromhttp://www.microsoft.com.vUsing Gacutil. Goto “Visual Studio Command Prompt” and type “gacutil –i(assembly_name)”, where (assembly_name) is the DLL name of the project.
(B) What is Delay signing ?During development process you will need strong name keys to be exposed to developer whichis not a good practice from security aspect point of view.In such situations you can assign the keylater on and during development you an use delay signingFollowing is process to delay sign an assembly:vFirst obtain your string name keys using SN.EXE.vAnnotate the source code for the assembly with two custom attributes fromSystem.Reflection: AssemblyKeyFileAttribute, which passes the name of the filecontaining the public key as a parameter to its constructor. AssemblyDelaySignAttribute,which indicates that delay signing, is being used by passing true as a parameter to itsconstructor. For example as shown below:[Visual Basic]
(B)What is garbage collection?Garbage collection is a CLR feature which automatically manages memory. Programmers forgetto release the objects while coding ..... Laziness (Remember in VB6 where one of the goodpractices is to set object to nothing). CLR automatically releases objects when they are no longer inuse and refernced. CLR runs on non-deterministic to see the unused objects and cleans them. Oneside effect of this non-deterministic feature is that we cannot assume an object is destroyed whenit goes out of the scope of a function.we should avoid using destructors because before GCdestroys the object it first executes destructor in that case it will have to wait for code to releasethe umanaged resource. resultin in additional delays in GC. So its recommended to implementIDisposable interface and write cleaup code in Dispose method and call GC.SuppressFinalizemethod so instructing GC not to call your constructor. For more details read Why is it preferredto not use finalize for clean up? in OOPS chapter..
(I) Can we force garbage collector to run ?System.GC.Collect() forces garbage collector to run. This is not recommended but can be used ifsituations arises.
(B)What is reflection?All .NET assemblies have metadata information stored about the types defined in modules. Thismetadata information can be accessed by mechanism called as “Reflection”.System. Reflectioncan be used to browse through the metadata information.Using reflection you can also dynamically invoke methods using System.Type.Invokemember.Below is sample source code if needed you can also get this code from CD provided, go to“Source code” folder in “Reflection Sample” folder.Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles MyBase.Load Dim Pobjtype As Type Dim PobjObject As Object Dim PobjButtons As New Windows.Forms.Button() Pobjtype = PobjButtons.GetType()81 For Each PobjObject In Pobjtype.GetMembers LstDisplay.Items.Add(PobjObject.ToString()) Next End SubEnd ClassNote :- Sample source code are compiled using VB.NET 2005.Figure:- 1.9 Sample reflection displaySample source code uses reflection to browse through “Button” class of “Windows.Forms”. Ifyou compile and run the program following is output as shown in “Sample Reflection Display”.Using reflection you can also dynamically invoke a method using “System.Type.InvokeMember”.Note :- System.Type.InvokeMember is left as homework for readers. Believe me you willenjoy doing it yourself and the concept of reflection will be clearer.
(P)What are different types of JIT ?Note :- This question can only be asked when the interviewer does not know what he wants.It was asked to me in one of interview and for 15 minutes he was roaming around the82same question in order to get answer from me (requirement was for a simple databaseproject). Beware of such companies and interviewers you can land up no where.JIT compiler is a part of the runtime execution environment.In Microsoft .NET there are three types of JIT compilers:vPre-JIT :- Pre-JIT compiles complete source code into native code in a singlecompilationcycle. This is done at the time of deployment of the application.vEcono-JIT :- Econo-JIT compiles only those methods that are called at runtime.However, these compiled methods are removed when they are not required.vNormal-JIT :- Normal-JIT compiles only those methods that are called at runtime.These methods are compiled the first time they are called, and then they are stored incache. When the same methods are called again, the compiled code from cache isused for execution.
(B) What are Value types and Reference types ?Value types directly contain their data which are either allocated on the stack or allocated in-line ina structure.Reference types store a reference to the value's memory address, and are allocated on the heap.Reference types can be self-describing types, pointer types, or interface types.Variables that are value types each have their own copy of the data, and therefore operations onone variable do not affect other variables. Variables that are reference types can refer to the sameobject; therefore, operations on one variable can affect the same object referred to by anothervariable. All types derive from the System.Object base type.
(B) What is concept of Boxing and Unboxing ?Boxing permits any value type to be implicitly converted to type object or to any interface typeimplemented by value type. Boxing is a process in which object instances are created and copyvalues in to that instance.Unboxing is vice versa of boxing operation where the value is copied from the instance in toappropriate storage location.Below is sample code of boxing and unboxing where integer data type is converted in to objectand then vice versa.Dim x As Integer83Dim y As Objectx = 10‘ boxing processy = x‘ unboxing processx = y
(B) What is the difference between VB.NET and C# ?Well this is the most debatable issue in .NET community and people treat there languages likereligion. Its a subjective matter which language is best. Some like VB.NET’s natural style and somelike professional and terse C# syntaxes. Both use the same framework and speed is also very muchequivalents. But still let’s list down some major differences between them :-Advantages VB.NET :-vHas support for optional parameters which makes COM interoperability much easy.vWith Option Strict off late binding is supported.Legacy VB functionalities can beused by using Microsoft.VisualBasic namespace.vHas the WITH construct which is not in C#.vThe VB.NET part of Visual Studio .NET compiles your code in the background.While this is considered an advantage for small projects, people creating very largeprojects have found that the IDE slows down considerably as the project gets larger.Advantages of C#vXML documentation is generated from source code but this is now been incorporatedin Whidbey.vOperator overloading which is not in current VB.NET but is been introduced inWhidbey.v Use of this statement makes unmanaged resource disposal simple.vAccess to Unsafe code. This allows pointer arithmetic etc, and can improveperformance in some situations. However, it is not to be used lightly, as a lot of thenormal safety of C# is lost (as the name implies).This is the major difference that youcan access unmanaged code in C# and not in VB.NET.84* How much ever this book tries it can not match the huge variations of questions that havebeen asked in.NET interviews.But note there will be variations and they will map to somequestion of this book.
(I)What is the difference between System exceptions and Applicationexceptions?All exception derives from Exception Base class. Exceptions can be generated programmaticallyor can be generated by system. Application Exception serves as the base class for all application-specific exception classes. It derives from Exception but does not provide any extended functionality.You should derive your custom application exceptions from Application Exception.Application exception is used when we want to define user defined exception, while systemexception is all which is defined by .NET.Figure :- 1.10 Exception Hierarchy85Note:- Frankly I have always relied on using Microsoft exception application blocks. Assuch I have never used application exception; I think most of the work is done using Systemexception classes.
(I)What is CODE Access security?CAS is part of .NET security model that determines whether or not a piece of code is allowed torun and what resources it can use while running. Example CAS will allow an application to readbut not to write and delete a file or a resource from a folder..
(A)How to prevent my .NET DLL to be decompiled?By design .NET embeds rich Meta data inside the executable code using MSIL. Any one can easilydecompile your DLL back using tools like ILDASM (owned by Microsoft) or Reflector for.NET which is a third party. Secondly there are many third party tools which make this decompilingprocess a click away. So any one can easily look in to your assemblies and reverse engineer themback in to actual source code and understand some real good logic which can make it easy tocrack your application.The process by which you can stop this reverse engineering is using “obfuscation”. It’s a techniquewhich will foil the decompilers. There are many third parties (XenoCode, Demeanor for .NET)which provide .NET obfuscation solution. Microsoft includes one that is Dotfuscator CommunityEdition with Visual Studio.NET.Note: - I leave this as homework to reader’s compile, a DLL obfuscate it using“Dotfuscator Community Edition” which comes with Visual Studio.NET and try viewingthe same using ILDASM.
(I) What is the difference between Convert.toString and .toString()method ?Just to give an understanding of what the above question means seethe below code.int i =0;MessageBox.Show(i.ToString());MessageBox.Show(Convert.ToString(i));86We can convert the integer “i” using “i.ToString()” or “Convert.ToString” so what’s the difference.The basic difference between them is “Convert” function handles NULLS while “i.ToString()”does not it will throw a NULL reference exception error. So as good coding practice using“convert” is always safe.
Monday, May 26, 2008
.net Interview Questions
Oracle Interview Questions - Part 4 Interview question
Where is the external query executed at the client or the server?
At the server.
Where is a procedure return in an external pl/sql library executed at the client or at the server?
At the client.
What is coordination Event?
Any event that makes a different record in the master block the current record is a coordination causing event.
What is the difference between OLE Server & Ole Container?
An Ole server application creates ole Objects that are embedded or linked in ole Containers ex. Ole servers are ms_word & ms_excel. OLE containers provide a place to store, display and manipulate objects that are created by ole server applications. Ex. oracle forms is an example of an ole Container.
What is an object group?
An object group is a container for a group of objects; you define an object group when you want to package related objects, so that you copy or reference them in other modules.
What is an LOV?
An LOV is a scrollable popup window that provides the operator with either a single or multi column selection list.
At what point of report execution is the before Report trigger fired?
After the query is executed but before the report is executed and the records are displayed.
What are the built -ins used for Modifying a groups structure?
ADD-GROUP_COLUMN (function)
ADD_GROUP_ROW (procedure)
DELETE_GROUP_ROW(procedure)
What is an user exit used for?
A way in which to pass control (and possibly arguments ) form Oracle report to another Oracle products of 3 GL and then return control ( and ) back to Oracle reports.
What is the User-Named Editor?
A user named editor has the same text editing functionality as the default editor, but, because it is a named object, you can specify editor attributes such as windows display size, position, and title.
My database was terminated while in BACKUP MODE, do I need to recover? (for DBA)
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle7.2, you can simply take the individual datafiles out of backup mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time.
Thiru Vadivelu contributed the following:
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hot backup mode:
ALTER DATABASE END BACKUP;
The above commands need to be issued when the database is mounted.
What is a Static Record Group?
A static record group is not associated with a query, rather, you define its structure and row values at design time, and they remain fixed at runtime.
What is a record group?
A record group is an internal Oracle Forms that structure that has a column/row framework similar to a database table. However, unlike database tables, record groups are separate objects that belong to the form module which they are defined.
My database is down and I cannot restore. What now? (for DBA )
Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!!
I've lost my REDOLOG files, how can I get my DB back? (for DBA)
The following INIT.ORA parameter may be required if your current redo logs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it. _allow_resetlogs_corruption = true
What is a property clause?
A property clause is a named object that contains a list of properties and their settings. Once you create a property clause you can base other object on it. An object based on a property can inherit the setting of any property in the clause that makes sense for that object.
What is a physical page ? & What is a logical page ?
A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.
I've lost some Rollback Segments, how can I get my DB back? (for DBA)
Re-start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch.
Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed. One can very well end up with lost or inconsistent data!!! Please contact Oracle Support before using it. _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04)
What are the differences between EBU and RMAN? (for DBA)
Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7's EBU utility. However, there is no direct upgrade path from EBU to RMAN.
How does one create a RMAN recovery catalog? (for DBA)
Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys
SQL>create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script. rman catalog rman/rman
RMAN>create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
How can a group in a cross products be visually distinguished from a group that does not form a cross product?
A group that forms part of a cross product will have a thicker border.
What is the frame & repeating frame?
A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.
What is a combo box?
A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.
What are three panes that appear in the run time pl/sql interpreter?
1. Source pane.
2. interpreter pane.
3. Navigator pane.
What are the two panes that Appear in the design time pl/sql interpreter?
1. Source pane.
2. Interpreter pane
What are the two ways by which data can be generated for a parameters list of values?
1. Using static values.
2. Writing select statement.
What are the various methods of performing a calculation in a report ?
1. Perform the calculation in the SQL statements itself.
2. Use a calculated / summary column in the data model.
What are the default extensions of the files created by menu module?
.mmb,
.mmx
What are the default extensions of the files created by forms modules?
.fmb - form module binary
.fmx - form module executable
To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?
& physical page no.
It is possible to use raw devices as data files and what is the advantages over file. system files ?
Yes. The advantages over file system files. I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very less.
What are disadvantages of having raw devices ?
We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries.
What is the significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.,
What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment toexpand into anotion Completes. e. will be written.
What is the use of FILE option in IMP command ?
The name of the file from which import should be performed.
What is a Shared SQL pool?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
What is hot backup and how it can be taken?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.
List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?
SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.
How to implement the multiple control files for an existing database ?
Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control file. name Restart the database.
What is advantage of having disk shadowing/ Mirroring ?
Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.
How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
Why query fails sometimes ?
Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments.
What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.
How will you monitor rollback segment status ?
Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or corupted.
PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database.
What is meant by Redo Log file mirroring ? How it can be achieved?
Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.
Which parameter in Storage clause will reduce no. of rows per block?
PCTFREE parameter
Row size also reduces no of rows per block.
What is meant by recursive hints ?
Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the difference between locks, latches, enqueues and semaphores? (for DBA)
A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET is used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.
Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.
Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:
semmns = sum of the "processes" parameter for each instance
(see init.ora for each instance)
semmni = number of instances running simultaneously;
semmsl = semmns
What is a logical backup?
Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
Where can one get a list of all hidden Oracle parameters? (for DBA)
Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported parameters. One can get a list of all hidden parameters by executing this query:
select *
from SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';
The following query displays parameter names with their current value:
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
order by a.ksppinm;
Remember: Thou shall not play with undocumented parameters!
What is a database EVENT and how does one set it? (for DBA)
Oracle trace events are useful for debugging the Oracle database server. The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.
Either adding them to the INIT.ORA parameter file can activate events. E.g.
event='1401 trace name errorstack, level 12'
... or, by issuing an ALTER SESSION SET EVENTS command: E.g.
alter session set events '10046 trace name context forever, level 4';
The alter session method only affects the user's current session, whereas changes to the INIT.ORA file will affect all sessions once the database has been restarted.
What is a Rollback segment entry ?
It is the set of before image data blocks that contain rows that are modified by a transaction. Each Rollback Segment entry must be completed within one rollback segment. A single rollback segment can have multiple rollback segment entries.
What database events can be set? (for DBA)
The following events are frequently used by DBAs and Oracle Support to diagnose problems:
" 10046 trace name context forever, level 4 Trace SQL statements and show bind variables in trace output.
" 10046 trace name context forever, level 8 This shows wait events in the SQL trace files
" 10046 trace name context forever, level 12 This shows both bind variable names and wait events in the SQL trace files
" 1401 trace name errorstack, level 12 1401 trace name errorstack, level 4 1401 trace name processstate Dumps out trace information if an ORA-1401 "inserted value too large for column" error occurs. The 1401 can be replaced by any other Oracle Server error code that you want to trace.
" 60 trace name errorstack level 10 Show where in the code Oracle gets a deadlock (ORA-60), and may help to diagnose the problem.
The following lists of events are examples only. They might be version specific, so please call Oracle before using them:
" 10210 trace name context forever, level 10 10211 trace name context forever, level 10 10231 trace name context forever, level 10 These events prevent database block corruptions
" 10049 trace name context forever, level 2 Memory protect cursor
" 10210 trace name context forever, level 2 Data block check
" 10211 trace name context forever, level 2 Index block check
" 10235 trace name context forever, level 1 Memory heap check
" 10262 trace name context forever, level 300 Allow 300 bytes memory leak for connections
Note: You can use the Unix oerr command to get the description of an event. On Unix, you can type "oerr ora 10053" from the command prompt to get event details.
How can one dump internal database structures? (for DBA)
The following (mostly undocumented) commands can be used to obtain information about internal database structures.
o Dump control file contents
alter session set events 'immediate trace name CONTROLF level 10'
/
o Dump file headers
alter session set events 'immediate trace name FILE_HDRS level 10'
/
o Dump redo log headers
alter session set events 'immediate trace name REDOHDR level 10'
/
o Dump the system state
NOTE: Take 3 successive SYSTEMSTATE dumps, with 10-minute intervals alter session set events 'immediate trace name SYSTEMSTATE level 10'
/
o Dump the process state
alter session set events 'immediate trace name PROCESSSTATE level 10'
/
o Dump Library Cache details
alter session set events 'immediate trace name library cache level 10'
/
o Dump optimizer statistics whenever a SQL statement is parsed (hint: change statement or flush pool) alter session set events '10053 trace name context forever, level 1'
/
o Dump a database block (File/ Block must be converted to DBA address) Convert file and block number to a DBA (database block address).
Eg: variable x varchar2;
exec :x := dbms_utility.make_data_block_address(1,12);
print x
alter session set events 'immediate trace name blockdump level 50360894'
/
What are the different kind of export backups?
Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date.
Cumulative backup - Only affected table from the last cumulative date/full backup date.
How free extents are managed in Ver 6.0 and Ver 7.0 ?
Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the neighboring free extent in Ver 7.0
What is the use of RECORD option in EXP command?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created.
What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.
How will you swap objects into a different table space for an existing database ?
Export the user
Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql.
This will create all definitions into newfile.sql. Drop necessary objects.
Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.
How does Space allocation table place within a block ?
Each block contains entries as follows
Fixed block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free space for row updation in future)
What are the factors causing the reparsing of SQL statements in SGA?
Due to insufficient Shared SQL pool size. Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE. LOGICAL & PHYSICAL ARCHITECTURE OF DATABASE.
What is dictionary cache ?
Dictionary cache is information about the databse objects stored in a data dictionary table.
What is a Control file ?
Database overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.
What is Database Buffers ?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
How will you create multiple rollback segments in a database ?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace. Create a Second Rollback Segment name R0 in the SYSTEM tablespace. Make new rollback segment available (After shutdown, modify init.ora file and Start database) Create other tablespaces (RBS) for rollback segments. Deactivate Rollback Segment R0 and activate the newly created rollback segments.
What is cold backup? What are the elements of it?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)
What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.
How will you estimate the space required by a non-clustered tables?
Calculate the total header size
Calculate the available dataspace per data block
Calculate the combined column lengths of the average row
Calculate the total average row size.
Calculate the average number rows that can fit in a block
Calculate the number of blocks and bytes required for the table.
After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.
How will you monitor the space allocation ?
By querying DBA_SEGMENT table/view.
What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
What is the use of IGNORE option in IMP command ?
A flag to indicate whether the import should ignore errors encounter when issuing CREATE commands.
What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
What is the use of ROWS option in IMP command ?
A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed.
What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.
What is the use of INDEXES option in IMP command ?
A flag to indicate whether import should import index on tables or not.
What is the use of GRANT option in EXP command?
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.
What is the use of GRANT option in IMP command ?
A flag to indicate whether grants on database objects will be imported.
What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.
What is the use of SHOW option in IMP command ?
A flag to indicate whether file content should be displayed or not.
What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.
What is the use of CONSISTENT (Ver 7) option in EXP command ?
A flag to indicate whether a read consistent version of all the exported objects should be maintained.
What are the different methods of backing up oracle database ?
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)
What is the difference between ON-VALIDATE-FIELD trigger and a POST-CHANGE trigger ?
When you changes the Existing value to null, the On-validate field trigger will fire post change trigger will not fire. At the time of execute-query post-change trigger will fire, on-validate field trigger will not fire.
When is PRE-QUERY trigger executed ?
When Execute-query or count-query Package procedures are invoked.