0

A few days ago while I tried perform database copy, using the database copy feature in SQL Developer, from a remote server to a local server. I got some warnings and one of them was like this:

"Error occurred executing DDL for TABLE:MASTER_DATA".

And then I clicked yes, but the result of database copy was unexpected, there were only few tables has been copied.

When I tried to see DDL from SQL section/tab on one of table, I got this kind of information:

-- Unable to render TABLE DDL for object COMPANY_DB_PROD.MASTER_DATA with DBMS_METADATA attempting internal generator.

I also got this message and I believe this message showed up because there's something wrong with DDL on my database so tables won't be created.

ORA-00942: table or view does not exist

I've never encountered this problem before and I always perform database copy every day since two years ago.

For the record before this problem occurred, I have removed old .arch files manually not by RMAN and I never using any RMAN commands. I also have removed old .xml log files, because these two type of files have made my remote server storage full.

How to trace and fix this kind of problem? Is there any corruption on my Oracle?

  • 2
    How, exactly, are you performing the copy? – Philᵀᴹ Jan 24 '17 at 9:07
  • using sql developer's database copy feature. and sorry i forget to mention both remote and local database using same version which is oracle 11g enterprise not express edition. and now i am currently using sql developer version 4.1.0.19. – user1878909 Jan 24 '17 at 9:36
  • @thatjeffsmith may help you out of this. – JSapkota Jan 25 '17 at 4:24
  • @thatjeffsmith when i tried to see DDL scripts from SQL section/tab on one of table there's some changes. i could tell these changes because i compared with my old database table DDL scripts. and how exactly DDL scripts on table generated by oracle system? is there any way to detect those changes? is it possible to regenerate DDL scripts of my tables automatically? – user1878909 Jan 25 '17 at 5:13
  • @JSapkota But thatjeffsmith won't hear you because the @ works only "If you are replying to someone else who has previously commented on the same post" or has written the post. Here is the account of that jeffsmith. You have to use one of his posts/comments if you want to notify him. – miracle173 Jan 25 '17 at 10:05
1

The problem was caused by datafile has reached its max size though. I have resolved the problem by following the answer of this discussion https://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext

Thank you everyone for the help.

0

Chances are that your table contains user defined types or is created in a tablespace for which you have no quota in your local database. You can trace the session that runs the script in your local database to find where it crashes.

An other option is to get the ddl for the problamatic table and apply that to your local database for better error messages.

To get the ddl run:

select dbms_metadata.get_ddl('TABLE','<your_table_name>','<the_remote_table_owner>') from dual;

nice blog about get_ddl

Doing so you should be able to pull your conclusion quickly.

  • How exactly the way to trace session that runs the script? Anyway, i've tried your ddl script. And I've got these messages ORA-01652: unable to extend temp segment by 128 in tablespace TEMP ORA-06512: at "SYS.DBMS_LOB", line 664 ORA-06512: at "SYS.DBMS_METADATA", line 3825 ORA-06512: at "SYS.DBMS_METADATA", line 5678 ORA-06512: at line 1 – user1878909 Jan 30 '17 at 8:52
  • I found this discussion stackoverflow.com/questions/25350703/… regarding to the "ORA-01652: unable to extend temp segment by 128 in tablespace". I've followed the answer of that discussion and i've found my system01.dbf has reached its maxbytes size. Should i set maxbytes size to unlimited? – user1878909 Jan 30 '17 at 9:04
0

Copy your Database using Oracle DataPump Export Wizard which is available on SQL Developer. Go to "View" select "DBA" then expand your DB node to select "Data Pump" then use the Export Wizard to make a copy of your DataBase.

  • I'm using sql developer on windows. When i clicked Oracle DataPump Export Wizard option it said "DBA privilege required". What i should do? I've been doing some searches on Google but i haven't found any discussion related to this "DBA privilege required". Anyway, is there any diffirence between using Database Copy and DataPump? I've been only using Database Copy. – user1878909 Jan 30 '17 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.