I have EC2 instance with 60GB Ram, 8 CPU and over 50GB space.

Tried to create 75K empty tables with sqlplus -s / as sysdba @script.sql and it works fine up to 48K tables (they are created fine with all proper columns) but not sure why it hangs at 48K tables since memory is not filled up (less than 50%) and space is hard drive space is not filled either.
Tried 3 times on 3 different EC2 instances and it always hangs around 47K-48K tables (but not at the same number of tables each time).

Set actions to be saved to the logs: SPOOL /home/oracle/logs/sqlplus.log but in logs is just showing how table is created successfully but no errors at all and nothing different towards the end of logs.

How to understand why is this happening and how to fix it?

migrated from stackoverflow.com Jan 19 '18 at 18:28

This question came from our site for professional and enthusiast programmers.

  • Seems like a wild edge case you have. Maybe just break it into 2 scripts? Have you tried Amazon support? I doubt anyone has run into this but maybe someone has. – Jacob H Jan 19 '18 at 15:24
  • Seems like it might be an config limit? Did you try this on a non EC2 environment just to confirm it's nothing about ec2? And 75K tables seems like one-crazy-database. Are you sure about that design? Good luck. – shellter Jan 19 '18 at 15:28
  • Well table creation I got from other party so can not influence design.. Also after it gets stuck with table creations - I can still do terminal commands but Oracle seems to be stuck (shutdown immediate never finishes) and found easiest way is to restart EC2 in order to be able to reboot Oracle (shutdown immediate etc.). – Joe Jan 19 '18 at 15:34
  • I wonder if this is a weird bug. Have you tried putting in a commit and log switch every 10,000 tables or so? Another thought is that you should gather dictionary stats after every 10,000 tables created. This sounds like an ERP application like Peoplesoft or SAP. – Gandolf989 Jan 19 '18 at 21:42
  • I'm not familiar with EC2 - does it give you access to OEM (Oracle Cloud Control), the instance monitoring dashboard? If not, what are the waits in v$session? By the way, who needs 75K tables? Surely more than a few hundred represents a design failure. – William Robertson Jan 20 '18 at 10:25

Your Answer

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

Browse other questions tagged or ask your own question.