24/07 ?

[khoshtip_uk]

Hi,

I know that in order to perform DDL on a table or to perform some other maintenance type jobs its required no user to
be accessing the database etc...so how to big banks deal with those kind of issues?

[Bore]

That's too strong sentence. Sure u can do ddl while people works, just can't ddl objects with active dmls on them

Same about maintanence jobs
Also, banks are not the best examle. They do not work trough the nights nor do they during weekends

[slimdave]

DDL requires a lock, so it's the object that cannot be accessed, not the database. Use DBMS_Redefinition to work around this.

[khoshtip_uk]

I know in places like banks database should be available 24/7 for update/delete, means no DDL or maintenance should interrupt?
will a lock on object interrupt update/delete?

[dbasan]

For 24/7 operations you can do update/delete until it is commited its not visibly seen by all. If you are doing bulk update/delete no, don't do it during peak banking hours. Can schedule it during nights and weekends!

Lock on objects for DML depends on what lock it has acquired. Shareable would allow you to do update/delete until it is commited (as said above) cannot be seen by all. Get to know about Read consistency. If the lock is exclusive it wont allow you for update/delete until the lock on the object is released.

[Bore]

Generally it's not common to DDL against oparatable objects in production 24/7 system.
For doing absolutely necessary maintanence which requires restricted user access onen have to schedule maintanence windows. Say u schedule maintanence window each second weekend of the month. Trough the month u collect the requests of changes, prepare scripts and test them against the test environment, ask people to test how the changes affects their job ( on the test system) etc. When the maintanence window comes, u restrict the user access, apply changes, ask them to test and get back to normal work ASAP

[khoshtip_uk]

Generally it's not common to DDL against oparatable objects in production 24/7 system.
For doing absolutely necessary maintanence which requires restricted user access onen have to schedule maintanence windows. Say u schedule maintanence window each second weekend of the month. Trough the month u collect the requests of changes, prepare scripts and test them against the test environment, ask people to test how the changes affects their job ( on the test system) etc. When the maintanence window comes, u restrict the user access, apply changes, ask them to test and get back to normal work ASAP

Yes my understanding is that some maintenance jobs do require down time, but the trick is that in real life we can withdraw cash from bank 24/7, am I’m right saying they are up 365days a year? Or that even them people do bring their db down for maintenance such as object lock etc ?

[jhmartin]

If you have that kind of operational requirement, then you have to design in the capacity for maintenance. Taking ATM transactions for example -- you could have a cached copy of the account balances of some sort, and queue transactions for posting back into the database.

[khoshtip_uk]

If you have that kind of operational requirement, then you have to design in the capacity for maintenance. Taking ATM transactions for example -- you could have a cached copy of the account balances of some sort, and queue transactions for posting back into the database.

sorry I didnt get it , cache in memory ? do you mean if we cache one table's full data in memory then we will be able to alter the table? DDL lock would not cause a problem? what if user commits data while performing maintenance job.

is there anywehre I can read more about maintenance jobs in 24/7 environment?

[pando]

Dave has already told you an option, DBMS_REDEFINITION