Printable View
Hi,
We are on Oracle 8.0.5.
I am looking for some script which shows DML,DDL activities in the database within all tables/objects in a specific time frame.
Example: Betw 2:00 PM to 3:00 PM, which are the tables in which DML transactions have been made. Also if possible, in terms of number of blocks/extents increased.
Thanks,
Hmm, since you are on 8.0.5, you can't use logminer.
You could set sql_trace on for the instance during that time frame. That might give you the DML you are looking for.
cant u use auditing for this?
Jeff,
sql_trace is set to true in init.ora. Now, how can I check the DML out of it?
There are thousands of tables and I don't want to increase any other overhead due to Auditing.
1) Enable Auditing - heavy overhead
2) Enable SQL_trace - lots and lots of trace files.
3) dump the redlog file /archive log file for the period.-- need to know internal structures.
see steps 2 and 3 of this link: http://otn.oracle.com/docs/products/...4_str.htm#8760Quote:
Originally posted by samdba
Jeff,
sql_trace is set to true in init.ora. Now, how can I check the DML out of it?
dump the redlog file /archive log file for the period.-- need to know internal structures.
- Infact, my original question is because, I want to know why lot of 100M Arch log files are getting generated. What makes these logs generated every 3-4 minutes inspite of low number of users and transactions.
Then u should firstr run utlbstat.estat to find out the
amount of redo generated,rollback generated , checkpoints done.
gpsingh,
I have already done that.
Is there any system table which stores block level information showing blocks inserted, updated, deleted for a particular table/index or datafile?
Not in 8.0.x.
But u can do one thing, run catio.sql present in ORACLE_HOME/rdbms/admin, go thru it.
It migh help u in some direction.
If you are on 8i, how can you use logminer to find out those information?
On 8.0.5 also, you can use logminer.
That's Great!!!Quote:
Originally posted by svk
On 8.0.5 also, you can use logminer.
How?????
Plz. tell me.
Check if you have the dbms_logmnr_d package installed.
If not, run the dbmslogmnrd.sql to create it and then follow the normal process.
I could not locate either package or .sql on my server. Can I download it from anywhere?Quote:
Originally posted by svk
Check if you have the dbms_logmnr_d package installed.
If not, run the dbmslogmnrd.sql to create it and then follow the normal process.
Rem
Rem $Header: dbmslogmnrd.sql 27-aug-98.13:53:23 rfrank Exp $
Rem $Header: dbmslogmnrd.sql 30-sep-98.09:39:07 gkulkarn Exp $
Rem
Rem dbmslogmnrd.sql
Rem
Rem Copyright (c) Oracle Corporation 1998. All Rights Reserved.
Rem
Rem NAME
Rem dbmslogmnrd.sql -
Rem
Rem DESCRIPTION
Rem This package contains the LogMnr procedure to create the
Rem LogMnr dictionary file.
Rem
Rem INPUTS
Rem Dictionary File Name - name of the file
Rem File Location - path to file directory
Rem
Rem
Rem NOTES
Rem
Rem MODIFIED (MM/DD/YY)
Rem gkulkarn 09/30/98 - Upcase the create dictionary table
Rem rfrank 08/27/98 -
Rem doshaugh 06/17/98 - . Improve exception handling for invalid file nam
Rem mcusson 05/12/98 - Add grant execute_catalog_role.
Rem doshaugh 04/14/98 - Created
Rem
Rem
Rem
Rem PUBLIC PROCEDURES
Rem
Rem BUILD (FileName, FileLocation)
Rem
Rem
Rem PRIVATE FUNCTIONS
Rem
Rem WRITE_COMMENTS (DictionaryFileHandle)
Rem
Rem GET_DICT_DATA (DictionaryFileHandle)
Rem
Rem GET_COLUMN_DESCRIPTION (in_Table_Name)
Rem
Rem WRITE_CREATE_TABLE (in_Table_Name, in_Column_Count)
Rem
Rem WRITE_INSERT_INTO (in_Table_Name, in_Column_Count, in_Where_Clause)
Rem
Rem RECORD_TABLE (in_Table_Name, in_Where_Clause)
Rem
Rem
--
CREATE or REPLACE PACKAGE dbms_logmnr_d AS
--
-- PACKAGE NAME
-- dbms_logmnr_d
--
-- DESCRIPTION
-- This package contains the LogMnr procedure to create the
-- LogMnr dictionary file. The procedure queries the dictionary
-- tables of the current database and creates a text based file
-- containing their contenets. Each table is represented by
-- "psuedo" SQL statements. A description of the columns in a
-- table is created by a "CREATE_TABLE" line (one statement for
-- table). It contains the name, datatype and length for each
-- column. A "INSERT_INTO" statment is created for each row in a
-- selected table. It contains the values for each row. The file
-- is created in preparation of future analysis of databases
-- log files using the LogMnr tool.
--
-- INPUTS
-- dictionary_fileName - name of the dictionary file
-- dictionary_ocation - path to file directory
--
-- EXAMPLE
-- Creating a dictionary file as:
-- /oracle/database/l_dictionary.ora
--
-- SVRMGR> execute dbms_logmnr_d.build('l_dictionary.ora',
-- SVRMGR> '/oracle/database/');
--
-- NOTES
-- The dictionary file should be created after all dictionary
-- changes to a database and prior to the creation of any log
-- files that are to be analyzed.
--
--
PROCEDURE build
(dictionary_filename IN VARCHAR2,
dictionary_location IN VARCHAR2);
--
--
END dbms_logmnr_d; -- End Definition of package
/
--
--
CREATE or REPLACE PACKAGE BODY dbms_logmnr_d
AS
--
PROCEDURE build
(dictionary_filename IN VARCHAR2,
dictionary_location IN VARCHAR2)
IS
--
--
-- Global Variables
--
db_header_result NUMBER := 0;
record_table_result NUMBER := 0;
comment_result NUMBER := 0;
dict_filehandle UTL_FILE.FILE_TYPE;
row_count NUMBER := 0;
rcount NUMBER ;
dest_location VARCHAR2(513);
--
--
-- Global column description record and array
-- The column description record contains the name, datatype, and
-- lenght for a column in a table. The array contains a record for
-- each column in the current table being recorded. In addition the
-- record for each column contains several value fields, one field
-- for each suppored datatype. The filed is used to store the values
-- of a column while recording it in the dictionary file.
--
TYPE col_description IS RECORD (
col_name VARCHAR2(30),
col_datatype VARCHAR2(30),
col_datalength NUMBER(22),
col_value_vr2 VARCHAR2(2000),
col_value_num NUMBER(22),
col_value_lng LONG,
col_value_raw RAW(2000),
col_value_mls MLSLABEL,
col_value_dat DATE
);
TYPE col_desc_array IS VARRAY(513) OF col_description;
--
col_test_desc col_description;
--
col_test_array col_desc_array := col_desc_array(); -- initialize empty varray
--
--
vr2_replacement VARCHAR2(4000);
--
--
--
-- **************************************************************************
--
-- WRITE_COMMENTS Function
-- This function will write a set of comments into the dictionary file
-- explaining the file format and steps required to convert the dictionary
-- file into a functional PL/SQL script.
--
--
FUNCTION write_comments (
DictionaryFileHandle IN UTL_FILE.FILE_TYPE )
RETURN NUMBER IS
--
--
--
--
BEGIN
--
UTL_FILE.PUTF(dict_filehandle, '\n'
|| '-- *************** LOGMNR DICTIONARY FILE *************** '|| '\n'
|| '-- ' || '\n'
|| '-- The LogMnr Dictionary file contains the dictionary data from the ' || '\n'
|| '-- target database. The dictionary data is collected from the dictionary' || '\n'
|| '-- tables defined in the target database. The LogMnr_Dictionary package' || '\n'
|| '-- build script queries the dictionary tables and reconstructs the table' || '\n'
|| '-- contents as a set of "SQL like" commands n the dictionary file.' || '\n'
);
UTL_FILE.PUTF(dict_filehandle, '\n'
|| '-- The dictionary file contains commands to create a table, insert into a' || '\n'
|| '-- table and create an index to a table. To protect against an unintentional' || '\n'
|| '-- execution of the dictionary file, these commands are named ' || '\n'
|| '-- ' || '\n'
|| '-- CREATE_TABLE - create a table (notice the underbar)' || '\n'
|| '-- CREATE_INDEX - create an index for a table (notice the underbar)' || '\n'
|| '-- INSERT_INTO - insert a row into table (again notice the underbar)' || '\n'
);
UTL_FILE.PUTF(dict_filehandle, '\n'
|| '-- The command syntax is identical to the SQL command except for the' || '\n'
|| '-- underbar replacing the space. ' || '\n'
|| '-- ' || '\n'
);
UTL_FILE.PUTF(dict_filehandle, '\n'
|| '-- The dictionary file can be converted to an executable SQL by globally' || '\n'
|| '-- replacing all occurences of the following text strings : ' || '\n'
|| '-- ' || '\n'
|| '-- CREATE_TABLE => CREATE TABLE' || '\n'
|| '-- CREATE_INDEX => CREATE INDEX' || '\n'
|| '-- INSERT_INTO => INSERT INTO' || '\n'
|| '-- ,, => ,NULL,'
|| '-- (, => (NULL,'
|| '-- ,) => ,NULL)'
|| '-- ' || '\n'
);
UTL_FILE.PUTF(dict_filehandle, '\n'
|| '-- For example, CREATE_TABLE may be easily globally replaced through the' || '\n'
|| '-- following SED request at a system command prompt' || '\n'
|| '-- ' || '\n'
|| '-- unix_box1>> sed -e/CREATE_TABLE/CREATE TABLE/ dictionary.ora > dictionary.sql' || '\n'
|| '-- ' || '\n'
|| '-- ' || '\n'
|| '-- ' || '\n'
);
--
--
UTL_FILE.NEW_LINE(dict_filehandle,4);
--
--
UTL_FILE.FFLUSH(dict_filehandle);
--
--
--
RETURN 0;
--
--
END;
--
--
--
-- **************************************************************************
--
-- GET_DICT_DATA Function
-- This function issues a series of selects to the database to gather
-- data about the database for later validation with the log files. It
-- creates a DICTIONARY_TABLE to represent the table (by writing a
-- CREATE_TABLE line to the file) and a single row for the values ( by
-- writing a single INSERT_VALUE for the dictionary table)
--
--
FUNCTION get_dict_data (
DictionaryFileHandle IN UTL_FILE.FILE_TYPE )
RETURN NUMBER IS
--
-- database
db_id NUMBER;
db_name VARCHAR2(9) := NULL;
db_created VARCHAR2(30);
db_resetlogs_change# NUMBER;
db_resetlogs_time VARCHAR2(30);
db_version_time VARCHAR2(30);
db_redo_type_id VARCHAR2(8);
db_redo_release VARCHAR2(60);
db_character_set VARCHAR2(30);
db_version VARCHAR2(64) := NULL;
db_status VARCHAR2(64) := NULL;
db_dict_created VARCHAR2(30);
maxobj# NUMBER;
--
BEGIN
--
SELECT value
INTO db_character_set
FROM sys.nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET'; /* get NLS Character Set */
--
SELECT type_id, release
INTO db_redo_type_id, db_redo_release
FROM sys.v$compatibility
WHERE type_id = 'REDODATA'; /* get Redo data compatibility */
--
SELECT dbid, name,
to_char(created,'MM/DD/YYYY HH24:MI:SS'),
resetlogs_change#,
to_char(resetlogs_time,'MM/DD/YYYY HH24:MI:SS'),
to_char(version_time,'MM/DD/YYYY HH24:MI:SS')
INTO db_id, db_name, db_created , db_resetlogs_change#, db_resetlogs_time , db_version_time
FROM sys.v$database;
--
SELECT version, status
INTO db_version, db_status
FROM sys.product_component_version
WHERE product like 'PL/SQL%'; /* get database version */
--
SELECT count (*) into rcount FROM sys.all_objects;
--
SELECT MAX(obj#) INTO maxobj# FROM sys.obj$;
--
SELECT to_char(sysdate,'MM/DD/YYYY HH24:MI:SS') INTO db_dict_created FROM DUAL;
--
--
UTL_FILE.PUT_LINE(dict_filehandle,
'CREATE_TABLE DICTIONARY_TABLE ('
|| ' DB_NAME VARCHAR2(9),'
|| ' DB_ID NUMBER(20),'
|| ' DB_CREATED VARCHAR2(20),'
|| ' DB_DICT_CREATED VARCHAR2(20),'
|| ' DB_DICT_SCN NUMBER(22),'
|| ' DB_THREAD_MAP NUMBER(22),'
|| ' DB_RESETLOGS_CHANGE# NUMBER(22),'
|| ' DB_RESETLOGS_TIME VARCHAR2(20),'
|| ' DB_VERSION_TIME VARCHAR2(20),'
|| ' DB_REDO_TYPE_ID VARCHAR2(8),'
|| ' DB_REDO_RELEASE VARCHAR2(60),'
|| ' DB_CHARACTER_SET VARCHAR2(30),'
|| ' DB_VERSION VARCHAR2(64),'
|| ' DB_STATUS VARCHAR2(64),'
|| ' DB_DICT_MAXOBJECTS NUMBER(22),'
|| ' DB_DICT_OBJECTCOUNT NUMBER(22));'
);
UTL_FILE.NEW_LINE(dict_filehandle,1);
--
--
UTL_FILE.PUT_LINE(dict_filehandle,
'INSERT_INTO DICTIONARY_TABLE VALUES ('
|| '''' || db_name || '''' || ','
|| db_id || ','
|| '''' || db_created || '''' || ','
|| '''' || db_dict_created || '''' || ','
|| NULL || ','
|| NULL || ','
|| db_resetlogs_change# || ','
|| '''' || db_resetlogs_time || '''' || ','
|| '''' || db_version_time || '''' || ','
|| '''' || db_redo_type_id || '''' || ','
|| '''' || db_redo_release || '''' || ','
|| '''' || db_character_set || '''' || ','
|| '''' || db_version || '''' || ','
|| '''' || db_status || '''' || ','
|| maxobj# || ','
|| rcount || ');'
);
--
--
UTL_FILE.NEW_LINE(dict_filehandle,4);
--
RETURN db_id;
--
END;
--
--
--
--
--
-- **************************************************************************
--
-- GET_COLUMN_DESCRIPTION Function
-- This function will store the name, datatype and length of
-- every column in a table in the column descriptor array. The
-- column array is ordered by column number.
--
--
FUNCTION get_column_description (
in_Table_Name IN VARCHAR2 )
RETURN NUMBER IS
--
--
col_count NUMBER := 1;
i NUMBER := 1;
--
CURSOR obj_cols is
SELECT col.name, col.length,
decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(col.scale, null,
decode(col.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
69, 'ROWID',
96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
105, 'MLSLABEL',
106, 'MLSLABEL',
111, o.name,
112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, o.name,
122, o.name,
123, o.name,
'UNDEFINED')
FROM sys.col$ col, sys.obj$ o
WHERE col.obj# = o.obj# and o.name = in_Table_Name
ORDER BY col.col#;
--
--
--
BEGIN
--
OPEN obj_cols;
--
--
<< objcol_loop >>
LOOP
--
col_test_array.EXTEND;
--
FETCH obj_cols
INTO col_test_array(col_count).col_name,
col_test_array(col_count).col_datalength,
col_test_array(col_count).col_datatype;
EXIT objcol_loop WHEN obj_cols%NOTFOUND;
col_count := col_count + 1;
EXIT WHEN col_count = 513;
--
END LOOP objcol_loop;
--
col_count := col_count-1;
--
--
--
CLOSE obj_cols;
--
RETURN col_count;
--
END;
--
--
-- **************************************************************************
--
-- WRITE_CREATE_TABLE Function
-- This function will write an CREATE_TABLE line to the dictionary
-- file. It uses the current contents of the column descriptor array
-- to format the CREATE_TABLE line.
--
--
FUNCTION write_create_table (
in_Table_Name IN VARCHAR2,
in_Column_Count IN NUMBER )
RETURN NUMBER IS
--
i NUMBER := 1;
--
--
BEGIN
--
IF in_Column_Count = 0 THEN
-- UTL_FILE.PUT(dict_filehandle,'-- Table ' || in_Table_Name || ' no columns defined in the database');
RETURN 0;
END IF;
--
UTL_FILE.PUT(dict_filehandle,
'CREATE_TABLE '
|| in_Table_Name
|| '_TABLE ('
);
--
WHILE i < in_Column_Count LOOP
UTL_FILE.PUT(dict_filehandle,
col_test_array(i).col_name
|| ' '
|| col_test_array(i).col_datatype
);
--
IF col_test_array(i).col_datatype != 'DATE'
AND col_test_array(i).col_datatype != 'LONG' THEN
UTL_FILE.PUT(dict_filehandle, '('
|| col_test_array(i).col_datalength
|| '), '
);
ELSE UTL_FILE.PUT(dict_filehandle, ', ');
END IF;
i := i +1;
END LOOP;
--
-- Write the last value and terminate line
--
UTL_FILE.PUT(dict_filehandle,
col_test_array(i).col_name
|| ' '
|| col_test_array(i).col_datatype);
--
IF col_test_array(i).col_datatype != 'DATE'
AND col_test_array(i).col_datatype != 'LONG' THEN
UTL_FILE.PUT_LINE(dict_filehandle, '('
|| col_test_array(i).col_datalength
|| ') );'
);
ELSE UTL_FILE.PUT_LINE(dict_filehandle, ' ); ');
END IF;
--
--
RETURN i;
--
END;
--
--
-- **************************************************************************
--
-- WRITE_INSERT_INTO Function
-- This function will write an INSERT_INTO line to the dictionary
-- file. The SQL select is created from the current contents of the name
-- fields in the the column descriptor array. The correct datatype for
-- a column is determined from the current datatype value in the column
-- record. The correct value field is then used for fetching the contents
-- of a row. A INSERT_INTO line is formatted for each row and written
-- to the dictionary file. A where clause may be specified to allow
-- filtering of rows in a table.
--
--
FUNCTION write_insert_into (
in_Table_Name IN VARCHAR2,
in_Column_Count IN NUMBER,
in_Where_Clause IN VARCHAR2 )
RETURN NUMBER IS
--
i NUMBER := 1;
row_count NUMBER := 0;
d number;
colout_c number;
colout_str VARCHAR2(500);
column_list VARCHAR2(500);
in_column_list VARCHAR2(500) := 'NAME';
--
col_varchar_value VARCHAR(30) := 'TEST';
colname VARCHAR2(30);
--
--
--
BEGIN
--
--
-- Create the column list for the select
i := 1;
--
--
--
WHILE i < in_Column_Count LOOP
IF col_test_array(i).col_datatype = 'DATE' THEN
column_list := column_list ||
'to_char(' || col_test_array(i).col_name || ',' || '''MM/DD/YYYY HH24:MI:SS''' || ')' || ', ';
ELSE
column_list := column_list || col_test_array(i).col_name || ', ';
END IF;
-- UTL_FILE.PUT_LINE(dict_filehandle, column_list );
i := i +1;
END LOOP;
--
-- copy the last one
IF col_test_array(i).col_datatype = 'DATE' THEN
column_list := column_list || 'to_char(' || col_test_array(i).col_name ||
',' || '''dd mm yy hh24 mi ss''' || ')' || ' ';
ELSE
column_list := column_list || col_test_array(i).col_name || ' ';
END IF;
-- UTL_FILE.PUT_LINE(dict_filehandle, column_list );
--
--
--
-- Create SQL statement
colout_str := 'SELECT ' || column_list || ' FROM ' || in_Table_Name || ' ' || in_Where_Clause;
--
colout_c := DBMS_SQL.OPEN_CURSOR;
--
--
--
DBMS_SQL.PARSE(colout_c, colout_str, DBMS_SQL.NATIVE);
--
--
--
-- Define columns for the select
i := 1;
WHILE i <= in_Column_Count LOOP
IF col_test_array(i).col_datatype = 'VARCHAR2' THEN
DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_vr2, col_test_array(i).col_datalength);
ELSIF col_test_array(i).col_datatype = 'NUMBER' THEN
DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_num);
ELSIF col_test_array(i).col_datatype = 'DATE' THEN
DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_vr2, 19);
ELSIF col_test_array(i).col_datatype = 'LONG' THEN
DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_vr2, col_test_array(i).col_datalength);
ELSIF col_test_array(i).col_datatype = 'RAW' THEN
DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_raw, col_test_array(i).col_datalength);
ELSIF col_test_array(i).col_datatype = 'MLSLABEL' THEN
DBMS_SQL.DEFINE_COLUMN(colout_c,i, col_test_array(i).col_value_mls, col_test_array(i).col_datalength);
ELSE
DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN datatype = ' || col_test_array(i).col_datatype);
END IF;
i := i +1;
END LOOP;
--
--
--
-- Execute the SQL code
d := DBMS_SQL.EXECUTE(colout_c);
--
--
<< fetchrow_loop >>
LOOP
IF DBMS_SQL.FETCH_ROWS(colout_c) = 0 THEN
-- DBMS_OUTPUT.PUT_LINE( ' All rows in TABLE: ' || in_Table_Name || ' recorded total rows = ' || row_count);
EXIT;
END IF;
UTL_FILE.PUT(dict_filehandle, 'INSERT_INTO ' || in_Table_Name || '_TABLE VALUES (' );
--
-- Get values for all columns selected
i := 1;
WHILE i < in_Column_Count LOOP
--
IF col_test_array(i).col_datatype = 'VARCHAR2' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
vr2_replacement := REPLACE(col_test_array(i).col_value_vr2, '''', '''''');
UTL_FILE.PUT(dict_filehandle, '''' ||vr2_replacement || ''',');
--
ELSIF col_test_array(i).col_datatype = 'NUMBER' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_num);
UTL_FILE.PUT(dict_filehandle, col_test_array(i).col_value_num || ',');
--
ELSIF col_test_array(i).col_datatype = 'DATE' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
IF(col_test_array(i).col_value_vr2 is NULL) THEN
UTL_FILE.PUT(dict_filehandle, ',');
ELSE
UTL_FILE.PUT(dict_filehandle, 'to_date(''' || col_test_array(i).col_value_vr2 || ''', ''MM/DD/YYYY HH24:MI:SS''),');
END IF;
--
ELSIF col_test_array(i).col_datatype = 'LONG' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_lng);
UTL_FILE.PUT(dict_filehandle, '''' ||col_test_array(i).col_value_lng || ''',');
--
ELSIF col_test_array(i).col_datatype = 'RAW' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_raw);
UTL_FILE.PUT(dict_filehandle, col_test_array(i).col_value_raw || ',');
--
ELSIF col_test_array(i).col_datatype = 'MLSLABEL' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_mls);
UTL_FILE.PUT(dict_filehandle, col_test_array(i).col_value_mls || ',');
--
ELSE
DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN datatype = ' || col_test_array(i).col_datatype);
DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN Name = ' || col_test_array(i).col_name );
--
END IF;
--
i := i +1;
--
END LOOP;
--
--
IF col_test_array(i).col_datatype = 'VARCHAR2' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
UTL_FILE.PUT_LINE(dict_filehandle, '''' || col_test_array(i).col_value_vr2 || ''');');
--
ELSIF col_test_array(i).col_datatype = 'NUMBER' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_num);
UTL_FILE.PUT_LINE(dict_filehandle, col_test_array(i).col_value_num || ');');
--
ELSIF col_test_array(i).col_datatype = 'DATE' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_vr2);
IF(col_test_array(i).col_value_vr2 is NULL) THEN
UTL_FILE.PUT_LINE(dict_filehandle, ' );');
ELSE
UTL_FILE.PUT_LINE(dict_filehandle, 'to_date(''' || col_test_array(i).col_value_vr2 || ''', ''MM/DD/YYYY HH24:MI:SS''));');
END IF;
--
-- DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_dat);
-- UTL_FILE.PUT_LINE(dict_filehandle, '''' ||col_test_array(i).col_value_dat || ''');');
--
ELSIF col_test_array(i).col_datatype = 'LONG' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_lng);
UTL_FILE.PUT_LINE(dict_filehandle, '''' ||col_test_array(i).col_value_lng || ''');');
--
ELSIF col_test_array(i).col_datatype = 'RAW' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_raw);
UTL_FILE.PUT_LINE(dict_filehandle, col_test_array(i).col_value_raw || ');');
--
ELSIF col_test_array(i).col_datatype = 'MLSLABEL' THEN
DBMS_SQL.COLUMN_VALUE(colout_c,i, col_test_array(i).col_value_mls);
UTL_FILE.PUT_LINE(dict_filehandle, col_test_array(i).col_value_mls || ');');
--
ELSE
DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN datatype = ' || col_test_array(i).col_datatype);
DBMS_OUTPUT.PUT_LINE ( 'UNKNOWN Name = ' || col_test_array(i).col_name );
--
END IF;
--
--
row_count := row_count +1;
--
END LOOP fetchrow_loop;
--
--
--
DBMS_SQL.CLOSE_CURSOR(colout_c);
--
--
--
--
RETURN i;
--
EXCEPTION
-- Create Exception to simply display error code and message
WHEN OTHERS THEN
-- DBMS_OUTPUT.PUT_LINE
-- ('ERROR ' || to_char(SQLCODE) || SQLERRM);
-- NULL;
--
DBMS_OUTPUT.PUT_LINE( ' TABLE: ' || in_Table_Name || ' ' || SQLERRM );
RETURN 0;
--
END;
--
--
-- **************************************************************************
--
-- RECORD_TABLE Function
-- This function issues calls to the get_column_description,
-- write_correct_table, and write_insert_into functions for
-- a given table name.
--
--
FUNCTION record_table (
in_Table_Name IN VARCHAR2,
in_Where_Clause IN VARCHAR2 )
RETURN NUMBER IS
--
--
insert_into_result NUMBER := 0;
create_table_result NUMBER := 0;
--
--
col_count NUMBER := 0;
i NUMBER := 1;
--
--
--
--
--
BEGIN
--
--
col_count := get_column_description(in_Table_Name);
--
--
create_table_result := write_create_table( in_Table_Name, col_count);
--
--
UTL_FILE.NEW_LINE(dict_filehandle,1);
--
--
insert_into_result := write_insert_into( in_Table_Name, col_count, in_Where_Clause);
--
--
UTL_FILE.NEW_LINE(dict_filehandle,4);
--
IF create_table_result != 0 THEN
DBMS_OUTPUT.PUT_LINE( ' TABLE: ' || in_Table_Name || ' recorded in LogMnr Dictionary File' );
END IF;
--
RETURN create_table_result ;
--
--
END;
--
--
-- **************************************************************************
--
-- BUILD Procedure
-- This public procedure coordinates the creation of the LogMnr
-- dictionary file. function issues calls to the get_column_description,
-- write_correct_table, and write_insert_into functions for
-- a given table name.
--
--
BEGIN
--
-- select value INTO dest_location from v$system_parameter where name = 'user_dump_dest';
--
DBMS_OUTPUT.PUT_LINE ('LogMnr Dictionary Procedure started ' );
--
-- open file
--
BEGIN
dict_filehandle := UTL_FILE.FOPEN(dictionary_location, dictionary_filename,'w');
EXCEPTION
-- Create Exception to simply display error code and message
WHEN utl_file.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('ERROR ' || to_char(SQLCODE) || ': ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('LogMiner Dictionary - file location or name was invalid ');
DBMS_OUTPUT.PUT_LINE(' - ensure utl_file_dir parameter in init.ora
is set correctly');
RAISE utl_file.INVALID_PATH;
NULL;
--
END;
--
DBMS_OUTPUT.PUT_LINE ('LogMnr Dictionary File Opened' );
--
-- write coments to file
comment_result := write_comments(dict_filehandle);
--
-- ensure a consistent read of the dictionary tables
SET TRANSACTION READ ONLY;
--
-- create the dictionary table
db_header_result := get_dict_data(dict_filehandle);
--
-- create the remaining table
--
record_table_result := record_table('OBJ$',
'WHERE type# != 4 AND type# != 5 AND type# != 7 AND type# != 8
AND type# != 9 AND type# != 10 AND type# != 22');
--
record_table_result := record_table('TAB$', '');
--
record_table_result := record_table('COL$', '');
--
record_table_result := record_table('SEG$', '');
--
record_table_result := record_table('UNDO$', '');
--
record_table_result := record_table('UGROUP$', '');
--
record_table_result := record_table('TS$', '');
--
record_table_result := record_table('CLU$', '');
--
record_table_result := record_table('IND$', '');
--
record_table_result := record_table('ICOL$', '');
--
record_table_result := record_table('LOB$', '');
--
record_table_result := record_table('USER$', '');
--
record_table_result := record_table('FILE$', '');
--
record_table_result := record_table('PARTOBJ$', '');
--
record_table_result := record_table('PARTCOL$', '');
--
record_table_result := record_table('TABPART$', '');
--
record_table_result := record_table('INDPART$', '');
--
record_table_result := record_table('SUBPARTCOL$', '');
--
record_table_result := record_table('TABSUBPART$', '');
--
record_table_result := record_table('INDSUBPART$', '');
--
record_table_result := record_table('TABCOMPART$', '');
--
record_table_result := record_table('INDCOMPART$', '');
--
DBMS_OUTPUT.PUT_LINE ('Procedure executed successfully - LogMnr Dictionary Created' );
--
-- ensure buffer is flushed
UTL_FILE.FFLUSH(dict_filehandle);
--
-- commit read transaction
-- NEED TO ADD EXCEPTION HANDLING IN THE EVENT OF FAILURE
COMMIT;
--
-- close file
UTL_FILE.FCLOSE(dict_filehandle);
--
--
EXCEPTION
-- Create Exception to simply display error code and message
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('ERROR ' || SQLCODE || ' ' || SQLERRM);
NULL;
COMMIT;
RAISE;
--
UTL_FILE.FCLOSE(dict_filehandle);
-- DBMS_OUTPUT.PUT_LINE('Processed Rows = ' || row_count || 'total =' || rcount);
--
END build;
--
END dbms_logmnr_d;
/
show errors
grant execute on dbms_logmnr_d to execute_catalog_role
/
SVK,
I beleive dbmslogmnrd.sql will create the LogMnr dictionary ASCII file.
How it will show me the contents of the particular Archive Log File?
Can you also show examples of using dbmslogmnrd package?
Thanks,
Refer to the manual for detail process but, here is the gist of it :
First create the dictionary file. You will have to have the 'utl_file_dir' parameter set in the init.ora. This file is required for generating 'readable' output.
Add the on-line redo log or archive log using the dbms_logmnr.add_logfile function.
Start the logminer using dbms_logmnr.start_logmnr.
Use the v$logmnr_contents view to see the details.
SVK,
Thanks.
Refer to the manual for detail process
- Which manual?
Add the on-line redo log or archive log using the dbms_logmnr.add_logfile function.
- There is no procedure or function called 'add_logfile' in
dbms_logmnr package.
- Do we have to add existing/old Archive log file?
The package name which you have posted is dbms_logmnrd but not dbms_logmnr.
Pl. clear above doubts.
Thanks for your inputs to this thread.