Database Experts

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

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

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

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.

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

** 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

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 '                   ...

**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

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

Oracle TO SQL Server DBA Migration

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 .