How to join v$database with v$instance

Here is a useful query which displays basic information about the database, using v$database and v$instance.
Take the opportunity to calculate the uptime for the database by subtracting the value of startup_time from the current date.

col platform_name format a30
col open_mode format a20
col host_name format a20
col version format a10
col status format a20
col uptime format a30
col name format a10
set lines 300

SELECT D.NAME,D.PLATFORM_NAME,D.CREATED, D.OPEN_MODE,I.HOST_NAME,I.VERSION, I.ARCHIVER,I.STATUS,
   TO_DSINTERVAL( TO_CHAR(
                                    TO_TIMESTAMP(SYSDATE)-I.STARTUP_TIME
                                   )
                      ) "UPTIME", 
                      (SELECT ROUND(SUM(BYTES)/1024/1024/1024) FROM DBA_DATA_FILES) "DB size GB",  
                      ( SELECT ROUND(BYTES/1024/1024/1024) "mem GB" FROM V$SGAINFO WHERE NAME = 'Maximum SGA Size' ) "SGA max size GB"
  FROM V$DATABASE D INNER JOIN V$INSTANCE I 
  ON UPPER(D.NAME) = UPPER(I.INSTANCE_NAME);

Result may look like the following:

NAME	PLATFORM_NAME	CREATED	OPEN_MODE	HOST_NAME	VERSION	ARCHIVER	STATUS	UPTIME	DB size GB	SGA max size GB
proddb01	Linux x86 64-bit	10.12.2015 14:16:46	READ WRITE	prodserver01.mycompany.com	11.2.0.4.0	STARTED	OPEN	+00 13:59:55.000000	929	12

Generate "drop user" statement for schemas with and without objects

set lines 200
col owner format a20
col username format a30
col schema format a30


