Top posts
-
Snapshot Agent
Snapshot Agent- The Snapshot Agent is used with all types of replication. It prepares the schema and the initial bulk copy files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution...
-
SQL FULL OUTER JOIN
SQL FULL OUTER JOIN Keyword The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins. SQL FULL OUTER JOIN Syntax SELECT column_name(s)...
-
SQL DEFAULT Constraint
SQL DEFAULT Constraint The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. SQL DEFAULT Constraint on CREATE TABLE CREATE TABLE Persons ( P_Id int NOT...
-
)Describe in brief working of Replication.
)Describe in brief working of Replication. At first data and object is synchronized between publisher and subscribers.The snapshot is created on the publisher and transmitted to subscribers. All the subsequent changes on the publisher are stored in distribution...
-
Complex Queries in SQL ( Oracle )
Complex Queries in SQL ----------------------------------------- Hai All, These questions are the most frequently asked in interviews. 1) To fetch ALTERNATE records from a table. (EVEN NUMBERED) select * from emp where rowid in (select decode(mod(rownum,2),0,rowid,...
-
PL/SQL Implicit Cursor
PL/SQL Implicit Cursor Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement or DML statement Oracle reserves a private SQL area in memory called cursor. Implicit cursor scope you can get information from cursor...
-
PL/SQL Parameterized Cursor
PL/SQL Parameterized Cursor PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query. PL/SQL Parameterized cursor define only datatype of parameter and not need to define it's length. Default values is assigned to the Cursor...
-
plsql triggers
Trigger Triggering SQL statement : SQL DML (INSERT, UPDATE and DELETE) statement that execute and implicitly called trigger to execute. Trigger Action : When the triggering SQL statement is execute, trigger automatically call and PL/SQL trigger block...
-
LOB types in PLsql
LOB data types used to store large objects such as image, video, graphics, text or audio. Maximum size is up to 4 Gigabytes. Following are the LOB data types in PL/SQL: BFILE BFILE data type is used to store large binary objects into Operating System...
-
SQL Server Interview Questions and Answers
Question: What is NO COUNT used for? Answer: It’s used to check whether the sql statement executed successfully or not. When no count is on than this message will not return and while it off then sql server returns the message. Question: What is SET ANSI...
-
Database Tuning Advisior in sql server
Database Tuning Advisior Database Engine Tuning Advisor analyzes a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database...
-
Handling Variables In PLSQL
Variables In PLSQL Variables must be declared first before the usage. The PL/SQL variables can be a scalar type such as DATE, NUMBER, VARCHAR(2), DATE, BOOLEAN, LONG and CHAR, or a composite type, such array type VARRAY. Only TRUE and FALSE can be assigned...
-
PL/SQL Procedures/Functions
Writing PL/SQL Procedures/Functions PL/SQL functions returns a scalar value and PL/SQL procedures return nothing. Both can take zero or more number of parameters as input or output. The special feature about PL/SQL is that a procedure/function argument...
-
PL/SQL Package example
CREATE OR REPLACE PACKAGE test AS -- package spec TYPE list IS VARRAY(25) of NUMBER(3); PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2); PROCEDURE fire_employee (emp_id INTEGER); PROCEDURE raise_salary (emp_id INTEGER, amount REAL); END test;...
-
PL/SQL Records
What are records? Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data...
-
ORACLE Partitions
Partitions Partition is a data base object, it allows VLDB objects (Very Large Databases such as tables, indexes etc). By splitting large database objects into smaller, more manageable units. You can divide the rows of a single table into different parts...
-
ORACLE Explain Plan
Explain Plan Explain Plan : It is a statement that allows you to have oracle generate execution plan for any sql statement with out actually executing it.You will be able to examine the execution plan by querying the plan table. Plan Table : A Plan table...
-
ORACLE DBA Frequently Used Data Dictionary Queries
Frequently Used Data Dictionary Queries 1.Index Table : Select INDEX_NAME ,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,NUM_ROWS, LAST_ANALYZED,PARTITIONED From All_Indexes; 2. Index Columns : select INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME, COLUMN_POSITION,...
-
SQL Server Exception Handling
SQL Server Exception Handling by TRY…CATCH QL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to...
-
Difference between Checkpoint and LazyWriter
Difference between Checkpoint and LazyWriter C: Flush dirty pages to Disk L: Flush dirty pages to disk. C: Flush only Data pages to disk L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects) C:...
-
Dynamic Management View in SQL SERVER
Dynamic Management View Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine. These values help the administrator to analyze problems...
-
SQL Server backups
Backup's =========== full:: backup database dbname to disk='path' Differential:: backup database dbname to disk='path'with differential log:: backup log dbname to disk='path' mirror:: backup database dbname to disk='path' mirror backup database dbname...
-
AlwaysOn Availability Groups
SQL Server 2012 AlwaysOn One of the better-known features in the release of SQL Server 2012 Enterprise Edition is AlwaysOn. This has been designed to meet the ever-increasing need for ‘High Availability’ (HA). AlwaysOn does not use entirely new technologies...
-
Clusters Server in SQL SERVER
Cluster Service: The Cluster Service controls cluster activities and performs such tasks as 1. Coordinating event notification, 2. Facilitating communication between cluster components, 3. Handling failover operations 4. Managing the configuration. Heartbeat:...
-
Differences between active-active and active-passive cluster
Differences between active-active and active-passive cluster Active-active Setup: Two active Servers are configured as companion servers, each with independent workloads. These companions run on the primary and secondary nodes, respectively, as individual...