Automatically generates AWR1 (SQL) and awr1sql

Automatically generates AWR1 (SQL) and awr1sql

Currently, two reliable automatic AWR generation methods have been found on the Internet.

Method 1:

Reference SQL link: http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports. SQL

-- ------------------------------------------------------------------------------------- File Name    : http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql-- Author       : DR Timothy S Hall-- Description  : Generates AWR reports for all snapsots between the specified start and end point.-- Requirements : Access to the v$ views, UTL_FILE and DBMS_WORKLOAD_REPOSITORY packages.-- Call Syntax  : Create the directory with the appropriate path.--                Adjust the start and end snapshots as required.--                @generate_multiple_awr_reports.sql-- Last Modified: 02/08/2007-- -----------------------------------------------------------------------------------CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/';DECLARE  -- Adjust before use.  l_snap_start       NUMBER := 1;  l_snap_end         NUMBER := 10;  l_dir              VARCHAR2(50) := 'AWR_REPORTS_DIR';    l_last_snap        NUMBER := NULL;  l_dbid             v$database.dbid%TYPE;  l_instance_number  v$instance.instance_number%TYPE;  l_file             UTL_FILE.file_type;  l_file_name        VARCHAR(50);BEGIN  SELECT dbid  INTO   l_dbid  FROM   v$database;  SELECT instance_number  INTO   l_instance_number  FROM   v$instance;      FOR cur_snap IN (SELECT snap_id                   FROM   dba_hist_snapshot                   WHERE  instance_number = l_instance_number                   AND    snap_id BETWEEN l_snap_start AND l_snap_end                   ORDER BY snap_id)  LOOP    IF l_last_snap IS NOT NULL THEN      l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.htm', 'w', 32767);            FOR cur_rep IN (SELECT output                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))      LOOP        UTL_FILE.put_line(l_file, cur_rep.output);      END LOOP;      UTL_FILE.fclose(l_file);    END IF;    l_last_snap := cur_snap.snap_id;  END LOOP;  EXCEPTION  WHEN OTHERS THEN    IF UTL_FILE.is_open(l_file) THEN      UTL_FILE.fclose(l_file);    END IF;    RAISE; END;/

The specific method is:

1. First save the preceding SQL statement to the DB host, generate_multiple_awr_reports. SQL

2. view the AWR snapshot generated by the system:

SELECT snap_id, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot;

3. Manually change the value of snap_id In the result obtained in step 2 to generate_multiple_awr_reports. SQL and replace the numbers 1 and 10:

Rochelle snap_start NUMBER: = 1;

Rochelle snap_end NUMBER: = 10;

4. Call the SQL file in sqlplus

SQL> @ generate_multiple_awr_reports. SQL

5. Check whether html files are generated in the/tmp directory.

 

Conclusion: In fact, this method is only suitable for replacing the sqlplus interactive command line. It does not work very effectively. Currently, tools such as TOAD directly provide AWR viewing tools.

 

SQL database columns automatically generate numbers?

The id is set to the int identity () field of the auto-increment field. The field is automatically increased from 1, and 1 is automatically added for every multiple records.
Row 1 and row 2 continue continuously. When deleting a record, for example, if the maximum value is 4, you delete the record whose id is 4, and the id of the added record is 5, that is, he guarantees uniqueness, but not continuity.
1. Set during creation:
Create table [dbo]. [video] (
[Id] [int] IDENTITY (1, 1) not null,
Field 2,
Field 3,
Field n
) ON [PRIMARY]
GO
2. Modify the existing table:
-- Copy the video data and structure of the table to video1, and set the auto-increment field for the id field of video1.
Select id = identity (int,) -- specifies the column to be changed to the auto-increment Field
, Field 1, Field 2, field n
Into video1
From video
Go
Drop table video -- delete a table video
Go
Exec sp_rename 'video1', 'video' -- change the name of the video1 table to video.
Go

Sequence automatically generated by SQL statements

Lpad (seq. nextval, 3, '0 ')