oracle database live monitoring 11g

we may use the below view for Db live monitoring 11g onwards.


 select sql_text ,status,username from V$SQL_MONITOR;

script to check database resource utilization history in oracle database

Please use the below view to find the resource history usage details with specific time frame.

select CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE from DBA_HIST_RESOURCE_LIMIT where RESOURCE_NAME like '%processes%' and SNAP_ID='13011';

scrip to check catalog db synced with rman repository?

select DB_NAME,DB_STATUS,RESYNC_TIME,RESYNC_TYPE from  rman.RC_RESYNC where DB_NAME='DB_NAME';

DB_NAME  DB_STAT RESYNC_TIME RESYNC_
-------- ------- --------- -------
DB_NAME      OPEN    30-OCT-14 FULL

steps to perform table-reorganization using DBMS_REDEFINITION on oracle table

1.DETERMINE IF THE TABLE CAN BE REDEFINED ONLINE

BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('schema_name','original_TABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

2.CREATE THE INTERIM TABLE

CREATE TABLE "schema_name"."original_TABLE_INTERIM" AS SELECT * FROM "schema_name"."original_TABLE" WHERE 1=2;

3.PARALLELISM FOR LARGE TABLES

ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

4.START THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
                 uname => 'schema_name',
                 orig_table => 'original_TABLE',
                 int_table => 'original_TABLE_INTERIM',
                 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

5.COPY THE TABLE DEPENDENTS FROM THE ORIGINAL TABLE TO THE INTERIM TABLE

DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('schema_name', 'original_TABLE', 'original_TABLE_INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/


6.check for invalid objects

select count(*),status from dba_objects where owner='schema_name' group by status;

select object_name from DBA_OBJECTS where owner='schema_name' and status='INVALID';

7.DO ONE FINAL SYNCHRONIZE BEFORE FINISHING THE REDEFINITION

BEGIN
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE('schema_name', 'original_TABLE', 'original_TABLE_INTERIM');
END;
/

8.FINISH THE REDEFINITION

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('schema_name', 'original_TABLE', 'original_TABLE_INTERIM');

9.Drop the interim table
drop TABLE "schema_name"."original_TABLE_INTERIM"

how to get the index size without creating the index on table?

There are multiple ways we can calculate the Index size without creating it.
1.just check the table size and based on the number of columns used on the index we may put the approx index size.
2.just take the explain plan for the index create statement ,it will show you the index size.

script to grant read/write access to user from all Db users in oracle database?

Here is the script which I used to pull all the grants and push to ROLE.
then grant to role to user.

select 'grant select ,update,insert,delete on '||owner||'.'||view_name||' to RW_ROLE;' from dba_views where owner in (select username from dba_users where username not in ('SYSTEM','DBSNMP','SYS','OUTLN','OLAPSYS','CTXSYS','EM_MONITOR','SYSMAN') );

select 'grant select ,update,insert,delete on '||owner||'.'||table_name||' to RW_ROLE;' from dba_tables where owner in (select username from dba_users where username not in ('SYSTEM','DBSNMP','SYS','OUTLN','OLAPSYS','CTXSYS','EM_MONITOR','SYSMAN') );

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