Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to capture DML statements on a table in Oracle database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to capture DML statements on a table in Oracle database"!

[a] dml triggers can be used, such as

CREATE OR REPLACE TRIGGER tr_capt_sql

BEFORE DELETE OR INSERT OR UPDATE

ON manager.test

DECLARE

Sql_text ora_name_list_t

State_sql capt$sql.sql_text%TYPE

BEGIN

FOR i IN 1..ora_sql_txt (sql_text) LOOP

State_sql: = state_sql sql_text (I)

END LOOP

INSERT INTO capt$sql (login_user,capt_time,ip_address,audsid,owner,table_name,sql_text)

VALUES (ora_login_user,sysdate,sys_context ('USERENV','IP_ADDRESS'))

Userenv ('SESSIONID'),' MANAGER','TEST',state_sql)

EXCEPTION

WHEN OTHERS THEN

Sp_write_log ('Capture DML Exception:'SQLERRM)

END tr_capt_sql

There are several points to pay attention to when creating the above trigger

1. A capt$sql table must be created to record ddl records.

2. Sp_write_log is originally a process of writing logs, which can be replaced with your own needs, such as null skip.

[Q] how to generate a file in date format

[a] on LINUX/UNIX, use `date +% y% m% d` (`this is the key on the keyboard) or $(date +% y%m%d), such as:

Touch exp_table_name_ `date +% y% m% d`.dm

DATE=$ (date +% y%m%d)

Or

DATE=$ (date +% Y%m%d-- date'1 days ago') # gets the date from yesterday or more days ago

On Windows, use% date:~4,10%, where 4 is the start character and 10 is the extraction length, which means that a string with a length of 10 starting from 4 is extracted from the date generated by date. You can change it to other numbers you need, such as:

Echo date:~4,10%

If you want to get a more accurate time, you can also use time on win

[Q] Test disk and array performance

[a] Test the writing ability in a method similar to the following

Time dd if=/dev/zero of=/oradata/biddb/testind/testfile.dbf bs=1024000 count=1000

During this period, the system IO can be used (unix):

Iostat-xnp 2 shows the degree of Busy

[Q] how to configure SSH key

[a] it can prevent the attack of "middleman"

1. Ssh-keygen or ssh-keygen-d (ssh 2.x) generate keys

2. Then copy the public key to the server you want to log in and rename it to authorized_keys. If it is below version 3.0, you need to change it to authorized_keys2.

3. Config files can also be used to further simplify the operation.

Such as

Host * bj

HostName machine name or IP

User user name

With this configuration file, you can use ssh bj to access the specified machine, and you can use scp and sftp to transfer files.

[Q] how to automatically upload / download FTP in the script

[a] you can write FTP into a shell script, such as

Ftp-n-I host IP

Cd destination directory

Put file

Get file

# query files

Ls

# exit

At this point, I believe you have a deeper understanding of "how to capture DML statements on the table in the Oracle database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.