Monthly Archives: August 2015

find tables without PK constraint

August 19, 2015 · by · in Script · Leave a comment

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

August 17, 2015 · by · in Oracle İntroduction · Leave a comment

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

August 11, 2015 · by · in Oracle İntroduction · Leave a comment

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