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