Tag Archives: Oracle

ORA-01758 when trying to alter table

2 Replies

If you are trying to add a new Not NULL columns to a table and you get the following error:

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Since the values in the new column are null (after all, you just added the column) the new constraint can not be satisfied.

There are several option to overcome ORA-01758:

1) provide a default value for the column.

ALTER TABLE table-name ADD column-name VARCHAR2(15) DEFAULT ‘X’ NOT NULL

If you don’t want the default value you can remove it after the column will be populated with the new data.

2) Add the column without the NOT NULL constraint,

ALTER TABLE table-name ADD (column-name VARCHAR2(15));

— add the data

UPDATE table-name set column-name=’x’;

— apply the NOT NULL  constraint.

ALTER TABLE table-name MODIFY (column-name NOT NULL)

3) Empty the table, apply the NOT NULL and add the data back to the table

How to move tablespace to a new location

Leave a reply

1)    Take the tablespace offline
ALTER TABLESPACE tablespace-name OFFLINE;
2)    Use the OS to Move the tablespace to the new location
3)    Run the following command:
ALTER TABLESPACE tablespace-name RENAME DATAFILE ‘OS path to old tablespace\tablespace-datafile- name.dbf’ TO ‘OS path to new location\tablespace-datafile-name.dbf’;
4)    Take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;
If the following error is displayed
ORA-01113: file n needs media recovery
ORA-01110: data file n: ‘new location file name’
Issue the the following command:
recover datafile ‘new location file name ‘;
and take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;

Simple solution for ORA-12638: Credential retrieval failed

41 Replies

If the Oracle client was installed using the domain administrator account instead of the local administrator, the following message could be displayed when trying to connect:

ORA-12638: Credential retrieval failed

From the wording of the error message one could think that this error means that he is using the wrong password but this is not the case. To solve this:

1) Go to /oracle home/network/admin and open sqlnet.ora

2) Search for  SQLNET.AUTHENTICATION_SERVICES= (NTS) and change it to
SQLNET.AUTHENTICATION_SERVICES= (NONE)

3) Save the file and try to connect, this should work

MSVCR71.DLL is in use during Oracle patch installation

18 Replies

If you are trying to upgrade the Oracle Client and an error message is displayed telling you that  msvcr71.dll is in use, try the following:

1) Verify that all Oracle services are stopped.

2) Stop the Distributed Transaction Coordinator – In most cases this will solve the issue.

3) Run Process Explorer

a) In the Find menu, click on “Find Handle or DLL” (CTRL F)

b) Type msvcr71.dll and click on Search

c) Go through the list and close the listed applications

What to do when Enterprise Manager is not able to connect to the database instance (ORA-28001)

33 Replies

If you are trying to connect to the Oracle enterprise Manger and you get the following errors:

Enterprise Manager is not able to connect to the database instance.

And in the “Agent connection to instance” section:

Status: Failed
Details: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)

One of the reasons could be that the password for the SYSMAN user is expired. However, changing the password alone will not solve this issue. Several additional steps are required in order to make Oracle Enterprise Manager connect:

Before you start: Verify that ORACLE_HOME, ORACLE_SID environment variables are set. – If not, set them using as  environment variables or open a command line and type SET ORACLE_SID=<The database SID>

1. Stop the dbconsole: emctl stop dbconsole (dos and Unix) or  using the windows services stop the OrcleDBConsole<The database SID>.

2. Connect to the database as a user with DBA privilege with SQL*Plus
and run the following command:

alter user sysman identified by <the new password> ;

3. Verify that the new password works
SQL> connect sysman/<the new password>

4. Go to ORACLE_HOME/<HostName_SID>/sysman/config and save a backup of the emoms.properties file.

a) Open the file emoms.properties and search for:
oracle.sysman.eml.mntr.emdRepPwd=<some encrypted value>

Replace the encrypted value with the new password value

b) Search for oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
and change TRUE to FALSE

c) Save and close emoms.properties

5. Restart the dbconsole:

emctl start dbconsole (dos and Unix) or  using the windows services start the OrcleDBConsole<The database SID>.

6. Open  emoms.properties again and Search for:
a)oracle.sysman.eml.mntr.emdRepPwd=
verify  that the password is encrypted

b) oracle.sysman.eml.mntr.emdRepPwdEncrypted=
verify  that the value is set to  TRUE

7. Refresh Oracle Enterprise Manager