PROMPT ===================================================
PROMPT List of all schemas and number of Objects they own:
PROMPT ===================================================
select u.username "Schema", decode(count(*),1,0, count(*)) "Number of Objects"
from dba_users u left outer join dba_objects o
on u.username = o.owner
where u.username in (USER1','USER2','USER3')
group by u.username
order by username ASC;

set heading off
set trimspool on
set lines 200
set feedback off
spool drop_users.sql
select 'spool drop_users_SD4440.log' from dual;
select 'DROP USER ' || u.username
                    || DECODE(decode(count(*),1,0, count(*)),
                       '0',';'
                       , ' CASCADE;')
from dba_users u left outer join dba_objects o
on u.username = o.owner
where u.username in ('USER1','USER2','USER3')
group by u.username;
select 'exit' from dual;
exit

How to solve ORA-27102: out of memory on startup

You want to increase your SGA to a larger value, and you have taken care to increase the value of shmmax in the /etc/sysctl.conf file.

When starting up the database with increased values for sga_max_size and sga_target, you hit

SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 1543503872
Additional information: 1

Solution:
Increase the value of shmall as well. Shmall indicates the total amount of shared memory that the system can use at one time (measured in pages).

To find the page size:

getconf PAGE_SIZE
4096

Make sure that the shmall is larger, not identical, than the setting of your SGA.
For example, if you want a 12G SGA, do not set shmall to 3145728 which would be the number of pages for 12G

Set it to a value larger instead, for example 3670016, which is the number of pages equivalent to 14G:

3670016*4096=15032385536
15032385536/1024/1024/1024 = 14G

As root, open /etc/sysctl.conf in an editor.
Search for the string kernel.shmall
Change the value
Save and exit.

Then, still as root, execute

sysctl -p

Check that the new settings are in place:

/sbin/sysctl -a | grep shm

The database should now be possible to open.

How to check memory consumption and swapping on a Linux server

The easiest way to check for memory consumption on Linux is in my opinion the "free" utility:

oracle@prodserver1:[proddb01]# free -m
             total       used       free     shared    buffers     cached
Mem:         16056       9027       7029          0        233       1093
-/+ buffers/cache:       7700       8356
Swap:         8191       2674       5517

Add totals with the -t flag:

 free -t -m
             total       used       free     shared    buffers     cached
Mem:         16056       9029       7027          0        233       1093
-/+ buffers/cache:       7701       8354
Swap:         8191       2674       5517
Total:       24248      11703      12545

So in this case I am using 7701 MB of memory, while 8354 MB is free for use.

To get a quick glance, you can have a look at the file /proc/meminfo, grep for the highlights, like this:

 egrep  'Mem|Cache|Swap' /proc/meminfo
MemTotal:       16442312 kB
MemFree:         7255224 kB
Cached:          1120380 kB
SwapCached:        45632 kB
SwapTotal:       8388600 kB
SwapFree:        5650100 kB

A good option for finding out if a server is swapping, is by using vmstat:

oracle@myserver# vmstat -w 10 4
procs -----------------------memory---------------------- ---swap-- -----io---- -system-- --------cpu--------
 r  b         swpd         free         buff        cache   si   so    bi    bo   in   cs  us  sy  id  wa  st
 3  0      2772764       659396        20340     10764836    4    4    19     9    3    4  13   6  81   0   0
 6  0      2771740       735076        20364     10766444   82    0    82     7 68360 52089  57  30  13   0   0
10  0      2771484       767816        20376     10766628    8    0    12     4 67349 52155  55  31  15   0   0
 7  0      2771228       747480        22832     10768888   34    0   495    12 67799 52119  57  30  13   0   0

where * The flag -w indicates wide output * Sample every 10 second * 4 iterations Columns: * si: Amount of memory swapped in from disk (/s) * so: Amount of memory swapped to disk (/s) When a lot of negative swapping occurs the value of "so" (swap out) is increasing.

How to resolve PLS-00201: identifier 'DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION'

During migration of an EBS (E-Business Suite) database, I was left with some packages in the APPS schema that couldn't be compiled.

SQL> alter package apps.ad_parallel_updates_pkg compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY APPS.AD_PARALLEL_UPDATES_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
338/8    PL/SQL: Statement ignored
338/8    PLS-00201: identifier 'DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION'
         must be declared

I first tried granting execute on dbms_system directly to the user app - no luck.

Then I found the following blog post

https://blogs.oracle.com/mandalika/entry/resolving_pls_00201_identifier_dbms

Which points to the following solution:

SQL> create public synonym dbms_system for dbms_system;

Synonym created.

SQL> grant execute on dbms_system to apps;

Grant succeeded.

SQL> alter package apps.ad_parallel_updates_pkg compile body;

Package body altered.

SQL>

Cardinality, Selectivity, Projection and Selection - definitions

From Oracle documentation:

Cardinality

The ratio of distinct values to the number of table rows. A column with only two distinct values in a million-row table would have low cardinality

Selectivity

In a query, the measure of how many rows from a row set pass a predicate test, for example, WHERE last_name = 'Smith'. A selectivity of 0.0 means no rows, whereas a value of 1.0 means all rows. A predicate becomes more selective as the value approaches 0.0 and less selective (or more unselective) as the value approaches 1.0.

Projection

The ability of a SELECT statement to choose a *subset* of the columns in a given table.
In other words, this corresponds to the column listing in the SELECT statement.

Selection

The ability of a SELECT statement to choose a subset of rows in a given table.
In other words, selection corresponds to the WHERE clause of the statement.

Preparations to make before running dbms_redefinition on a large table

To avoid ORA-01555 "snapshot too old", you should take the following precaution before starting an online redefinition of a large table:

1. If possible, create a new BIGFILE undo tablespace:

CREATE BIGFILE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/u02/oradata/proddb01/undotbs02_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE;

2. Make the new UNDO tablespace database default:

ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;

3. Alter the retention of UNDO data to GUARANTEED retention:

ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;

4. If possible, create a new BIGFILE temporary tablespace:

CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'/u02/oradata/proddb01/temp2_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

5. Make the new temporary tablespace the database default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

6. Empty the recyclebin. As sysdba:

PURGE RECYCLEBIN;

7. Adjust the parameter undo_retention
Set the parameter high enough to sustain operation of the database for the amount of time you expect the redefintion to last.
For example, if you expect the redefinition to run for 48 hours, set the undo_retention to 172800 seconds:

alter system set undo_retention = 172800 scope=memory;

When the online redefinition has completed, return the UNDO tablespace to its normal retention:

ALTER TABLESPACE UNDOTBS2 RETENTION NOGUARANTEE;

About RETENTION GUARANTEED:

... specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

About RETENTION NOGUARANTEE:

... returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is the default

Source: Oracle Documentation

More about undo retention can be found here

Setting the minimum undo retention period