Oracle Database Day-to-day Problems & Solutions for DBA’s

Problems & Solutions

# Records has to be inserted in a table which has foreign keys and constraints.

It’s like update the table and it may have duplicate values also.

Solution

# Findout all the foreign key’s and table constraints.

(dba_constraints, dba_cons_columns)

# Disable all constraints

(ALTER TABLE table_name DISABLE CONSTRAINT constraint name;)

# If unique constraint is present, Truncate the Table.*

(Before truncating consult with developers, and team lead and read release notes if any)

(TRUNCATE TABLE schema.tablename;)

# Load the records into the table.(insertion)

# Commit;

# Enable all disabled constraints.

(ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;)

*

If unique constraints are prestnt

ORA-02298 error will come,…..parent keys are not enabled,

Findout out primary key difference in foreign table and delete the(those)records…

(DELETE FROM foreign_table WHERE column_name = 799;) and proceed

---

# Allocate space for a user fernando who’s id already exists and grant write permissions in devalert73 database.

Solution:

ALTER USER fernando QUTOA 5M ON users;

GRANT develop TO fernando; ( develop – write permission role)

---

# Next extent allocation failure for a table.

Solution

ALTER TABLE owner.table_name

STORAGE (NEXT 500K PCTINCREASE 1);

PCTINCREASE is set to 1 – SMON releases the freed extents

---

# Create a new user with create session privilage

Solustion:

. CREATE USER roysys IDENTIFIED EXTERNALLY

DEFAULT TABLESPACE users

TERMPORARY TABLESPCAE temp;

. GRANT create session TO roysys.