Tag Archives: database edition

Useful scripts: Table list with stale statistics

This script is intended for daily use to get tables where percentage of changed records is above 10%:

SELECT DT.OWNER,
       DT.TABLE_NAME,
       ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
FROM   dba_tables dt, ALL_TAB_MODIFICATIONS atm
WHERE      DT.OWNER = ATM.TABLE_OWNER
       AND DT.TABLE_NAME = ATM.TABLE_NAME
       AND NUM_ROWS > 0
       AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
ORDER BY 3 desc

Also you can avoid system schemas by adding:

AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OSMMON','PERFSTAT')

To be or not to be?… Standard Edition or Standard Edition One?

From time to time we all meet the need to determine the edition of installed Oracle Database software.

In case of Enterprise Edition it is very simple:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

But in case of Standard Edition or Standard Edition One we can’t be sure. Previous select returns the same result for both editions:

SQL> select * from v$version;   
BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

So there is another way to determine the exact edition type installed.

We need to find install log. In Windows by default they are located under ‘C:\Program Files\Oracle\Inventory\logs’. Search for following string: ‘- Database edition’ and you’ll catch the ball:

- Disk space : required 3,29 GB available 169,15 GB
- Source location : C:\Distr\oracle\11g\win 64\database\install\..\stage\products.xml
- Install method : Typical installation
- Database edition : Standard Edition (Install database software only)
- Oracle base : C:\Tools\Oracle\product
- Software location : C:\Tools\Oracle\product\11.2.0\dbhome_1

Or:

- Disk space : required 3,29 GB available 168,75 GB
- Source location : C:\Distr\oracle\11g\win 64\database\install\..\stage\products.xml
- Install method : Typical installation
- Database edition : Standard Edition One (Install database software only)
- Oracle base : C:\Tools\Oracle\product
- Software location : C:\Tools\Oracle\product\11.2.0\db_home
%d такие блоггеры, как: