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

Oracle Vernier Collection

The SELECT surprise sentence uses surprise to query data from the database. When you use the SELECT surprise clause in PL/SQL, or the INTO clause is used together, the return value of the query is assigned to the variable in the INTO clause, and the variable is declared in DELCARE. The SELECT INTO surprise method is as follows:

SELECT [DISTICT | ALL] {* | column [, column,...]}

INTO (variable [, variable,...] | record)

FROM {table | (sub-query)} [alias]

WHERE.

The SELECT surprise sentence in PL/SQL returns a row of data. If you have more than one row of data, you should use explicit cursors (we'll cover the rows later in the discussion of cursors), and the INTO clause should have variables that do not have the same number of columns in the SELECT clause. It can also be a token variable in the INTO clause.

% TYPE attribute

Variables and constants can be declared as built-in user-defined data types in PL/SQL to refer to a column name while inheriting its data type and size. The return state assignment method is very useful, for example, the data type and size of the column referenced by the variable has changed, and if% TYPE is used, the user does not have to modify the code, otherwise the code must be modified.

Example:

V_empno SCOTT.EMP.EMPNO%TYPE

V_salary EMP.SALARY%TYPE

But column names can use% TYPE, while constants such as variables, cursors, notations, and ring declarations can all use% TYPE. Returns are useful for "fixing" variables of the same data type.

DELCARE

Vega NUMBER (5): = 10

V_B V_A%TYPE:=15

V_C V_A%TYPE

BEGIN

DBMS_OUTPUT.PUT_LINE

('Vetera' | | VroomA | | 'VroomB | |' VroomC'| | VroomC)

END

SQL > /

V_A=10 V_B=15 Vacancy C =

PL/SQL procedure successfully completed.

SQL >

Other DML surprise sentences

Other DML surprise sentences for manipulating data are: INSERT, UPDATE, DELETE and LOCK TABLE. Some surprise sentences are not the same in PL/SQL as in SQL. We have discussed the use of DML surprise sentences earlier and will not repeat them again. The variable declared in the DECLARE section can be used in the DML surprise sentence. If it is a nested block, pay attention to the scope of the variable.

Example:

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)

AS

V_ename EMP.ENAME%TYPE

BEGIN

SELECT ename INTO v_ename

FROM emp

WHERE empno=p_empno

INSERT INTO FORMER_EMP (EMPNO,ENAME)

VALUES (paired empnother vandename)

DELETE FROM emp

WHERE empno=p_empno

UPDATE former_emp

SET date_deleted=SYSDATE

WHERE empno=p_empno

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE ('Employee Number Not foundations')

END

The result of DML surprise sentence

When a DML surprise sentence is executed, the result of the DML surprise sentence is saved in four cursor properties, some of which are used to control the flow of the program to know the state of the program. When running the DML surprise sentence, PL/SQL opens a built-in cursor to process the result. The cursor is a key field in memory that maintains the query result. The cursor opens when running the DML surprise sentence and closes when it is finished. The implicit cursor uses three attributes of SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT. SQL% found, SQL%NOTFOUND is a Boolean value, and SQL%ROWCOUNT is an integer value.

SQL%FOUND and SQL%NOTFOUND

The values of SQL%FOUND and SQL%NOTFOUND are both NULL before the execution of the surprise sentence of DML. After the execution of the surprise sentence of DML, the attribute value of SQL%FOUND will be:

. TRUE: INSERT

. TRUE ELETE and UPDATE, at least one line was abstaining from UPDATE by DELETE.

. TRUE: SELECT INTO returns at least one row

When SQL%FOUND is TRUE, SQL%NOTFOUND is FALSE.

SQL%ROWCOUNT

Before the execution of "he DML surprise sentence", the value of SQL%ROWCOUNT is NULL. For the surprise sentence of SELECT INTO, if the execution is successful, the value of SQL%ROWCOUNT is 1. If it is not successful, the value of SQL%ROWCOUNT is 0, and an exception NO_DATA_FOUND is generated.

SQL%ISOPEN

SQL%ISOPEN is a Boolean value, TRUE if the cursor is open, FALSE if the cursor is closed. SQL%ISOPEN is always FALSE for implicit cursors, because the implicit cursor opens when the DML surprise sentence is executed and closes immediately at the end.

Transaction control surprise sentence

A transaction is a logical unit of work that can include multiple DML surprise sentences, and the control of things helps users to ensure data consistency. If a DML surprise sentence in the transaction control logic unit fails, the entire transaction will be rolled back, and the user can explicitly use COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION surprise sentences in PL/SQL.

COMMIT surprise sentence terminates the transaction, Yongli saves the database changes, while releasing all LOCK,ROLLBACK terminates the current transaction releases all LOCK, but does not save the database changes, SAVEPOINT sets the intermediate point, when the transaction calls too many database operations, the intermediate point is very useful, SET TRANSACTION uses quit to set transaction properties, such as read-write and destroy delamination and so on.

Explicit cursor

When the result of the query returns more than one row, an explicit cursor is required, and the user cannot use the select into surprise sentence. PL/SQL manages implicit cursors, which open when the query starts and close automatically when the query ends. The explicit cursor declares in the declaration section of the PL/SQL block, opens, fetches data, and closes the exception handling section in the execution part.

Use cursors

To make a statement back, we usually mean explicit cursors, so from now on there will be no cursors.

For specifically specified situations, the cursors we are talking about refer to explicit cursors. To use cursors in a program, you must first declare cursors.

Declare a cursor

Surprise method:

CURSOR cursor_name IS select_statement

In PL/SQL, the cursor name is an undeclared variable, and you cannot assign a value to the cursor name to avoid using the cursor expression.

Example:

DELCARE

CURSOR C_EMP IS SELECT empno,ename,salary

FROM emp

WHERE salary > 2000

ORDER BY ename

.

BEGIN

In the SELECT surprise sentence in Vernier calibration, it is not necessary that the table can be a view, you can select columns from multiple list views, and you can even use * to select all columns.

Open the cursor

You should first open the cursor and open the cursor to initialize the query processing before using the value in the cursor. The surprise way to open the cursor is:

OPEN cursor_name

Cursor_name is the cursor name specified in the declaration section.

Example:

OPEN C_EMP

Close the cursor

Surprise method:

CLOSE cursor_name

Example:

CLOSE C_EMP

Extract data from cursors

Use the FETCH command to get a row of data from the cursor. Each time the data is extracted, the cursor points to the knot

The next row of the fruit set. The surprise method is as follows:

FETCH cursor_name INTO variable [, variable,...]

For each column of the cursor defined by SELECT, the list of FETCH variables should have a variable corresponding to it, and the variable should be of the same type.

Example:

SET SERVERIUTPUT ON

DECLARE

V_ename EMP.ENAME%TYPE

V_salary EMP.SALARY%TYPE

CURSOR c_emp IS SELECT ename,salary FROM emp

BEGIN

OPEN c_emp

FETCH c_emp INTO v_ename,v_salary

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

FETCH c_emp INTO v_ename,v_salary

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

FETCH c_emp INTO v_ename,v_salary

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

CLOSE c_emp

END

There is no doubt that the return segment code is very troublesome. If there are multiple lines of return results, you can use the loop pointer to use the cursor attribute as the condition to end the loop and extract the data in a return way, so that the readability and simplicity of the program are greatly improved. let's use a loop to rewrite the above program:

SET SERVERIUTPUT ON

DECLARE

V_ename EMP.ENAME%TYPE

V_salary EMP.SALARY%TYPE

CURSOR c_emp IS SELECT ename,salary FROM emp

BEGIN

OPEN c_emp

LOOP

FETCH c_emp INTO v_ename,v_salary

EXIT WHEN c_emp%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary)

