How to generate awr snapshot manually other than default snapshots in oracle

This document describes how to create or generate AWR snapshot in between default generated snapshots in oracle.

Normally by default AWR snapshot generates every 60 minutes. We need AWR snapshots to create AWR report to analyse when we have performance issues in database.

Below is the procedure to generate snapshot manually at any time interval.

Connect to database as sysdba and find the snapshot history.

[oracle@server1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 9 18:40:51 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

SQL> select snap_id,instance_number,end_interval_time from dba_hist_snapshot;

   SNAP_ID    INSTANCE_NUMBER   END_INTERVAL_TIME
     ----------            ---------------                 ------------------------------
10            1                              09-OCT-18 05.43.50.566 PM
11            1                              09-OCT-18 06.30.58.809 PM

SQL>

Generate  AWR snapshot manually using dbms_workload_repository.

SQL> execute dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL>

SQL> select snap_id,instance_number,end_interval_time from dba_hist_snapshot;

   SNAP_ID           INSTANCE_NUMBER       END_INTERVAL_TIME
    ----------              ---------------                         ------------------------------
12              1                                  09-OCT-18 06.51.22.192 PM
10              1                                  09-OCT-18 05.43.50.566 PM
11              1                                  09-OCT-18 06.30.58.809 PM






Thanks for your patience to view this post.....

How to insert thousands of records in a table using procedure

This document describes how to insert thousands of records in a table using procedure.

Step 1: Create a test table

SQL> show user
USER is "VIJAY"


SQL> create table  test
  2  (SNUMBER number(10),
  3  Name varchar2(10)
  4  )
  5  ;

Table created.

SQL> insert into test values
  2  (100,'Vijay');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
       1

Step 2: Create a procedure using "for" loop to insert one crore values.

[oracle@server1 scripts]$ ls -ltr
total 4
-rwxrwxr-x. 1 oracle dba 103 Oct  9 17:58 insert_test.sql

[oracle@server1 scripts]$ cat insert_test.sql
declare
begin
for r in 1..10000000
loop
insert into test values(101,'Kumar');
end loop;
commit;
end;
/

Step 3: Execute the procedure to insert data

SQL> conn vijay/vijay
Connected.

SQL> @insert_test.sql

PL/SQL procedure successfully completed.

SQL>




Thanks for your patience to view this post.......