Monthly Archives: August 2015

find tables without PK constraint


SELECT table_name FROM all_tables WHERE owner = ‘&OWNER’ MINUS SELECT table_name FROM all_constraints WHERE owner = ‘&&OWNER’ AND constraint_type = ‘P’; Script: To find out which primary keys are disabled SELECT owner, constraint_name, table_name, status FROM all_constraints WHERE owner = ‘&OWNER’ AND status = ‘DISABLED’ AND constraint_type = ‘P’;

Migrating 10g database to ASM


Check status Block change tracking.If not disabled then, disable using this command. SQL> select * from v$block_change_tracking; STATUS ———- FILENAME ——————————————————————————– BYTES ———- DISABLED SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; Database altered. SQL> Shutdown Database SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise […]

move datafile


sqlplus / as sysdba; shut imemdiate exit mv /u01/app/oracle/oradata/PREAZKKDB/system01.dbf /data/PREAZKKDB/datafile/system01.dbf mv /u01/app/oracle/oradata/PREAZKKDB/sysaux01.dbf /data/PREAZKKDB/datafile/sysaux01.dbf mv /u01/app/oracle/oradata/PREAZKKDB/undotbs01.dbf /data/PREAZKKDB/datafile/undotbs01.dbf mv /u01/app/oracle/oradata/PREAZKKDB/users01.dbf /data/PREAZKKDB/datafile/users01.dbf mv /u01/app/oracle/oradata/PREAZKKDB/temp01.dbf /data/PREAZKKDB/datafile/temp01.dbf sqlplus / as sysdba; startup mount alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/system01.dbf’ to ‘/data/PREAZKKDB/datafile/system01.dbf’; alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/sysaux01.dbf’ to ‘/data/PREAZKKDB/datafile/sysaux01.dbf’; alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/undotbs01.dbf’ to ‘/data/PREAZKKDB/datafile/undotbs01.dbf’; alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/users01.dbf’ to […]