Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to solve the problem of slow exp speed caused by too many Oracle temporary tables". In daily operation, I believe many people have doubts about how to solve the problem of slow exp speed caused by too many Oracle temporary tables. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt that "how to solve the problem of slow exp speed caused by too many Oracle temporary tables". Next, please follow the editor to study!

Problem phenomenon:

Customers report that the daily backup speed of the database is getting slower and slower.

The cause of the problem:

The size of the remote view database is only 5G, but the backup takes more than 5 hours.

-5G

SQL > Select sum (bytes) / 1024 from dba_segments where owner='CJC' 1024 from dba_segments where owner='CJC'

When performing exp backup, export the statement to create the table, export the table data, view the backup log, and start to jam before the table data is exported. The guess table data is not large, but the number of tables is so large that it gets stuck when the table statement is exported at the beginning of exp.

Finally, it is found that there are 670000 temporary tables under CJC users.

SQL > select count (*) from user_tables where temporary='Y'

COUNT (*)

-

673165

Among them, there are 620000 temporary tables starting with TEM_ and more than 40, 000 starting with TMPTABSUBJ%

SQL > select count (*) from user_tables where temporary='Y' and table_name like'TEM_%'

COUNT (*)

-

623866

SQL > select count (*) from user_tables where temporary='Y' and table_name like'TMPTABSUBJ%'

COUNT (*)

-

47899

Among them, the initial temporary tables of TEM_ are all produced in 2009-14, with an average of 10, 000 temporary tables per day, but there are no such temporary tables in 15-16 years.

SQL > select * from (select to_char (created,'yyyymmdd'), count (*)

2 from user_tables a,user_objects b

3 where a.table_name=b.object_name

4 and a. Temporary1.

5 and a.table_name like'TEM_%'

6 group by to_char (created,'yyyymmdd')

7 order by 1 desc

8)

9 where rownum select * from (

2 select to_char (created,'yyyymmdd'), count (*)

3 from user_tables a,user_objects b

4 where a.table_name=b.object_name

5 and a. Temporary1.

6 and a.table_name like'TMPTABSUBJ%'

7 group by to_char (created,'yyyymmdd')

8 order by 1 desc

9)

10 where rownum VARIABLE JOBNO NUMBER

SQL > VARIABLE INSTNO NUMBER

SQL >

SQL > BEGIN

2 SELECT INSTANCE_NUMBER INTO: INSTNO FROM V$INSTANCE

3 DBMS_JOB.SUBMIT (: JOBNO

4 'DROP_TEMPTAB;'

5 TRUNC (SYSDATE) + 1 + 3 / 24

6 'TRUNC (SYSDATE) + 2' 3ax 24'

7 TRUE

8: INSTNO)

9 COMMIT

10 END

11 /

PL/SQL procedure successfully completed

Check whether the JOB is created successfully

SQL > select * from dba_jobs

At this point, the study on "how to solve the problem of slow exp caused by too many Oracle temporary tables" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.