Muhammad Nadeem Chaudhry's Blog


How To Get Execution Statistics Reports on Query Statements?

Posted in Uncategorized by Muhammad Nadeem Chaudhry on March 24, 2011

If your user account has autotrace configured by the DBA, you can use the “SET AUTOTRACE ON STATISTICS” command to turn on execution statistics reports on query statements. The tutorial exercise bellow shows you a good example:

SQL> CONNECT {USERNAME}/{PASSWORD}
SQL> SET AUTOTRACE ON STATISTICS
SQL> SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
  2  FROM EMPLOYEES E, JOBS J
  3  WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

ORA-01994: GRANT failed: password file missing or disabled.

Posted in Uncategorized by Muhammad Nadeem Chaudhry on March 17, 2011

SQL> GRANT SYSDBA to SYS;
GRANT SYSDBA to SYS
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled.

Solution: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dba007.htm

Database Cloning

Posted in Uncategorized by Muhammad Nadeem Chaudhry on March 14, 2011
  • First of all I created the file hierarchy for datafiles and logfiles.
  • Created a pfile of Old Database (Test).

CREATE PFILE=’C:\Init{NEW DATABASE}.ORA’ FROM SPFILE;

  • Spool a datafile copy command from directory structure of Old Database to directory structure of New database.
  • Generate trace file for creation of control files.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

  • Modify controlfilecreation script.
    • Change REUSE with SET.
    • Remove all lines above ‘Startup mount’.
    • Remove all lines starting with ‘- -‘.
    • Remove RECOVER DATABASE and ALTER SYSTEM ARCHIVE LOG ALL;
    • Save script like C:\CreateDB.SQL.
  • Modified the init{New Database}.ora with the new datafile,logfile, dumpfile flash recovery area destinations and new SID.
  • Shutdown DB immediately.
  • Copy data & redo files to new location. Don’t copy Temp.DBF and .CTL files
  • Create service as:

oradim -NEW -SID NEWNAME -SRVC OracleServiceNEWNAME -STARTMODE manual -PFILE D:\oracle\product\10.2.0\db_1\database\initNEW DATABASE.ora.

  • Register newly created service with LISTENER.
  • Set servive varriable & connect to new database as SQLPLUS /nolog.
  • Startup nomount;

Error: ORA-01081: cannot start already-running ORACLE – shut it down first.

Solution: Shutdown other oracle services.

  • @CreateDB.sql;
  • ALTER DATABASE OPEN RESETLOGS;