This error occurs when an expression is divided by zero. It is considered a logical error in Oracle. In mathematics, division by zero is division where the divisor (denominator) is zero. Such a division can be formally expressed as a/0 where a is the dividend (numerator). Whether this expression can be assigned a well-defined value … Continue reading ORA-01476: divisor is equal to zero
SQL
Install Oracle Apex in 11gR2
Source --> http://oraexplorer.com/2007/11/oracle-apex-in-11g-installation/ Today I installed Oracle 11g (11.1.0.6) on my machine. I did not realize that Oracle APEX is a part of the standard database components. So after the 11g installation, I just follow simple steps (shown later below) for the post-installation. In order to access the APEX application, either the embedded PL/SQL gateway … Continue reading Install Oracle Apex in 11gR2
Scripts related to Tables/Indexes Oracle
Here are some scripts related to Tables/Indexes . Tabs w/ Questionable Inds TABLES WITH QUESTIONABLE INDEX(ES) NOTES: Owner - Owner of the table Table Name - Name of the table Column - Name of the column in question The above query shows all tables that have more than one index with the same leading column. … Continue reading Scripts related to Tables/Indexes Oracle
Backup Controlfile to Trace : Automate using Triggers
PL/SQL Procedure to Automatically Backup ControlFile to trace whenever you are shutting down or starting up the database. 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE' command at database startup or shutdown. For DBA's requiring to take regular backup copies of the controlfile.It is highly recommended to systematically keep a backup of the controlfiles as soon as … Continue reading Backup Controlfile to Trace : Automate using Triggers
How to drop all indexes in a SCHEMA : Oracle 10g
How to drop all indexes in a SCHEMA --Login as the schema user whose indexes you want to drop SQL>set pages 10000 SQL>set wrap off SQL>set heading off SQL>spool /u2/scripts/idxswx.txt SQL> select ' drop index ' || index_name ||';' from user_indexes; drop index IND1; drop index IND2; drop index IND3; SQL> spool off; -- This … Continue reading How to drop all indexes in a SCHEMA : Oracle 10g
Proof that count(*) is faster than count(1)
The final answer to the age old debate of count(*) vs count(1).Absolute proof that count(*) is faster than count(1). sql->alter session set events '10046 trace name context forever, level 12'; Session altered. Elapsed: 00:00:00.10 sql->select count(1) from dual; COUNT(1) ---------- 1 1 row selected. Elapsed: 00:00:00.40 sql->select count(*) from dual; COUNT(*) ---------- 1 1 row … Continue reading Proof that count(*) is faster than count(1)
SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora
Purpose of this parameter found in sqlnet.ora file: Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods. Default None Values Authentication Methods Available with Oracle Net Services: * none for … Continue reading SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora
Use datapump(impdp) to import a table : Oracle 10g
----- Procedure to import table from production system to test system ------ 1. Copy the backup(dump) of schema onto /backup_restor_only $ gzip -d <name of the file> 2. After this gzping finished logon to SQL*PLUS and type below commands SQL> create or replace directory import_dir as '/backup_restor_only'; SQL> grant read,write on DIRECTORY import_dir to public; … Continue reading Use datapump(impdp) to import a table : Oracle 10g
SQL command to find size of Oracle database
The size of the database is the total size of the datafiles that make up the tablespaces of the database. These details are found in the dba_extents view. type the following lines at the SQL*PLUS prompt: ######## datafiles size ########## SQL> select sum(bytes/1024/1024/1024) from dba_data_files; ######## actual database size ######### SQL> select sum(bytes/1024/1024/1024) from dba_segments; … Continue reading SQL command to find size of Oracle database
Creating Table from another Table : Oracle
Copy Table to a new table with some condition ______________________________________________ SQL> CREATE TABLE newTable AS (SELECT * FROM emp WHERE sal > 2000); CREATE TABLE testholder AS (SELECT * FROM histholder WHERE cardholder=325073128); Copying table structure not data _______________________________ SQL> CREATE TABLE My_myTables AS SELECT * FROM myTable WHERE 1=0; CREATE TABLE testholder AS SELECT … Continue reading Creating Table from another Table : Oracle
EasyOraDBA | Shadab Mohammad 