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
Tuesday, December 22, 2015
Generate "drop user" statement for schemas with and without objects
Friday, December 18, 2015
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
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:
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:
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
Check that the new settings are in place:
The database should now be possible to open.
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:
Add totals with the -t flag:
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:
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 kBA 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 0where * 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.
Wednesday, December 16, 2015
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.
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> 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>
Sunday, December 13, 2015
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.
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.
Friday, December 11, 2015
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
Friday, December 4, 2015
How to enable OLAP in an Oracle 11gR2 database
First, check that the prerequisites are fulfilled. The components below must be installed and valid before proceeding.
If java virtual machine is not installed:
If XML DB is not yet install, you need to follow "Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)" from Oracle Support
If Oracle Expression Filter is not installed:
1. Start by shutting down the database:
For Oracle 11R2 and above, use the chopt utility. It was provided to help DBAs add new options and implicitly relink the binaries.
For versions prior to 11gR2:
3. Open your database:
4. Set up OLAP
5. Verify that OLAP was installed:
select comp_id, comp_name, version,status from dba_registry where comp_id in ('XDB','XML','JAVAVM','EXF');
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- ----------
XDB Oracle XML Database 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
If java virtual machine is not installed:
sqlplus / as sysdba set echo on spool JServer.log @?/javavm/install/initjvm.sql; @?/xdk/admin/initxml.sql; @?/xdk/admin/xmlja.sql; @?/rdbms/admin/catjava.sql; spool off
If XML DB is not yet install, you need to follow "Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)" from Oracle Support
If Oracle Expression Filter is not installed:
sqlplus / as sysdba @?/rdbms/admin/catexf.sql
1. Start by shutting down the database:
sqlplus / as sysdba shutdown immediate2. Prepare your ORACLE_HOME by relinking the "oracle" executable.
For Oracle 11R2 and above, use the chopt utility. It was provided to help DBAs add new options and implicitly relink the binaries.
chopt enable olap Writing to /u01/oracle/product/11204/install/enable_olap.log... /usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk olap_on ORACLE_HOME=/u01/oracle/product/11204 /usr/bin/make -f /u01/oracle/product/11204/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/oracle/product/11204
For versions prior to 11gR2:
cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk olap_on make -f ins_rdbms.mk ioracle
3. Open your database:
sqlplus / as sysdba startup
4. Set up OLAP
cd $ORACLE_HOME/olap/admin sqlplus / as sysdba spool install_olap.log @olap.sql SYSAUX TEMP
5. Verify that OLAP was installed:
select * from v$option where parameter='OLAP'; PARAMETER VALUE ------------------------------ ------------------------------ OLAP TRUE
Subscribe to:
Posts (Atom)