Oracle DBA interview questions

By karthick , in , 0 Comments


Oracle DBA interview questions

Explain the difference between a hot backup and a cold backup.
A cold backup is done when there is no user activity going on with the system. Also called as offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made.
A hot backup is taken when the database needs to run all the time. It is an online backup. All files of the database are copied and there may be changes to the database during the copy.

How many memory layers are in the oracle shared pool? Explain them
Oracles shared pool consists of two layers namely, Library cache and Data dictionary cache.
Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.
Data Dictionary cache: this Layer has information about the accounts of the users, their privileges and segments information.

What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
When the cache client like a CPU, web browser etc finds a successful entry in the cache memory, it is called as a cache hit. The percentage of these successful cache hits is called as cache hit ratio. Higher the cache hit ratio better will be the performance because data read from the cache is faster than the memory.



Explain the concept of the DUAL table.
On installing Oracle database, DUAL table Is present by default. It is a special table with just one row. It has a single column called DUMMY. The data type of this column in VARCHAR2(1). It has a value “X”. It is most commonly used to select pseudo columns in Oracle like sysdate.
Select sysdate from dual

What are the ways tablespaces can be managed and how do they differ?
Objects can be assigned to a table space. The related objects can be then grouped together. Table space can also be managed using extents. Extents consist of a specific number of contiguous data blocks. For the required extent, the free extent closest in size is determined.

Explain what are Oracle Catalog and Oracle Archive log?
Oracle catalog contains tables and views to get information about the database. It helps user to understand the available tables, attributes, constraints etc.
Oracle Archive log mode of a database in Oracle, ensures, the online redo logs are not overwritten before they are archived. This ensures that recovery is possible.
What are PCT Free and PCT Used? What is PCT increase parameter in segment?
PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.
PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.
PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %.

What is dump destination? What are bdump, cdump and udump?
Trace files for Oracle processes are stored in dump destination.
Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.
Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.
Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.

How do you increase the performance of %LIKE operator?
LIKE% works the fastest because it uses the index to search on the column provided an index is specified on the column. Using % after LIKE, results in faster results.

Why use materialized view instead of a table
Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

Why and how the deadlock situation arises
A deadlock situation arises when two or more users wait for the same resource locked by one anther or two or more processes wait to update rows which are locked by other processes. Oracle if detects a deadlock, rolls back the session chosen by the deadlock victim.



What are standby databases? Difference between Physical and logical standby databases
A standby database is a replica of the original database. In order to keep both the database synchronized, archived redo logs can be used. It is mainly used in disaster protection. It can also be opened in read only mode which allows it to be used independently for reporting.
A logical standby database allows new database objects like tables, indexes to be added to the database. On the other hand, the physical standby database is a physical or structural copy of primary database. They can be opened in read only for disaster recovery.

What is Cache Fusion Technology?
In Cache fusion, multiple buffers join to act as one. It eliminates disk i/o operaions by making use of a scalable shared cache. It treats multiple buffer caches as one thereby resolving data consistency issues. Cash fusion technology can provide more resources and increases concurrency of users.

What is the difference between Cloning and Standby databases?
The clone database is a copy of the database which can be opened in read write mode. It is treated as a separate copy of the database that is functionally completely separate. The standby database is a copy of the production database used for disaster protection. In order to update the standby database; archived redo logs from the production database can be used. If the primary database is destroyed or its data becomes corrupted, one can perform a failover to the standby database, in which case the standby database becomes the new primary database.


What is dump destination? What are bdump, cdump and udump? 

Trace files for Oracle processes are stored in dump destination.

Bdump- Oracle writes to the trace log and creates trace files for background processes in background dump destination. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Cdump- Oracle writes core files and background processes in Core dump destination directory. If this directory becomes full and more files cannot be written, debugging becomes difficult.

Udump – Oracle creates trace files for the user processes in the User Dump directory if this directory becomes full and more files cannot be written, debugging becomes difficult.


What are PCT Free and PCT Used? What is PCT increase parameter in segment? 

PCTFREE is a parameter used to find how much space should be left in a database block for future updates. This means that if the PCTFREE = 20, new rows will be added in the block until it is 80% full.

PCTUSED is a parameter helps Oracle to find when it should consider a database block to be empty enough to be added to the freelist. This means that if the PCTFREE = 50, new rows will be not be added in the block until sufficient rows are deleted from the block so that it falls below 40% empty.

