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

 

 

graja@dialog.com, 11 sep 1998

# 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.

 

Table record insertion

  1. findout all foreign key constraints**
  2. disable all foreign key constraints**

    3. ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

  3. findout all triggers related with the table
  4. Disable all the triggers

    5. ALTER TRIGGER schema.trigger_name DISABLE;

  5. INSERT INTO schema.table

    6. (SELECT * FROM schema.table@remote_database_name);

  6. Enable all all foreign key constraints
  7. Enable all table triggers.

Increasing the Sequence no

1.SELECT MAX(cloumn) FROM table;

2. SELECT SEQUENCE_NAME FROM DBA_SEQUENCES;

3. SELECT sequence_name.nextval FROM dual;

4. ALTER SEQUENCE sequence_name INCREMENT BY 1000;

5. SELECT sequence_name.nextval FROM dual;

If the sequence_next value is more than that of sequence_no..problem is fixed;

 

#ORA-08004: sequence SEQUENCE_NAME.nextval exceeds MAXVALUE and cannot be instantiated

1. SELECT * FROM DBA_SEQUENCES

WHERE SEQUENCE_NAME LIKE ‘%SEQUENCE_NAME%’;

2. ALTER SEQUENCE owner.sequence_name MAXVALUE 9999999;

set the max value greater than the displayed max_value

3. SELECT owner.sequence_name.NEXTVAL from DUAL; (check).

 

#After refreshing GLOBAL_NAME and database name are different

ALTER DATABASE RENAME GLOBAL_NAME TO db_name.db_domain;

graja@dialog.com 28Sep98

# A user wants to have create privilage

  1. grant corrensponding roles to him,

    2. GRANT create_privilage TO user;

  2. allocate space on default tablespace;

ALTER USER username

DEFAULT TABLESPACE users

QUOTA 10M ON users;

 

How to view all objects in a schema/database?

#SELECT OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS

WHERE OWNER LIKE ‘%SCHEMA%’;

#SELECT OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS

WHERE OBJECT_TYPE IN (‘PROCEDURE’,’FUNCTION’);

How to view the source code of a procedure?

# SELECT TEXT FROM ALL_SOURCE

WHERE NAME LIKE ‘object_name’;

#Changing default profile for a User

ALTER USER "username" PROFILE "DEFAULT" DEFAULT ROLE

"ALCHEMY_DEV","ALCHEMY_READ";

#Changing Default value as NONE or ALL

ALTER USER "TESFIVE" PROFILE "DEFAULT" DEFAULT ROLE NONE;

#Relocating Datafiles

  1. Copy the datafile to new locations(cp command)

    2. Cp old_file_location new_file_location

  2. Take the tablespace Offline

    3. ALTER TABLESPACE "USERS" OFFLINE;

  3. Rename the datafile

    4. ALTER TABLESPACE users

    RENAME ‘ole_file_location’ TO ‘new_file_location;

  4. Recover the datafile(only when it asks for media recovery)

    5. REVOVER DATAFILE ‘new-file_location’;

  5. Take the tablespce online.

ALTER TABLESPCE "USERS" ONLINE;

#Changing Database Name.

  1. Take control file backup

ALTER DATABASE BACKUP CONTROL TO TRACE;
  1. Change db_name to new name in init.ora or config.ora file;
  2. Edit the controlfile change the dbname to new name

SET DATABASE "new name" RESETLOGS
  1. startup nomount;
  2. @execite-control file script.
  3. ALTER DATABASE OPEN RESETLOGS;
  4. ALTER DATABASE RENAME GLOBAL_NAME TO new global_name;

#Relocating system Tablespace datafile.

1. Shutdown the database and start to mount state.

  1. ALTER DATABASE RENME FILE ‘system01.dbf’ TO ‘/xxx/system02.dbf’;
  2. Recover datafile ‘/xxx/system02.dbf’;
  3. ALTER DATABASE OPEN.

# Relocating Rollbacksegment Datafile.

  1. Place all ROLLBACK segments offline;

    2. ALTER ROLLBACK SEGMENT RB1 OFFLINE;

  2. Place rollback segment tablespace offline

    3. ALTER TABLESPACE "ROLLBACK" OFFLINE;

  3. ALTER TABLSPACE ROLLBACK RENAME DATAFILE ‘/rollback01.dbf" TO ‘roll01.dbf’;
  4. Place ROLLBACK tablespace online;

    5. ALTER TABLESPACE "ROLLBACK" ONLINE;

  5. Place rollback segments online;

ALTER ROLLBACK SEGMENT rb1 online;

#Rnaming redologs

1. Startup the database in mount state

2. copy all the redologs to new locations

3. ALTER DATABASE RENAME FILE ‘redo01.log’ TO ‘redo02.log’;

4. ALTER DATABASE OPEN.

 

#Rollback Segment reached 100%, and taransactions are still running

  1. ALTER ROLLBACK SEGMENT rb1 shrink;

    2. (free up space by repeating this command for all the rollback segments)

  2. See the rollback segment % used, then do coealese of rollback segment

#Unique constraint (shema.constraint) violated(ORA –0001)

Duplicate Keys exists.

 

#SCHEMA creation

#1. Create DATA, INDX tablespaces.

#2. Create schema owner(user) and assign data, indx tablespaces and unlimited quota .

#3. Create roles which has to be assigned to the schma.

#4. Grant all those roles with admin option and create session to the schema.

#5. If the schema is to refreshed from another database, export the schema,

and import into the new schema.

#6. Create all public synonyms realated to that schema.

 

Change user profiles

ALTER USER "ECARAN" PROFILE "USER_PROFILE";

Problem: Found in Alert_DB.log file

ORA-12012: error on auto execute of job 1

ORA-12008: error in snapshot refresh path

ORA-01401: inserted value too large for column

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 271

ORA-06512: at "SYS.DBMS_IREFRESH", line 416

ORA-06512: at "SYS.DBMS_REFRESH", line 171

ORA-06512: at line 1

Ans:

SQL> Select job,what from dba_jobs

Sales_reps, dbms_refresh.refresh(‘"ALCHEMY"."SALES_REPS"’);

 

SQL> select name,master from dba_snapshots

Name -> Destination object

Master -> Source object from source remote database

SQL> set long 2000;

SQL> select query from dba_snapshots where name like ‘SALES_REPS’;

You will get the definition of snapshots

SQL>

Compare the columns there will be difference in columns sizes

Fix those colums sizes either in dest, or in source.

Execute manuly the snapshot

SQl> exec dbms_refresh.refresh(‘"ALCHEMY"."SALES_REPS"’);

You must get : PL/SQL procedure executed successfully