Posts

Showing posts from May, 2012

Oracle 10g:Put Database in Archive Log Mode.

Image
1.Set the parameters. SQL> alter system set log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile; SQL> alter system set log_archive_dest_1='location=C:\archive' SCOPE=spfile; SQL>alter system set log_archive_start=true scope=spfile; 2.Restart the database. SQL>SHUT IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> conn / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started. SQL> conn / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             171969412 bytes Database Buffers          432013312 bytes Redo Buffers                7135232 bytes Database mounted. 3.Start Archive log Mode. SQL> alter database archivelog; Database altered. 4.Open Database. SQL> alter database open; D

Microsoft SQL Server:Add Table/Columns Description

Image
Use sys.sp_addextendedproperty to add Description to the tables and columns. E.g. For Table Description, EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N' Put your Table Comments Here ', @level0type = N'SCHEMA', @level0name = ' dbo ', @level1type = N'TABLE',  @level1name = ' TableName '; GO **dbo is database schema.you may your own schema where objects reside. For Columns Description, EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N' Put your Comment here ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N' TableName ', @level2type=N'COLUMN',@level2name=N' ColumnName1 ' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N' Put your Comment here ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1n

Oracle 10g:Change Tablespace to all objects

Image
1.Use this script to change Index's Tablespace. SQL>spool C:\Change_Index_tablespace.sql SQL>select 'ALTER INDEX '||index_name||' REBUILD TABLESPACE BHAVESH_INDX    ;' from user_indexes; / ALTER INDEX SYS_C007391 REBUILD TABLESPACE BHAVESH_INDX    ; ALTER INDEX TLMYIELDFORECAS_IDX$$_28180009 REBUILD TABLESPACE BHAVESH_INDX; ALTER INDEX TLMSEGMENTFLIGHTMASTER_PK REBUILD TABLESPACE BHAVESH_INDX; ----- ------- ---------- SQL>Spool off SQL>@C:\Change_Index_tablespace.sql 2.Use this script to change table's tablespace SQL>spool C:\Change_Index_tablespace.sql SQL>select 'ALTER TABLE '||table_name||' MOVE TABLESPACE tablespace_name;'  from user_tables; / ALTER TABLE MLOG$_MVDCPRLOADYIELD MOVE TABLESPACE tablespace_name; ALTER TABLE MLOG$_MVFCSTFLIGHTMAPPINGD MOVE TABLESPACE tablespace_name; ALTER TABLE MLOG$_MVFLIGHTSCHEDULE MOVE TABLESPACE tablespace_name; ALTER TABLE MLOG$_MVIEW_REFRESH_LOG MOVE TABLE

Oracle 10gR2:Reconfigure Data Control by EMCA

Image
1.Set oracle parameter Set oracle_sid=blogdb Set Oracle_Home=F:\oracle\product\10.2.0\db_1 2.De configure and delete old entries. emca -deconfig all db -repos drop 3.Configure Data Control. emca -config all db -repos create Output May 22, 2012 12:07:44 PM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at F:\oracle\product\10.2.0\db_1\cfgtoollog s\emca\blogdb\emca_2012-05-22_12-06-05-PM.log. May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig createRepository INFO: Creating the EM repository (this may take a while) ... May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig invoke SEVERE: Error creating the repository May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMReposConfig invoke INFO: Refer to the log file at F:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\bl ogdb\emca_repos_create_<date>.log for more details. May 22, 2012 12:07:47 PM oracle.sysman.emcp.EMConfig perform SEVERE: Error creating the repository

ORACLE-MS SQL DBA: Oracle 10g:Create New Database Manually.

Image
ORACLE-MS SQL DBA: Oracle 10g:Create New Database Manually.

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

Blog for Oracle to MS SQL DBA Migration Methods

Image
Oracle TO SQL Server DBA Migration

SQL Server Connection from Oracle SQL Developer

Image
Connection from SQL Developer to MS SQL. 1.Download jtds-1.2.5-dist 2.Extract Files 3.Go to SQL Developer's TOOL Menu-->Preferences-->Search like Third Party JDBC 4.Click Add Entry -->Add jtds-1.2.5.jar File. 5.Search file ntlmauth.dll into @@jtds-1.2.5-dist\x86\SSO\ and  put into @@\Java\jre1.6.0_12\bin 6.Restart SQL Developer. 7.Go to New Connection and Tab of SQL Server. 8.Give username,password,hostname(instance name),TCP/IP Port. Eg. 10.Test and Check Status as Success .