END

Remember the variable

Specify a token variable to use the TYPE command and% ROWTYPE. For more information about% ROWsTYPE, please refer to the relevant materials.

Notation variables extract rows of data from cursors, and when cursors select many columns, it is much more convenient to use notation than to declare a variable for each column.

When using% ROWTYPE entries on a table to record values taken from cursors, it is much safer to use * in the SELECT clause than to list all the column names if you want to select all the columns in the table.

Example:

SET SERVERIUTPUT ON

DECLARE

R_emp EMP%ROWTYPE

CURSOR c_emp IS SELECT * FROM emp

BEGIN

OPEN c_emp

LOOP

FETCH c_emp INTO r_emp

EXIT WHEN c_emp%NOTFOUND

DBMS_OUT.PUT.PUT_LINE ('Salary of Employee' | | r_emp.ename | |' is' | | r_emp.salary)

END LOOP

CLOSE c_emp

END

% ROWTYPE can also be defined with a cursor name. If you return the sample, you must first declare the cursor:

SET SERVERIUTPUT ON

DECLARE

CURSOR c_emp IS SELECT ename,salary FROM emp

R_emp c_emp%ROWTYPE

BEGIN

OPEN c_emp

LOOP

FETCH c_emp INTO r_emp

EXIT WHEN c_emp%NOTFOUND

