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
Solution:
. CREATE USER roysys IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
TERMPORARY TABLESPCAE temp;
. GRANT create session TO roysys.