steps to generate fake error in oracle database alert log

11g on-wards

alter session set events '942 incident(FAKE_ERROR_GENERATED)';
drop table KILL_NOTEXIST;
alter session set events '942 trace name context off';.

Steps to import same table as different table name in oracle database?

here is the steps:
1.take the expdp of the table  scott.emp

2. impdp scott/scott tables=EMP remap_table=emp:emp_temp3 directory=DATA_PUMP_DIR dumpfile=EMP_table.dmp logfile=impdpEMP3.log

it will create new table as emp_temp3

scrip to detach /attach a sql database from instance?

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DB_NAME'
GO

CREATE DATABASE [test2] ON ( FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.mdf'), ( FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf' ) FOR ATTACH ;

script to create sql database manually?

USE [master]
GO

CREATE DATABASE [test2] ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\test2_1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [test2] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [test2].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [test2] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [test2] SET ANSI_NULLS OFF
GO

ALTER DATABASE [test2] SET ANSI_PADDING OFF
GO

ALTER DATABASE [test2] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [test2] SET ARITHABORT OFF
GO

ALTER DATABASE [test2] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [test2] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [test2] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [test2] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [test2] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [test2] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [test2] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [test2] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [test2] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [test2] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [test2] SET  DISABLE_BROKER
GO

ALTER DATABASE [test2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [test2] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [test2] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [test2] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [test2] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [test2] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [test2] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [test2] SET  READ_WRITE
GO

ALTER DATABASE [test2] SET RECOVERY SIMPLE
GO

ALTER DATABASE [test2] SET  MULTI_USER
GO

ALTER DATABASE [test2] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [test2] SET DB_CHAINING OFF
GO



GO

scrip to restore sql database from full backup?

RESTORE DATABASE [test] FROM  DISK = N'C:bakup\test.bak' WITH  FILE = 2,  NOUNLOAD,  STATS = 10

script to take full database backup on sql server?

BACKUP DATABASE [test] TO  DISK = N'C:\Backup\test.bak' WITH NOFORMAT, NOINIT,  NAME = N'bala-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

scrip to check stale stats in oracle database?

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

steps to run segment advisor for table using OEM?

1.GO TO OEM  home page -->administration--.storage-->segment advisor
2.provide the table name or schema name to search and submit the job
3.once the job completed ,view the result and recommendations

steps to run awr report for a specific sql_id?

we can generate awr report for a specific  sql_ID

use the below script.

1.@?/rdbms/admin/awrsqrpt.sql;

scrip to check the most expensive sql on the cursor cache?

SQL> @?/rdbms/admin/sqltrpt.sql;

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID           ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------

steps to manually run the sql_tunning advisor for sql_id in oracle database?

1. Create SQL Tuning Advisor task

DECLARE
  my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'd6v2m5q1t3hpp',scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'my_tune',description => '7a6b4442j5pcz');
end;
/

2. Run Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tune');

3. View results
SET LONG 10000
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tune') from dual;


4.drop the sql_tunning task

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_tune');

5.to list the tunning task job details
dba_advisor_tasks,DBA_ADVISOR_FINDINGS

steps to compare the database performance between two time stamps in oracle database?

1.generate the AWR manually and check the performance of the SQL's and TOP sql's
2.use OEM tool ,go to AWR  & compare awr for two time frames by snapshot  .

steps to check sql is hard/soft parse ,#execution,rows_processed in oracle database?

1.check from oem HISTORY FOR CURSOR AND HISOTRIC DATA
2.USE v$SQL,v$SQLAREA,DBA_HIST_SQLSTAT VIEWS TO GET THE INFORMATION

steps to check when the SQL executed on the oracle database?

1.go to OEM page ->performance->sql-->search sql from AWR if its history
2.select sql_id from dba_hist_sqlstat  where sql_text  like 'select * from emp%'; if its history
3. select sql_id from V$sql where sql_text like 'select ename%'; if its in cursor

monitoring tools for postgreSql database

	1.OPM 2.PGWATCH

steps to generate explain pla for sql in oracle DB?

EXPLAIN PLAN command

select plan_table_output   from table(dbms_xplan.display('plan_table',null,'basic'));

V$SQL_PLAN --last slq staement on cursor

select plan_table_output  from table(dbms_xplan.display_cursor(null,null,'basic'));

Automatic Workload Repository (AWR) 

1.note down the specific time
2.generate the awr for the specific  time
2.genarete the awr and pick the sql_id and genrate the explain plan
or
use dba_hist_sqlstat and get the sql_id and genrate the explain plan

SQL Tuning Set (STS)

SQL Plan Baseline (SPM)

--get the sql_handle from the query and generate the plan

 select SQL_HANDLE, PLAN_NAME, ACCEPTED   from dba_sql_plan_baselines
  where sql_text like 'select * from empty%';


select t.* from
table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed0999',format => 'basic')) ;

steps to flush a particular sql statements from the database

get the SQL_ID for the SQl statement

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '81d%';

ADDRESS HASH_VALUE
---------------- ----------
<address> <hash value>

SQL> exec DBMS_SHARED_POOL.PURGE ('address, hash value', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '81d%';
no rows selected.