PCTINCREASE parameter is used to find how much will the each subsequent segment will grow. This value is in %


Explain what are Oracle Catalog and Oracle Archive log? 

Oracle catalog contains tables and views to get information about the database. It helps user to understand the available tables, attributes, constraints etc.

Oracle Archive log mode of a database in Oracle, ensures, the online redo logs are not overwritten before they are archived. This ensures that recovery is possible






SQL Server DBA interview questions

By karthick , in , 1 Comment



SQL Server DBA interview questions
What are the steps to take to improve performance of a poor performing query?
  • Maximum use of indexes, stored procures should be done.
  • Avoid excessive use of complicated joins and cursors.
  • Avoid using conditional operators using columns of different tables.
  • Make use of computed columns and rewriting the query.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
A deadlock occurs when two or more processes waits for a resource that is acquired by or is under the control of another process. A live lock is similar to a deadlock except the process states keeps changing. The result of such state is that none of the process will be complete.
Deadlock detection finds and resolves deadlocks. A WFG strategy is followed. WFG is wait for graph. In WFG, processes are represented by nodes while dependencies are represented by edges. Thus, if process A is waiting for a resource held by process B, there is an edge in the WFG from the node for process A to the node for process B. a cycle is this graph is a deadlock. WFG constantly checks for cycles or when a process is blocked and adds a new edge to the WFG.   When a cycle is found, a victim is selected and aborted.

What is blocking and how would you troubleshoot it?
Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.
Troubleshooting blocking:
  • SQL scripts can be written that constantly monitor the state of locking and blocking on SQL Server
  • The common blocking scenarios must be identified and resolved.
  • The scripts output must be checked constantly,
  • The SQL profilers data must be examined regularly to detect blocking.
Explain the different types of BACKUPs available in SQL Server.
Complete database backup: This type of backup will backup all the information in the database. Used most commonly for disaster recovery and takes the longest time to backup.
Differential databse backup: The database is divided into partitions that have been modified since last complete backup. Most suitable for large databases. The most recent differential backup contains the changes from previous backups.
Transaction log backups: Backups only the changes logged in the transaction log. The transaction log has all changes logged about a database. Once the changes are accommodated on the database, the log is truncated or backed up.
File/File Group backups: used to recover individual files or file groups. Each filegroup can be individually backed up. This helps in recovery only the required file or filegroup for disaster recovery.

What is database isolation in SQL Server?
Isolation in database defines how and when changes made by one transaction can be visible to other transactions. Different isolation levels are:
  • Serializable
  • Repeatable read
  • Read committed
  • Read uncommitted

What is a Schema in SQL Server 2005? Explain how to create a new Schema in a Database?
A schema is used to create database objects. It can be created using CREATE SCHEMA statement. The objects created can be moved between schemas. Multiple database users can share a single default schema.
CREATE SCHEMA sample;
Table creation
Create table sample.sampleinfo
{
id int primary key, 
name varchar(20)
}

Explain how to create a Scrollable Cursor with the SCROLL Option.
Using the SCROLL keyword while declaring a cursor allows fetching of rows in any sequence
Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;
The active set of the cursor is stored can be accessed in any order without the need of opening and closing the cursor. The Scroll cursors can be set for select and function cursors but not insert or update statements.

Explain how to create a Dynamic Cursor with the DYNAMIC Option.
When a cursor is declared as DYNAMIC, the cursor reflects all changes made to the base tables as the cursor is scrolled around.
Declare cursor_name cursor
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
FOR select_statement
The dynamic option does not support ABSOLUTE FETCH.

What are database files and filegroups?

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:

Primary: starting point of a database. It also points to other files in database. Extension: .mdf
Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf
Log files: All log information used to recover database. Extension: .ldf


Describe in brief Databases and SQL Server Databases Architecture.

SQL Server consists of a set of various components which fulfill data storage and data analysis needs for enterprise applications. Database architecture: All the data is stored in databases which is organized into logical components visible to the end users. It’s only the administrator who needs to actually deal with the physical storage aspect of the databases, whereas users only deal with database tables.

Every SQL Server instance has primarily 4 system database i.e. master, model, tempdb and msdb. All other databases are user created databases as per their needs and requirements.

