Feeds:
Posts
Comments

Archive for the ‘Oracle db’ Category

Are you gettinng the Unable to connect to https://localhost:1158/em? Here is the solution:

Head to your installation of Oracle and check what’s the hostname and unique name, by heading into the oracle installation folder (in my case C:\Oracle\app\product\11.2.0\db_home_1):

Go into folder oc4jc\j2ee and for a folder named something like OC4J_DBConsole_<hostname>_<unique name>, in my case this is OC4J_DBConsole_localhost_XE, so this means that the hostname is localhost and unique name is XE for my installation.

Start a windows command promt (as an admin) and enter the following (replace localhost/XE with the values you found above in the folder name):

set ORACLE_HOSTNAME=localhost
set ORACLE_UNQNAME=XE

Then, start with:

emctl start dbconsole

Advertisements

Read Full Post »

If you are making production script, you might want to make sure the script generates no errors or even during development you want to run in multiple times without it will report any errors…

This is how to update a row (and make sure it does not fail if it’s not in the database), using the EXISTS function in Oracle:

UPDATE myTableName SET myColumnName=’NewValue’ WHERE myKeyValueColumn=’KeyValue’ AND EXISTS (SELECT * FROM SYST_COLUMN_DISPLAY_NAME WHERE myKeyValueColumn=’KeyValue’);

This is how to insert a new row without generating any errors even if the row already exists:

BEGIN
INSERT INTO myTableName  (‘KeyValue’, ‘NewValue’);
EXCEPTION WHEN dup_val_on_index THEN NULL; — Ignore duplicates
END;

Read Full Post »

Today I was writing an sql script for cleaning out data from a lot of tables in an Oracle database, and wanted to figure out in which order to delete data from the tables. I found this SQL script on http://blog.mclaughlinsoftware.com/2009/03/05/validating-foreign-keys/ which helped a lot:

SELECT UC.OWNER
, UC.CONSTRAINT_NAME
, UCC1.TABLE_NAME||’.’||UCC1.COLUMN_NAME “CONSTRAINT_SOURCE”
, ‘REFERENCES’
, UCC2.TABLE_NAME||’.’||UCC2.COLUMN_NAME “REFERENCES_COLUMN”
FROM USER_CONSTRAINTS uc
, USER_CONS_COLUMNS ucc1
, USER_CONS_COLUMNS ucc2
WHERE UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
AND UC.R_CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME
AND UCC1.POSITION = UCC2.POSITION — Correction for multiple column primary keys.
AND UC.CONSTRAINT_TYPE = ‘R’
ORDER BY UCC1.TABLE_NAME
, UC.CONSTRAINT_NAME;

Read Full Post »

Well, you might get an expired login message if the user login is set to expire after a specific period of days. This could be fixed like this (I did a .bat-file for fixing this).

The .bat file’s content was a login command as well as invoking an sql file (sqlplus is run with the sys user which is not expired):

sqlplus sys/#passord# AS SYSDBA @”C:\temp\sqlfile.sql”

The sqlfile.sql’s content (this alter the password expire on user #username# to unlimited):

ALTER USER #username# IDENTIFIED BY #password#;
CREATE PROFILE my_profile LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER USER #username# PROFILE my_profile;

Read Full Post »