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

This article will give the matters needing attention in the process of using Oracle temporary table, and verify these characteristics.

① temporary tables do not support materialized views

② can create indexes on temporary tables

③ can create views based on temporary tables

The ④ temporary table structure can be exported, but the content cannot be exported

⑤ temporary tables are usually created in the user's temporary table space, and different users can have their own independent temporary table space

⑥ different session cannot access each other's temporary table data

⑦ temporary table data will not be locked by DML (Data Manipulation Language)

1. Temporary tables do not support materialized views

1) Environmental preparation

(1) create a session-based temporary table

Sec@ora10g > create global temporary table t_temp_session (x int) on commit preserve rows

Table created.

Sec@ora10g > col TABLE_NAME for A30

Sec@ora10g > col TEMPORARY for A10

Sec@ora10g > select TABLE_NAME,TEMPORARY from user_tables where table_name = 'Thoughtemps session'

TABLE_NAME TEMPORARY

T_TEMP_SESSION Y

(2) initialize two pieces of data

Sec@ora10g > insert into t_temp_session values (1)

1 row created.

Sec@ora10g > insert into t_temp_session values (2)

1 row created.

Sec@ora10g > commit

Commit complete.

Sec@ora10g > select * from t_temp_session

X

-

one

two

(3) add a primary key to the temporary table T_TEMP_SESSION

Sec@ora10g > alter table T_TEMP_SESSION add constraint PK_T_TEMP_SESSION primary key (x)

Table altered.

2) create materialized views on the temporary table T_TEMP_SESSION

(1) create materialized view log

Sec@ora10g > create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values

Create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values

*

ERROR at line 1:

ORA-14451: unsupported feature with temporary table

As you can see, when you create a materialized view, you are prompted that a materialized view log cannot be created on the temporary table.

(2) create a materialized view

Sec@ora10g > create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION

Create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION

*

ERROR at line 1:

ORA-23413: table "SEC". "T_TEMP_SESSION" does not have a materialized view log

Because the materialized view log is not created successfully, it is obvious that the materialized view cannot be created either.

two。 Create an index on a temporary table

Sec@ora10g > create index i_t_temp_session on t_temp_session (x)

Index created.

Index on temporary table created successfully.

3. Create a view based on a temporary table

Sec@ora10g > create view v_t_temp_session as select * from t_temp_session where x select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage

USERNAME TABLESPACE SID SQLADDR SQLHASH SEGTYPE EXTENTS BLOCKS

--

SEC TEMP 370 389AEC58 1029988163 DATA 1 128

SEC TEMP 370 389AEC58 1029988163 INDEX 1 128

It can be seen that temporary tables created in SEC users and the indexes on them are stored in the TEMP temporary tablespace.

When you create a user, you can specify the user's default temporary tablespace so that different users can use their own temporary tablespaces when creating temporary tables without interfering with each other.

6. Different session cannot access each other's temporary table data.

1) View temporary table data in the first session

Sec@ora10g > select * from t_temp_session

X

-

one

two

This data is the data inserted when the environment is initialized.

2) Open a session separately to view the temporary table data.

Ora10g@secdb / home/oracle$ sqlplus sec/sec

SQL*Plus: Release 10.2.0.1.0-Production on Wed Jun 29 22:30:05 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production

With the Partitioning, OLAP and Data Mining options

Sec@ora10g > select * from t_temp_session

No rows selected

It shows that different session has its own characteristics of temporary table operation, and different session can not access each other's data.

7. Temporary table data will not be locked by DML (Data Manipulation Language)

1) View the lock information of SEC users in the new session

Sec@ora10g > col username for A8

Sec@ora10g > select

2 b.username

3 a.sid

4 b.serial#

5 a.type "lock type"

6 a.id1

7 a.id2

8 a.lmode

9 from v$lock a, v$session b

10 where a.sid=b.sid and b.username = 'SEC'

11 order by username,a.sid,serial#,a.type

No rows selected

There is no lock information.

2) insert data into the temporary table to view lock information

(1) insert data

Sec@ora10g > insert into t_temp_session values (1)

1 row created.

(2) View lock information

Sec@ora10g > select

2 b.username

3 a.sid

4 b.serial#

5 a.type "lock type"

6 a.id1

7 a.id2

8 a.lmode

9 from v$lock a, v$session b

10 where a.sid=b.sid and b.username = 'SEC'

11 order by username,a.sid,serial#,a.type

Lock lock

USERNAME SID SERIAL# type id1 id2 mode

SEC 142 425 TO 12125 1 3

SEC 142 425 TX 65554 446 6

The TO and TX type locks appear.

(3) check the lock information again after submitting the data

Sec@ora10g > commit

Commit complete.

Sec@ora10g > select

2 b.username

3 a.sid

4 b.serial#

5 a.type "lock type"

6 a.id1

7 a.id2

8 a.lmode

9 from v$lock a, v$session b

10 where a.sid=b.sid and b.username = 'SEC'

11 order by username,a.sid,serial#,a.type

Lock lock

USERNAME SID SERIAL# type id1 id2 mode

SEC 142 425 TO 12125 1 3

The firm TX was released. The TO lock is reserved.

3) Lock information changes under the test update data scenario

(1) Update temporary table data

Sec@ora10g > update t_temp_session set Xbox 100

1 row updated.

(2) the lock information is as follows

Lock lock

USERNAME SID SERIAL# type id1 id2 mode

SEC 142 425 TO 12125 1 3

SEC 142 425 TX 524317 464 6

(3) submit data

Sec@ora10g > commit

Commit complete.

(4) Lock information

Lock lock

USERNAME SID SERIAL# type id1 id2 mode

SEC 142 425 TO 12125 1 3

4) Lock information changes in the test delete data scenario

(1) Delete temporary table data

Sec@ora10g > delete from t_temp_session

1 row deleted.

(2) View lock information

Lock lock

USERNAME SID SERIAL# type id1 id2 mode

SEC 142 425 TO 12125 1 3

SEC 142 425 TX 327713 462 6

(3) submit data

Sec@ora10g > commit

Commit complete.

(4) Lock information

Lock lock

USERNAME SID SERIAL# type id1 id2 mode

SEC 142 425 TO 12125 1 3

5) Summary

DML operations such as additions, deletions and modifications on temporary tables will result in to locks and TX transactions. The TO lock persists from the time the data is inserted.

However, DML's TM-level locks are not generated throughout the process.

8. Summary

This paper introduces the common problems and characteristics in the use of temporary table. Temporary tables, as database objects of Oracle, will greatly improve system performance if they can be used on the basis of understanding these features.

Good luck.

Secooler

11.06.29

-- The End--

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.