DBMS_OUT.PUT.PUT_LINE ('Salary of Employee' | | r_emp.ename | |' is' | | r_emp.salary)

END LOOP

CLOSE c_emp

END

Cursor with parameters

No stored procedures are similar to functions, and parameters can be passed to cursor markers for use in queries. It is very useful to handle situations where cursors are opened under certain conditions. Its surprise is as follows:

CURSOR cursor_name [(parameter [, parameter],...)] IS select_statement

The surprise method for determining the parameters is as follows:

Parameter_name [IN] data_type [{: = | DEFAULT} value]

Unlike a stored procedure, a cursor can accept the value passed, but cannot return the value. Parameter "set" data type, no size.

In addition, you can set a default value for the parameter, which is used when no parameter value is passed to the cursor. The parameter defined in the cursor is a placeholder, and it may not be reliable to reference it elsewhere.

Assign a value to the parameter when opening the cursor. The surprise method is as follows:

OPEN cursor_name [value [, value]....]

The parameter value can be a text ring variable.

Example:

DECALRE

CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno

CURSOR c_emp (p_dept VARACHAR2) IS

SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename

R_dept DEPT%ROWTYPE

V_ename EMP.ENAME%TYPE

V_salary EMP.SALARY%TYPE

V_tot_salary EMP.SALARY%TYPE

BEGIN

OPEN c_dept

LOOP

FETCH c_dept INTO r_dept

EXIT WHEN c_dept%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname)

V_tot_salary:=0

OPEN c_emp (r_dept.deptno)

LOOP

FETCH c_emp INTO v_ename,v_salary

EXIT WHEN c_emp%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary)

V_tot_salary:=v_tot_salary+v_salary

END LOOP

CLOSE c_emp

DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary)

END LOOP

CLOSE c_dept

END

Vernier FOR loop

Most of the time we follow these steps when designing programs:

1. Open the cursor

2. Start the cycle

3. Take a value from the cursor

4. Check which line is returned.

5. Deal with

6. Close the loop

7. Close the cursor

You can simply refer to the return type of code as a cursor loop. However, there is a kind of loop that does not return to the same type, which is the FOR loop. The cursor of the circle FOR loop is declared according to the normal declaration. Its advantage lies in that it does not need to explicitly open, close, fetch data, test the existence of data, determine the variables that store the data, and so on. The surprise method of the cursor FOR loop is as follows:

FOR record_name IN

(corsor_name [(parameter [, parameter]...)]

| (query_difinition)

LOOP

Statements

END LOOP

Let's rewrite the above example with a for loop:

DECALRE

CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno

CURSOR c_emp (p_dept VARACHAR2) IS

SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename

V_tot_salary EMP.SALARY%TYPE

BEGIN

FOR r_dept IN c_dept LOOP

DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname)

V_tot_salary:=0

FOR r_emp IN c_emp (r_dept.deptno) LOOP

DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary)

V_tot_salary:=v_tot_salary+v_salary

END LOOP

DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary)

END LOOP

END

Using queries in cursor FOR loops

In the cursor FOR loop, you can define the query. The cursor is not explicitly declared, so the cursor has no name, and the name of the cursor is determined by the cursor query.

DECALRE

V_tot_salary EMP.SALARY%TYPE

BEGIN

FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP

DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname)

V_tot_salary:=0

FOR r_emp IN (SELECT ename,salary

FROM emp

WHERE deptno=p_dept

ORDER BY ename) LOOP

DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary)

V_tot_salary:=v_tot_salary+v_salary

END LOOP

DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary)

END LOOP

END

Subqueries in cursors

The surprise method is as follows:

CURSOR C1 IS SELECT * FROM emp

