Category: Database
All about databases
RedUI – Redis Desktop
RedUI is a Cross platform Redis Desktop Manager built with Electron, Angular, Clarity UI and ioredis. Main Features Cross platform Supports List view and Tree view `Monitor` mode Display Redis `Info` JSON formatting for `string` Github https://github.com/rizansari/red-ui Screenshots
Oracle Replication through Oracle Data Guard
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
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
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
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
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
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
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
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
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 + […]
- 1
- 2