• When spooling output of a SQL query returning more than 50000 rows, Oracle automatically add another header section in the spool file after 50000 rows. How can I get rid of this extra header section?
    Post By: admin

When spooling output of a SQL query returning more than 50000 rows, Oracle automatically add another header section in the spool file after 50000 rows. How can I get rid of this extra header section?

This is hard limit set by the SQL*Plus client but you can try the following workaround. Add following commands into the start of your db script.

set pages 0
set newpage none
set emb on

  • How to Verify Database Original Creation Wordsize?
    Post By: admin

Execute the following SQL statement against the target database as sysdba:

select decode(instr(metadata,'B023'),0,'64bit Database','32bit Database') "DB Creation" from kopm$;

  • How to enable trace for another session using DBMS_SUPPORT package?
    Post By: admin

If we want to enable trace for a running session from a different session, dbms_support package can be handy. Execute the DBMS_SUPPORT package as shown below:

exec dbms_support.start_trace_in_session(sid,serial#,true,true); 

  • How to check the hidden database parameters in Oracle databases?
    Post By: admin

Execute the following SQL statement from the target database as SYSDBA.

select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and lower(ksppinm) like ‘_%’;

 

  • How to monitor the progress of running parallel query in Oracle database?
    Post By: admin

Oracle provide the dynamic performance views that can be used to monitor the progress of parallel queries. Please refer the the article How to monitor progress of running parallel query in Oracle database?

  • How to identify the Oracle databases installed on a physical machine?
    Post By: admin

Check the oratab file, which contains list of the databases with the actual physical path to the Oracle Home. The oratab file is located under /etc directory on linux machines and under /var/opt/oracle directory on other major UNIX machines. You can also check the inventory.xml file under oraInventory/ContentsXML folder if the inventory is correct and valid. If you want to identify the Oracle database instances running on the machine then run the 'ps -ef | grep pmon' command on the shell command prompt on the machine.

oracle@node1:~ ps -ef | grep pmon 
oracle 24645 1 0 Jul29 ? 00:00:21 ora_pmon_ORCL1 
oracle 10250 1588 0 12:34 pts/1 00:00:00 grep pmon oracle@node11:~ 

In the above sample, ORCL1 is the name of the database instance running on linux machine node1. If there is only ORCL1 database is installed on machine node1 then oratab file should look like:

ORCL1:/u01/app/oracle/product/db_1:N

  • Can I run multiple cluster databases in same Oracle Real Application Cluster?
    Post By: admin

YES, you can run multiple Oracle databases in same Oracle Real Application Cluster. You can also run multiple database instances of different cluster databases on same cluster node.

  • What is limitation of SCN numbers in Oracle RDBMS?
    Post By: admin

SCN is 6 bytes long so the maximum value is equivalent to 2^48.
One should not need to worry about running out of SCN numbers even if you were to commit 16K transactions per second, it would take more than 500 years to run out of SCN numbers.

  • How to estimate redo data that can be generated by a batch process or data load in an Oracle database?
    Post By: admin

The amount of redo data generated by a batch process or data load varies upon different factors such as DML operation is not running in nologging mode, FORCE or SUPPLMENETAL logging is enabled in the database etc.
So to estimate how much redo data, a data load or batch process can generate, one can calculate the redo data generated by executing the single transaction manually and record the ‘redo size’ statistics from V$MYSTAT view and multiply the value with the expected number of transactions in the data load or batch process. Execute the following query to display value of redo size from V$MYSTAT view.

SELECT value FROM v$mystat a, v$statname b WHERE a.statistic# = b.statistic# AND b.name=redo size

 

  • What is the meaning of DBA and RDBA in Oracle different trace files?
    Post By: admin

DBA stands for Data Block Address and RDBA stands for relative data block address.