WHERE deptno NOT IN (SELECT deptno

FROM dept

WHERE dnameplate accounts

You can see that there is no key for subqueries in SQL.

Updates and deletes in cursors

You can still use UPDATE and DELETE surprise sentences to update and delete data rows in PL/SQL. Explicit cursors are used in situations where multiple rows of data need to be obtained. PL/SQL provides a way to delete ring updates by simply using cursors.

The WHERE CURRENT OF substring and gate in the UPDATE DELETE surprise sentence process the latest data taken from the table to perform the UPDATE ring DELETE operation. To use the return method, you must use the FOR UPDATE substring when declaring the cursor. When the conversation uses the FOR UPDATE substring to open a cursor, all data rows in the callback set will be exclusively locked at the row level (ROW-LEVEL). Other objects can query some data rows, and cannot perform UPDATE and DELETE operations.

Surprise method:

FOR UPDATE [OF [schema.] table.column [, [schema.] table.column]..

[nowait]

In a multi-table query, the OF clause is used to lock a specific table, and if the OF clause is omitted, the data rows selected in all tables are locked. If the returned rows are already locked by another session, under normal circumstances ORACLE will wait until the rows are unlocked.

The surprise method of using WHERE CURRENT OF substring in UPDATE and DELETE is as follows:

WHERE {CURRENT OF cursor_name | search_condition}

Example:

DELCARE

CURSOR c1 IS SELECT empno,salary

FROM emp

WHERE comm IS NULL

FOR UPDATE OF comm

V_comm NUMBER (10Phone2)

BEGIN

FOR r1 IN c1 LOOP

IF r1.salary show parameter utl

NAME TYPE VALUE

Utl_file_dir string / data6/cyx/logmnr

The return directory is mainly used to store the dictionary information files generated by the dbms_logmnr_d.build process, if you do not need to return, you can open it, and just skip the following step.

2. Generate dictionary information file:

Exec dbms_logmnr_d.build (dictionary_filename = >')

Dic.ora',dictionary_location = >'/ data6/cyx/logmnr')

Where dictionary_location refers to the location of the dictionary information file, it must match the value of UTL_FILE_DIR completely. For example, if UTL_FILE_DIR=/data6/cyx/logmnr/, returns a surprise sentence above, there will be an error, because there is an extra "/" after UTL_FILE_DIR, but in many other places it is insensitive to return one "/".

Dictionary_filename refers to the name of the dictionary information file, which can be chosen as desired. Of course, we can not explicitly write back two options, that is, as follows:

Exec dbms_logmnr_d.build ('dic.ora','/data6/cyx/logmnr')

If you do not set the parameters for the first step and start to take a step back, Oracle will report the following error:

ERROR at line 1:

ORA-01308: initialization parameter utl_file_dir is not set

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 923

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 1938

ORA-06512: at line 1

It is important to note that the following error occurs in the oracle817 for Windows version:

14:26:05 SQL > execute dbms_logmnr_d.build ('oradict.ora','c:\ oracle\ admin\ ora\ log')

BEGIN dbms_logmnr_d.build ('oradict.ora','c:\ oracle\ admin\ ora\ log'); END

*

ERROR at line 1:

ORA-06532: Subscript. Outside of limit

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793

ORA-06512: at line 1

Solution:

Edit the "$ORACLE_HOME/rdbms/admindbmslmd.sql" file and put the

TYPE col_desc_array IS VARRAY (513) OF col_description

Change it to:

TYPE col_desc_array IS VARRAY (700) OF col_description

Save the file, and then execute the return script again:

15:09:06 SQL > @ c:\ oracle\ ora81\ rdbms\ admin\ dbmslmd.sql

Package created.

Package body created.

No errors.

Grant succeeded.

Then recompile the DBMS_LOGMNR_D package:

15:09:51 SQL > alter package DBMS_LOGMNR_D compile body

Package body altered.

Re-execute dbms_logmnr_d.build after you have completed the following:

15:10:06 SQL > execute dbms_logmnr_d.build ('oradict.ora','c:\ oracle\ admin\ ora\ log')

PL/SQL procedure successfully completed.

3. Add log files to be analyzed

SQL > exec dbms_logmnr.add_logfile (logfilename= >'

/ data6/cyx/rac1arch/arch_1_197.arc', ptions= > dbms_logmnr.new)

PL/SQL procedure successfully completed.

The options option in the return has three parameters:

NEW-means to create a new list of log files

ADDFILE-means to add a log file to the return list, as in the following example

REMOVEFILE-the opposite of addfile.

SQL > exec dbms_logmnr.add_logfile (logfilename= >'

/ data6/cyx/rac1arch/arch_2_86.arc', ptions= > dbms_logmnr.addfile)

PL/SQL procedure successfully completed.

4. After you have added the log files that need to be analyzed, we can let LogMiner start the analysis:

SQL > exec dbms_logmnr.start_logmnr (dictfilename= >'/ data6/cyx/logmnr/dic.ora')

PL/SQL procedure successfully completed.

If you don't use a dictionary information file (we just need to activate an example at this time), that's fine.

You need to follow the dictfilename parameter:

SQL > exec dbms_logmnr.start_logmnr ()

PL/SQL procedure successfully completed.

Of course, the dbms_logmnr.start_logmnr () procedure has several other parameters that use "fix" to analyze the log time / SCN window, which are:

STARTSCN / ENDSCN-the start / end SCN number of the fixed analysis

STARTTIME / ENDTIME-determine the start / end time of the analysis.

For example, the following procedure analyzes the log for a period of time from '2003-09-21 09-21 09-21 09-21 09-21 09-22 09-21 09-21 09-15 15: 00 back to' 2003-09-21 09-21 09:

SQL > exec dbms_logmnr.start_logmnr (dictfilename= >'/ data6/cyx/logmnr/dic.ora',-

Starttime = > '2003-09-21 09-21 09-21 09-39) endtime = >' 2003-09-21 09-21 09 15)

PL/SQL procedure successfully completed.

The "-" at the end of the first line of the above process indicates a career change. If you are on the same line, you don't need it. We can see the timestamp of the valid log:

SQL > select distinct timestamp from v$logmnr_contents

TIMESTAMP

-

2003-09-21 09:40:02

2003-09-21 09:42:39

It is important to note that since we have set the NLS_DATE_FORMAT environment variable before, the above date can be written directly in the return format. If you do not set it, you need to use to_date.

Function to convert it.

SQL >! env | grep NLS

NLS_LANG=american_america.zhs16cgb231280

NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

ORA_NLS33=/oracle/oracle9/app/oracle/product/9.2.0/ocommon/nls/admin/data

The format for using to_date is as follows:

Exec dbms_logmnr.start_logmnr (dictfilename= >'/ data6/cyx/logmnr/dic.ora',-

Starttime = > to_date ('2003-09-21 09 / 21 09 / 39 / 39 / 09 / 21 / 09 / 21 / 09 / 09 / 21 / 09 / 39 / 09 / 21 / 09 / 39 / 09 / 21 / 09 / 09 / 21 / 09 / 39).

Endtime = > to_date ('2003-09-21 09-21 09 / 21 09 / 45 / 05 / 05 / 45 / 05)

The STARTSCN and ENDSCN parameters are used in similar ways.

5. Well, after the completion of the above process, we can extract the information we need by visiting several views that are not related to LogMiner. You can see the list of logs we are currently analyzing in v$logmnr_logs. If the database has two instances (that is, OPS/RAC), there will be two different THREAD_ID in v$logmnr_logs.

The real result of the analysis is put in the v$logmnr_contents, and there is a lot of information in it, and we can track the information we are interested in as needed. I will list the common tracking situations separately later.

6. After all is finished, we can execute the dbms_logmnr.end_logmnr process to exit the LogMiner analysis process, or you can directly exit the SQL*PLUS, which will automatically terminate.

4. How to use LogMiner to analyze Oracle8 log files

Although it is said that LogMiner was pushed by Oracle8i, we can also use it to analyze the log files of Oracle8. However, it is a little troublesome, and there are some restrictions on Oracle8. Here are the specific methods:

We first copy the $ORACLE_HOME/rdbms/admin/dbmslmd.sql script of Oracle8i to the same destination of the host where the Oracle8 database is located; the return script creates the dbms_logmnr_d package with Oracle9i (note that the dbms_logmnr package will be created in Oracle9i), and the name of the 8.1.5 script is dbmslogmnrd.sql. Then run the return script on Oracle8's database, and then use the

The dbms_logmnr_d.build process creates a dictionary information file. Now we can copy the Oracle8 archive log together with a return dictionary information file to the host where the Oracle8i database is located, and then analyze the Oracle8 log in the Oracle8i database from the third step of the above analysis process, but

The dictionary information file for Oracle8 is used in dbms_logmnr.start_logmnr ().

As I said earlier, if it is not a dictionary file, we can copy the archive log of Oracle8 directly to the host where the Oracle8i database resides, and then analyze it.

In fact, the return involves a cross-platform use of LogMiner, the author has done experiments, you can also analyze the Oracle8i log in Oracle9i. However, some returns are limited, mainly as follows:

1. The dictionary file used by LogMiner must be generated from the same database as the log file being analyzed, and the character set of the database should be the same as that of the LogMiner database. It is easy to understand that there is no corresponding relationship if it is not generated by the same database.

2. The requirements of the database hardware platform for generating the log and the hardware platform for executing the LogMiner database are the same, and the version of the operating system can be different. When the author is doing the experiment (if readers are interested, you can download the whole process of the experiment on my website http://www.ncn.cn, because it is too long to put it back), the two database operating systems used are tru64/ UNIX, but one is V5.1A and the other is V4.0F. If the operating system is not consistent, the following wrong gag will occur:

ORA-01284: file / data6/cyx/logmnr/arch_1_163570.arc cannot be opened

ORA-00308: cannot open archived log'/ data6/cyx/logmnr/arch_1_163570.arc'

ORA-27048: skgfifi: file header information is invalid

ORA-06512: at "SYS.DBMS_LOGMNR", line 63

ORA-06512: at line 1

5. Analysis of v$logmnr_contents

We already know that the analysis results of LogMiner are put in v$logmnr_contents, and there is a lot of information back, so we can track the information we are interested in according to our needs. What are we usually interested in?

1. Track the changes in the database structure, that is, DDL operations. As mentioned earlier, only Oracle9i supports returning a database:

SQL > select timestamp,sql_redo from v$logmnr_contents2

Where upper (sql_redo) like'% CREATE%'

TIMESTAMP

-

SQL_REDO

-

2003-09-21 10:01:55

Create table t (C1 number)

2. Tracking user gag operations to avoid malicious operations:

For example, we have a real need to return samples. Once we found that an employee had modified the business database information through a program to change the charging type of some phone calls to free. Now we are required to find out who did the return work from the database. How can I find out? LogMiner provides us with the means to analyze log files, in which v$logmnr_contents 's SESSION_INFO package contains the following information:

Login_username=NEW_97

Client_info= OS_username=oracle8 Machine_name=phoenix1

OS_terminal=ttyp3 OS_process_id=8004 OS_program name=sqlplus@phoenix1

(TNS V1-V3)

Although there is a lot of information, but in our business database, the program logs in to the database through the same login_username, and it is difficult to judge from the above information.

However, we have noticed that because not everyone in the company's application server has the right to write programs on it, generally malicious programs are directly connected to the database through their own PC, which requires an accurate location. IP tracking is the first thing that comes to mind, and IP tracking also meets our actual requirements, because the internal IP address allocation is managed uniformly, and we can accurately locate the IP address if we can trace it. But we can't see the IP directly from the SESSION_INFO, but we have a way, because the content in the SESSION_INFO is actually extracted from the V$SESSION view, and we can

To create a trigger in the production database that tracks the client's IP address:

Create or replace trigger on_logon_trigger

After logon on database

Begin

Dbms_application_info.set_client_info (sys_context ('userenv',' ip_address'))

End

/

We can now see the newly logged-in client IP address in the CLIENT_INFO column of the V$SESSION view. Then the problem raised above can be easily solved. If the updated table is named HMLX, we can find the required information through the following SQL:

SQL > select session_info, sql_redo from v$logmnr_contents

2 where upper (operation) = 'UPDATE' and upper (sql_redo) like'% HMLX%'

3 /

SESSION_INFO

-

SQL_REDO

-

Login_username=C client_info=10.16.98.26 OS_username=sz-xjs-chengyx Machine_name

= GDTEL\ SZ-XJS-CHENGYX

Update "C". "HMLX" set "NAME" = 'free' where "NAME" =' ncn.cn' and ROWID = 'AAABhTAA

FAAABRaAAE'

3. Use stored procedures to partition tables periodically in ORACLE

There are all kinds of data stored in the Oracle database, some of which will become larger and larger over time. Such as the log of making friends and chatting, the log of sending and receiving short messages, the log of the production system, the log of the website publishing system, and so on. The return information is closely related to time, is there any way to divide some log tables into calendar years and months (such as log200308,log200309) according to time? Please take a look at the way I use stored procedures to divide the table on a regular basis.

First, the introduction of the question

1. When I first learned the database, I knew how to use delete to delete the data in the table. However, in the Oracle database, after a large number of delete records, we can not release the physical space occupied by the table, and there is a concept of high water level, so we can not use delete to partition the table.

two。 Using the method of renaming (rename) tables

(1) create a new table (such as log_new) with the same data structure as the original log table (if it is log), and create constraints, indexes and default values for specified fields.

(2) rename table log to log_YYYYMM

The problem to be noted is that the OLTP system may have a false gag prompt that the ORA-00054 resource is busy because of the DML operation, which may hinder the renaming execution successfully, which requires multiple attempts to succeed.

(3) rename the table log_new to log.

The log table is truncated without modification by the sample application (the affected time is only a few seconds).

The above steps can be implemented with stored procedures in Oracle.

Second, use stored procedures to partition tables

You can see that step (2) is the key in the method of renaming the table. The following rename_table procedure will be retried recursively 100 times in the case of lock hindrance.

Rename the stored procedure rename_table from the original table to the target table:

Create or replace procedure rename_table

(source_name in varchar2

Target_name in varchar2

Times in out number)

Is

Query_str varchar2 (4000)

Source_name1 varchar2 (64)

Target_name1 varchar2 (64)

Cursor c1 is select segment_name from user_segments

Where segment_name=upper (source_name)

Dummy c1%rowtype

Cursor c2 is select segment_name from user_segments

Where segment_name=upper (target_name)

Dummy2 c2%rowtype

Begin

Source_name1:=source_name

Target_name1:=target_name

Open c1

Fetch c1 into dummy

-- if c1%found then

-- dbms_output.put_line (source_name1 | | 'destroy')

-- end if

Open c2

Fetch c2 into dummy2

-- if c2%notfound then

-- dbms_output.put_line (target_name1 | | 'not subscription')

-- end if

If c2%notfound and c1%found then

Query_str: = 'alter table' | | source_name1 | | 'rename to'

| | target_name1 |

Execute immediate query_str

Dbms_output.put_line ('rename customers')

End if

Close c1

Close c2

Exception

WHEN OTHERS THEN

Times:=times+1

If timesalter database rename global_name to shenzhen.test.com.cn

Log in to the beijing database with system:

SQL > alter database rename global_name to beijing.test.com.cn

②, log in to shenzhen database with system

SQL > create public database link beijing.test.com.cn using 'beijing'

Test database whole urine name and public database link

SQL > select * from global_name@beijing.test.com.cn

It is correct to return the result as beijing.test.com.cn.

Log in to the beijing database with system:

SQL > create public database link shenzhen.test.com.cn using 'shenzhen'

Test database whole urine name and public database link

SQL > select * from global_name@shenzhen.test.com.cn

It is correct to return the result as shenzhen.test.com.cn.

3. Set up the user repadmin that manages database replication, and delegate the rights to users.

①, log in to shenzhen database with system

SQL > create user repadmin identified by repadmin default tablespace users temporary tablespace temp

SQL > execute dbms_defer_sys.register_propagator ('repadmin')

SQL > grant execute any procedure to repadmin

SQL > execute dbms_repcat_admin.grant_admin_any_repgroup ('repadmin')

SQL > grant comment any table to repadmin

SQL > grant lock any table to repadmin

②, also use system to log in to the beijing database, run the above command, manage the user repadmin of database replication, and authorize users.

Description: repadmin username and password can be freely named according to the needs of users.

4. Create a private database link under the user repadmin of database replication.

①, log in to shenzhen database with repadmin

SQL > create database link beijing.test.com.cn connect to repadmin identified by repadmin

The test returns a private database link:

SQL > select * from global_name@beijing.test.com.cn

It is correct to return the result as beijing.test.com.cn.

②, log in to beijing database with repadmin

SQL > create database link shenzhen.test.com.cn connect to repadmin identified by repadmin

Test to return a private database link

SQL > select * from global_name@shenzhen.test.com.cn

It is correct to return the result as shenzhen.test.com.cn.

5. Create users and objects to realize database replication by selecting users and objects, and empower users. Database objects must have primary keywords.

Suppose we use the scott user, the dept table, which is used as an example in ORACLE.

①, log in to shenzhen database with internal, and create scott user account authorization

SQL > create user scott identified by tiger default tablespace users temporary tablespace temp

SQL > grant connect, resource to scott

SQL > grant execute on sys.dbms_defer to scott

②, log in to shenzhen database with scott, and create table dept

SQL > create table dept

(deptno number (2) primary key

Dname varchar2 (14)

Loc varchar2 (13))

③, if the database object does not have a primary keyword, you can run the following SQL command to add:

SQL > alter table dept add (constraint dept_deptno_pk primary key (deptno))

④, create the sequence number of the primary keyword under the shenzhen database scott user, and the range avoids conflicts with beijing.

SQL > create sequence dept_no increment by 1 start with 1 maxvalue 44 cycle nocache

(note: maxvalue 44 can be determined according to the number of digits required by the application and the primary keyword of the table structure)

⑤, insert initialization data under shenzhen database scott user

SQL > insert into dept values (dept_no.nextval,'accounting','new york')

SQL > insert into dept values (dept_no.nextval,'research','dallas')

SQL > commit

⑥, run the above ①, ②, ③ on the beijing database

⑦, create the sequence number of the primary keyword under the beijing database scott user, and the range avoids conflicts with shenzhen.

SQL > create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache

⑧, insert initialization data under beijing database scott user

SQL > insert into dept values (dept_no.nextval,'sales','chicago')

SQL > insert into dept values (dept_no.nextval,'operations','boston')

SQL > commit

6. Create the group scott_mg to be replicated, add database objects, and generate object replication support.

①, log in to the shenzhen database with repadmin, and create the master replication group scott_mg

SQL > execute dbms_repcat.create_master_repgroup ('scott_mg')

Description: scott_mg group name can be freely named according to the needs of users.

②, add database objects to the replication group scott_mg

SQL > execute dbms_repcat.create_master_repobject (sname= > 'scott',oname= >' dept', type= > 'table',use_existing_object= > true,gname= >' scott_mg')

Parameter description:

User name of database replication implemented by sname

Database object name for database replication by oname

(the table name is within 27 bytes and the package name is within 24 bytes)

Database object categories of database replication realized by type

(supported categories: tables, indexes, synonyms, triggers, views, procedures, functions, packages, package bodies)

Use_existing_object true represents a database object that already exists with the primary replication node

Gname Primary replication Group name

③, replication support for database objects

SQL > execute dbms_repcat.generate_replication_support ('scott','dept','table')

(description: generate database triggers and packages that support dept table replication under scott users)

④, data dictionary that confirms that replicated groups and objects have been added to the database

SQL > select gname, master, status from dba_repgroup

SQL > select * from dba_repobject

7. Create a master replication node

①, log in to the shenzhen database with repadmin, and create the master replication node

SQL > execute dbms_repcat.add_master_database

(gname= > 'scott_mg',master= >' beijing.test.com.cn',use_existing_objects= > true, copy_rows= > false, propagation_mode = > 'asynchronous')

Parameter description:

Gname Primary replication Group name

Master joins another database of the primary replication node

Use_existing_object true represents a database object that already exists with the primary replication node

Copy_rows false indicates that the first replication start is consistent with the primary replication node.

Propagation_mode executes asynchronously

②, confirm that the replicated transaction queue has been added to the data dictionary of the database

SQL > select * from user_jobs

8. Change the state of the synchronization group from quiesced to normal (normal)

①, log in to the shenzhen database with repadmin, and run the following command

SQL > execute dbms_repcat.resume_master_activity ('scott_mg',false)

②, confirm that the status of the synchronization group is normal (normal)

SQL > select gname, master, status from dba_repgroup

③, if the ① command does not make the state of the synchronization group normal (normal), and there may be some paused replication, run the following command to try again (it is recommended to use it only in an emergency):

SQL > execute dbms_repcat.resume_master_activity ('scott_mg',true)

9. To create a schedule for replicating the database, we assume that we use a fixed schedule: replicate every 10 minutes.

①, log in to the shenzhen database with repadmin, and run the following command

SQL > begin

Dbms_defer_sys.schedule_push (

Destination = > 'beijing.test.com.cn'

Interval = > 'sysdate + 10 Compact 1440'

Next_date = > sysdate)

End

/

SQL > begin

Dbms_defer_sys.schedule_purge (

Next_date = > sysdate

Interval = > 'sysdate + 10 Compact 1440'

Delay_seconds = > 0

Rollback_segment = >'')

End

/

②, log in to the beijing database with repadmin, and run the following command

SQL > begin

Dbms_defer_sys.schedule_push (

Destination = > 'shenzhen.test.com.cn'

Interval = > 'sysdate + 10 / 1440'

Next_date = > sysdate)

End

/

SQL > begin

Dbms_defer_sys.schedule_purge (

Next_date = > sysdate

Interval = > 'sysdate + 10 Compact 1440'

Delay_seconds = > 0

Rollback_segment = >'')

End

/

10. Add caution to modify the records of both databases and follow up the replication process.

If you want to see the changes in the database entries immediately after adding precepts, you can find push's job_number under both repadmin users and run:

SQL > exec dbms_job.run (job_number)

Third, the handling of abnormal situations

1. Check whether the replication is working properly. You can query user_jobs under the repadmin user.

SQL > select job,this_date,next_date,what, broken from user_jobs

There are two normal states:

Idle-this_date is empty and next_date is a time value after the current time

Busy-this_date is not empty, next_date is a time value after the current time

There are also two kinds of abnormal states:

Busy deadlock-next_date is a time value before the current time

Busy deadlock-next_date is a very large time value, for example: 4001-01-01

The deadlock may be caused by a network outage.

How to unlock the deadlock:

$ps-ef | grep orale

The program number ora_snp*, that found the deadlock refresh snapshot deletes this procedure with the kill-9 command

Then type under the repadmin user SQL > operator and run the command:

SQL > exec dbms_job.run (job_number)

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.