Monday, May 14, 2012

Oracle 10g:Create New Database Manually.



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. Create Database 

Sample Script

create database blogdb
  logfile   group 1 ('D:\Database\redo1.log') size 50M,
            group 2 ('D:\Database\redo2.log') size 50M,
            group 3 ('D:\Database\redo3.log') size 50M
  character set          WE8ISO8859P1
  national character set utf8
  datafile 'D:\Database\system.dbf'
            size 100M
            autoextend on
            next 50M maxsize unlimited
            extent management local
  sysaux datafile 'D:\Database\sysaux.dbf'
            size 100M
            autoextend on
            next 50M
            maxsize unlimited
  undo tablespace undo
            datafile 'D:\Database\undo.dbf'
            size 100M
  default temporary tablespace temp
            tempfile 'D:\Database\temp.dbf'
            size 100M;

Check Message

Database created.

SQL>
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

5.Execute Database Scripts

@?\RDBMS\ADMIN\catalog.sql

@?\RDBMS\ADMIN\cataproc.sql

@?\RDBMS\ADMIN\utlrp.sql

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