Posts

Showing posts from May 14, 2012

Oracle 10g:Create New Database Manually.

Image
1.Create New Service Go to Command Prompt. oradim -new -sid blogdb -intpwd blogdb 2.Create Parameter File Sample Pfile *.__db_cache_size=444596224 *.__java_pool_size=4194304 *.__large_pool_size=4194304 *.__shared_pool_size=150994944 *.__streams_pool_size=0 *.compatible='11.1.0' *.control_files='D:\Database\control01.ctl','D:\Database\control02.ctl','D:\Database\control03.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='blogdb' *.db_recovery_file_dest_size=2147483648 *.db_unique_name='blogdb' *.dispatchers='(PROTOCOL=TCP) (SERVICE=blogdbXDB)' *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=203423744 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=612368384 3.Start Database in NoMount mode with pfile  startup nomount pfile=D:\oracle\product\10.2.0\db_1\database\INITblogdb.ora 4. Creat

MIcrosoft SQL Server:Create New Database User for Login.

** Execute this Query ** USE [master] GO   CREATE LOGIN [ LoginName ] WITH PASSWORD=N' Password ', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO   USE [ DatabaseName ] GO   CREATE USER [ DatabaseUserName ] FOR LOGIN [ LoginName ] GO   USE [ DatabaseName ] GO   EXEC sp_addrolemember N'db_datareader', N' DatabaseUserName ' GO

Oracle 10g:Get All Query output values in Comma Separated in One Single row

Replace ColumnName to Column that need out in Comma Separated. Replace TableName with your Table  SELECT SUBSTR (c, 2) concatenated   FROM (SELECT     SYS_CONNECT_BY_PATH ( ColumnName , ',') c, r               FROM (SELECT   ROWNUM ID, ColumnName ,                              RANK () OVER (ORDER BY ROWID DESC) r                         FROM TableName                         WHERE ColumnName =' Value '                     ORDER BY ColumnName )         START WITH ID = 1         CONNECT BY PRIOR ID = ID - 1)  WHERE r = 1;

XType values in Sys.sysobjects

**select *from sys.sysobjects where xtype=? ** Question mark replace by below values.     C = CHECK constraint     D = Default or DEFAULT constraint     F = FOREIGN KEY constraint     L = Log     P = Stored procedure     PK = PRIMARY KEY constraint (type is K)     RF = Replication filter stored procedure     S = System table     TR = Trigger     U = User table     UQ = UNIQUE constraint (type is K)     V = View     X = Extended stored procedure

Kill Session in Microsoft SQL Server.

DECLARE @ID INT DECLARE @MSG CHAR(8) While 1 = 1 BEGIN     Set RowCount 1  SELECT   @id =   spid  FROM   Master..Sysprocesses P,   Master..Sysdatabases D  WHERE   D.Name=' DatabaseName '  AND   D.dbid = P.dbid   --and P.blocked<>0 --if you want to kill blocked session.     IF @@rowcount = 0     break     SELECT @msg = 'KILL ' + convert(char(8) ,@ID)     Print @msg     EXEC( @msg)     --break END GO