A single SQL Server instance is capable of handling thousands of users working on multiple databases.





PHP interview questions with answers

By karthick , in , , 0 Comments




String Encryption,Decryption software

By karthick , in , , 2 Comments



Maintain scroll position on post back

By karthick , in , , 1 Comment



Page.MaintainScrollPositionOnPostBack = true;
 protected void Page_Load(object sender, EventArgs e)
    {
        Page.MaintainScrollPositionOnPostBack = true;

    }

ASP.NET WITH SQL CONNECTION C#

By karthick , in , , , , 0 Comments


HEADER

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;


CREATE
SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("CREATE TABLE Persons (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); ", con1);
      
       cmd.ExecuteNonQuery();

INSERT

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 (id, name)VALUES ('"+TextBox1.Text+"','"+TextBox2.Text +"') ; ", con1);
      
        cmd.ExecuteNonQuery();

SELECT

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
  SqlCommand cmd = new SqlCommand("select * from login ",con1 ); 
        rd1=cmd.ExecuteReader();
        while (rd1.Read())
        {
          TextBox1.Text= rd1.GetString(1).ToString();
                        }


DELETE

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("DELETE FROM Table_1 WHERE id='"+TextBox2.Text +"'; ", con1);
      
        cmd.ExecuteNonQuery();






UPDATE

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("UPDATE Table_1 SET name='"+TextBox2.Text+"' WHERE id='"+TextBox1.Text+"';  ", con1);
      
        cmd.ExecuteNonQuery();

ASP.NET WITH SQL CONNECTION C#

By karthick , in , , , , 2 Comments


HEADER

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;


CREATE
SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("CREATE TABLE Persons (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); ", con1);
      
       cmd.ExecuteNonQuery();

INSERT

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("INSERT INTO Table_1 (id, name)VALUES ('"+TextBox1.Text+"','"+TextBox2.Text +"') ; ", con1);
      
        cmd.ExecuteNonQuery();

SELECT

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
  SqlCommand cmd = new SqlCommand("select * from login ",con1 ); 
        rd1=cmd.ExecuteReader();
        while (rd1.Read())
        {
          TextBox1.Text= rd1.GetString(1).ToString();
                        }


DELETE

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("DELETE FROM Table_1 WHERE id='"+TextBox2.Text +"'; ", con1);
      
        cmd.ExecuteNonQuery();






UPDATE

SqlConnection con1 = new SqlConnection("Data Source=MCANODE-102\\MCA;Initial Catalog=master;Integrated Security=True");
        SqlDataReader rd1 = null;
        con1.Open();
        SqlCommand cmd = new SqlCommand("UPDATE Table_1 SET name='"+TextBox2.Text+"' WHERE id='"+TextBox1.Text+"';  ", con1);
      
        cmd.ExecuteNonQuery();

Message box for asp.net c#

By karthick , in , 1 Comment




Alert for asp.net c#

Type cstype = this.GetType();

            // Get a ClientScriptManager reference from the Page class.
            ClientScriptManager cs = Page.ClientScript;

            // Check to see if the startup script is already registered.
            if (!cs.IsStartupScriptRegistered(cstype, "PopupScript"))
            {
                String cstext = "alert('karthi');";
                cs.RegisterStartupScript(cstype, "PopupScript", cstext, true);

            }

ASP.NET with SQL Server 2005 DataBase

By karthick , in , 1 Comment

CREATE DATABASE AND TABLE



 

ASP.NET with Database connection
VB.NET



Source code

Imports System
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConnection As System.Data.SqlClient.SqlConnection
        Dim myCommand As System.Data.SqlClient.SqlCommand
        Dim strSQL As String
        myConnection = New SqlConnection("Data Source=USER-PC;Initial Catalog=master;User ID=sa;Password=system") 'master is database name,User is  hostname

        myConnection.Open() 'Open the connection
        strSQL = "SELECT * FROM log where id=" + TextBox1.Text  'log is tablename,id is field
        myCommand = New SqlCommand(strSQL, myConnection)
        Dim re As SqlDataReader = myCommand.ExecuteReader()
        While re.Read
            TextBox2.Text = re.GetString(1).ToString’Read rows
        End While
    End Sub
End Class



Run the website


Subscribe to: Posts (Atom)


Your IP Address is:

Browser: