Category: Database

All about databases

Oracle Replication through Oracle Data Guard

Author: | Categories: Database No Comments
This is a step by step guide to create Physical Standby database in Oracle using Oracle Data Guard. Force Logging on Primary Instance Set Force Logging on Primary database instance. alter database force logging; Verify by running below query. SELECT force_logging FROM v$database; Add standby redo log files ALTER DATABASE ADD STANDBY LOGFILE 'C:\ORACLE\ORADATA\ORCL\SREDO01.log' SIZE […]

Import and Export MySQL Database

Author: | Categories: Database, Wordpress One Comment
There are many ways you can Export and then Import MySQL on other server like phpMyAdmin and MySQL WorkBench. But most efficient way (though not much user friendly) is command line option. This is helpful when you don’t have other option to transfer database but you have SSH access to the server and you want […]

Clone SQL Server database

Author: | Categories: Database One Comment
Option 1 Detach Original Database, copy its MDF and LDF files and create a new Database from copied files. USE master; GO EXEC sp_detach_db @dbname = N'OriginalDB'; GO copy "c:\\OriginalDB.mdf" "c:\\NewDB.mdf" copy "c:\\OriginalDB_log.ldf" "c:\\NewDB_log.ldf" USE master; GO CREATE DATABASE OriginalDB ON (FILENAME = 'C:\\OriginalDB.mdf'), (FILENAME = 'C:\\OriginalDB_log.ldf') FOR ATTACH; GO CREATE DATABASE NewDB ON (FILENAME […]

SQL Server format date

Author: | Categories: Database No Comments
Format Date in SQL Server by using a user defined functions. /* author: Rizwan Ansari */ create function dbo.fnFormatDate (@datetime datetime, @format varchar(35)) returns varchar(35) as begin declare @date varchar(35) set @date = @format if (charindex ('yyyy',@date) > 0) set @date = replace(@date, 'yyyy', datename(YY, @datetime)) else if (charindex ('yy',@date) > 0) set @date = […]

Oracle EM Configuration

Author: | Categories: Database No Comments
Start Stop DBConsole Service for Oracle EM emctl status dbconsole emctl start dbconsole emctl stop dbconsole OC4J Configuration issue If you get following error in starting dbconsole OC4J_DBConsole__ not found then drop and re-create OC4J configuration first drop existing configuration and then create new. To drop existing configuration emca -deconfig dbcontrol db -repos drop To […]

Oracle connect remotely

Author: | Categories: Database No Comments
If Oracle is not connecting from remote machine then in listener.ora file add one more listener apart from localhost (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOST NAME or NIC IP>)(PORT=1521))) If it is not connected after this then check firewall.

SQLPlus Snippets

Author: | Categories: Database No Comments
SET LINESIZE <linesize> the length of the line. In most cases the maximum value for linesize is 32767 SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached. SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is […]

Import Oracle dump in different tablespace

Author: | Categories: Database No Comments
Follow below steps to Import Oracle dump into different Tablespace With your .DMP file, create a SQL file containing the structure (Tables): imp <username>/<password>@<sid> file=<filename.dmp> indexfile=index.sql full=y Open the indexfile (index.sql) in a text editor and issue the following find and replace statements IN ORDER (ignore the single quotes): Find: ‘REM<space>’ Replace: <nothing> Find: ‘”<source_tablespace>”‘ […]

Using comma separated value CSV parameter strings in SQL IN clauses

Author: | Categories: Database No Comments
Below function when called with a CSV list returns a temporary table which can be used in “IN” of WHERE clause /****** Object: User Defined Function [dbo].[CSVTable] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[CSVTable] (@InStr VARCHAR(MAX)) RETURNS @TempTab TABLE (id varchar(10) not null) AS BEGIN SET @InStr = REPLACE(@InStr + […]

Store historical records in a history table in SQL Server

Author: | Categories: Database No Comments
You can maintain historical changes made in a table by creating another table with the same schema which holds historical data. SomeTable is the base table containing current data rows SomeTable-History has exact same schema with 2 additional columns trans_type varchar(10) date_time datetime and this table will hold historical data Below triggers created on SomeTable […]