The solution is dbms_redefinition package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.
What you have to do is:
- Create the interim table withe correct column definition
- Run redefinition
- Drop the old table
Use dbms_redefinition in the following way:
-- DETERMINE IF THE ORIGINAL TABLE CAN BE REDEFINED ONLINE
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','YOURTABLE', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- BEGIN THE REDEFINITION
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
UNAME => 'SCHEMA',
ORIG_TABLE => 'YOURTABLE',
INT_TABLE => 'INTERIM_YOURTABLE'
OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/
-- IF THE TABLE HAD DEPENDENCIES (INDEXES ... CONSTRAINTS ... TRIGGERS)
-- THIS WOULD BE THE POINT AT WHICH THEY WOULD HAVE BEEN COPIED
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA', 'YOURTABLE', 'INTERIM_YOURTABLE',
dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
-- FINISH THE REDEFINITION
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','YOURTABLE','INTERIM_YOURTABLE');
If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:
- Execute privilege to DBMS_REDEFINITION
- Create any table
- Alter any table
- Drop any table
- Lock any table
- Select any table
Tables with the following characteristics cannot be redefined online:
- [9.0.1]Tables with no primary keys
- Tables that have materialized view logs defined on them
- [9i] Tables that are materialized view container tables and AQ tables
- [10g] Tables that are replicated in an n-way master configuration can be redefined, but horizontal subsetting (subset of rows in the table), vertical subsetting (subset of columns in the table), and column transformations are not allowed.
- The overflow table of an IOT table
- Tables with fine-grained access control (row-level security)
- Tables with BFILE columns
- Tables with LONG columns can be redefined online, but those columns must be converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS. Tables with LOB columns are acceptable.
- Tables in the SYS and SYSTEM schema
- Temporary tables
Other restrictions:
- A subset of rows in the table
- Only simple deterministic expressions, sequences, and SYSDATE can be used when mapping the columns in the interim table to those of the original table. For example, subqueries are not allowed.
- If new columns are being added with no column mappings, then they must not be declared NOT NULL until the redefinition is complete.
- There cannot be any referential constraints between the table being redefined and the interim table.
- Table redefinition cannot be done NOLOGGING.
- [10g] For materialized view logs and queue tables, online redefinition is restricted to changes in physical properties.
- You cannot convert a nested table to a VARRAY.
The account that your application server uses to connect to the database should not lock out under any circumstances. It's a very simple security issue - it makes for the easiest denial-of-service attack in the world. Send n db login requests with random password -> application can't connect. You should definitely set this to unlimited.
(You should also talk to the manager of the DBA if he is really being so willfully unhelpful, but that's a non-technical issue that I don't think Stack Exchange can help you with. :D )
Best Answer
Provided audit trail is turned on, then I prefer to use the following to help track down login failures (which is usually the cause of locked accounts):
returncode is the ORA- error that would be returned from the database: 1017 is "invalid usercode or password" and 28000 is "account is locked".
This view provides valuable information such as the time the login failures occurred, the computer where the error originated from, as well as the OS user.
If you do not have audit_trail turned on, which you can verify with:
then obviously, the above query will not help with past login failures, but you may want to consider turning it on to help with future failures. Also, keep in mind that this change requires a bounce of the database to go into effect.
You can refer to this document in order to set this environment variable to a value that is appropriate to your environment: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams017.htm
You would set this parameter as follows:
Keep in mind that if you do turn on audit_trail, consider periodically clean out older records out of the audit table or the table can grow to be pretty unwieldy. There is an informative blog that talks about that here: http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/
As for another possible cause:
If you are running in a distributed environment (multiple databases connected by database links), then proxy database links (null user/password) could be your problem since such database links attempt to login to the destination database with the credentials that are passed from the source database.
If the credentials are different between the source and the destination, ORA-1017 is returned which counts against the login attempts on the destination DB (provided the usercode was the same, and just the passwords are different).
In addition, if the source DB user is logged in via a proxy user, then proxy DB links will ALWAYS fail with ORA-1017 regardless of whether or not the passwords match.