Full expdp export, generating Oracle_sid and date for .dmp and .log

expdp system/password DIRECTORY=EXP_DIR DUMPFILE=${ORACLE_SID}_`date +%Y%m%d`_EXPDP%U.DMP FULL=y LOGFILE=${ORACLE_SID}_`date +%Y%m%d`_EXPDP%U.log

Get the size of an Oracle Database

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Find a user's tables structures, ERD style almost

An easy way to get table information if you don't have tools to pull up ERD's. This will generate SQL to describe the table structures. You can include this in a script or even have it spooled out to HTML for easy reading.

select 'desc '||owner||'.'||table_name||'' from dba_tables where owner='HR’;

Turn of archive log mode in Oracle

1) Shutdown the database using Shutdown normal/immediate
2) Mount the database using startup mount
3) Issue: Alter database noarchivelog;
4) Issue: Alter database open;

Now you are in noarchivelog mode.

Optionally, you can remove all LOG_ARCHIVE_* parameters from your intialization file.

List all tables owned by a user sorted by size in Oracle

set lines 100 pages 999
col segment_name format a40
col mb format 999,999,999
select segment_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_segments
where owner like '&user'
and segment_type = 'TABLE'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/

Find all tables containing the specified column in Oracle

set pages 999 lines 100
col tab format a60
col column_name format a20
select owner || '.' || table_name as tab
, column_name
from dba_tab_columns
where column_name like upper('&col')
/

Oracle invalid objects

Find invalid objects:

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

Compile invalid objects:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Get Oracle Database growth over the last year

A useful query to determine the growth of an Oracle Database over the last year. You can modify sysdate to suit whichever period's growth you would like to determine:

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
;

Oracle jobs - status, enabling or disabling them

Check the status of Oracle jobs:

You can terminate a running job by marking the job as broken, identifying the session running the job, and disconnecting that session. You should mark the job as broken, so that Oracle does not attempt to run the job again.

After you have identified the session running the job (using V$SESSION or V$LOCK, as shown earlier), you can disconnect the session using the SQL statement ALTER SYSTEM.

Display Oracle jobs:

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;

Display all running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r.JOB = j.JOB;

Script to generate SQL to disable or enable all jobs:

Disable all jobs:
select 'exec dbms_ijob.broken('||job||',true);' from dba_jobs;

Enable all jobs:
select 'exec dbms_ijob.broken('||job||',fales);' from dba_jobs;

Script to create a new Oracle user like an existing database user

This script will create a new user with privs like an existing user, without copying the user data. Very effective for schema refreshes form dev to prod etc. It generates the SQL for you. All you need to do is type in the existing user and the new user at the prompt. You can add a spool command at the beginning to spool it to an SQL script that can be run after getting all the metadata, but I prefer to look at the output first.

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Tablespace Quotas...
select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');

-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/

Send email from Oracle 10g Server

In order to send email within 10g you must install and set up the UTL_MAIL package.

UTL_MAIL isn't installed when the database is installed because the SMTP_OUT_SERVER parameter must be configured. Listing 1 shows how to install UTL_MAIL and the results from the script. You must connect to the database as user SYS and run the two scripts identified in the listing.

Installation of UTL_MAIL

SQL> connect sys/password as sysdba
Connected.

SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

Package created.

Synonym created.

SQL> @$ORACLE_HOME /rdbms/admin/prvtmail.plb

Package body created.

No errors.

Next, the SMTP_OUT_SERVER parameter must be configured.

You must connect to SYS and then use the alter system command to configure SMTP_OUT_SERVER parameter as shown here:

SQL> alter system set smtp_out_server = 'ip-address:port' scope=Both;

System altered.

If you're not sure what your SMTP server is, try and find the information on one of your existing data:

SQL> show parameter smtp_out_server

This will show the IP address as well as the port number, which is usually port 25 by defaul.


SEND Procedure

This procedure packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients. It hides the SMTP API and exposes a one-line email facility for ease of use.

UTL_MAIL.SEND (
sender IN VARCHAR2 CHARACTER SET ANY_CS,
recipients IN VARCHAR2 CHARACTER SET ANY_CS,
cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
message IN VARCHAR2 CHARACTER SET ANY_CS,
mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
priority IN PLS_INTEGER DEFAULT NULL);

Grant privilege to user to user UTL_MAIL:

GRANT execute ON utl_mail TO user ;

Oracle Processes Chart

Quite a handy chart displaying the architecture of an Oracle database instance. Click to view full view / zoom:

Oracle autoextend

Find datafiles in Oracle database which has autoextend turned on or off:

select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
from dba_data_files where autoextensible = 'YES';

Generate a script with dynamic SQL to change autoextend to ON or OFF:

select
'alter database datafile '||
file_name||
' '||
' autoextend off;'
from
dba_data_files;


select
'alter database datafile '||
file_name||
' '||
' autoextend on;'
from
dba_data_files;

Check space and free space in an Oracle database

Probably the best script for indicating free space that I've come across:

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

Oracle sessions, blocking locks and killing sessions

Some info I gathered on Oracle sessions, blocking locks and killing sessions

ORACLE SESSION INFO:

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

BLOCKING LOCKS:

select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/

BLOCKING LOCKS

Another script to show which sessions that are blocking each other

select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
/

KILL A SESSION IN ORACLE:

ALTER SYSTEM KILL SESSION '419,5651' immediate;

Find sizes of tables in Oracle

If you want to leave out SYS, SYSTEM and SYSAUX, I've included it at the end. There is a second script at the end that you can use to specify a table.

SELECT
SUBSTR(s.segment_name,1,20) TABLE_NAME,
SUBSTR(s.tablespace_name,1,20) TABLESPACE_NAME,
ROUND(DECODE(s.extents, 1, s.initial_extent,
(s.initial_extent + (s.extents-1) * s.next_extent))/1024000,2) ALLOCATED_MB,
ROUND((t.num_rows * t.avg_row_len / 1024000),2) REQUIRED_MB
FROM
dba_segments s,
dba_tables t
WHERE
s.owner = t.owner AND
s.segment_name = t.table_name and t.tablespace_name not in ('SYS','SYSTEM','SYSAUX')
ORDER BY s.segment_name;

If you want to specify a specific table, use this query or put it in a script:

SELECT
owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
/

Dynamic SQL grants in Oracle

Dynamically grant roles. You can use / modify this script to create a role and for instance grant the select role to it:

set feedback off
set heading off

create role MY_SELECT;

spool grant_me.sql

SELECT 'GRANT SELECT ON ' ||owner||'.'|| table_name ||'
TO MY_SELECT;'
FROM dba_tables
WHERE owner = 'SOMEowner';

spool off
set feedback on

@grant_me.sql

exit

Dynamically grant for instance select rights of objects or tables:

SELECT 'GRANT SELECT ON ' ||owner||'.'|| table_name || ' to somedude;' FROM dba_tables WHERE owner = 'someowner';

SELECT 'GRANT SELECT ON ' ||owner||'.'|| object_name || ' to somedude;' FROM dba_objects WHERE owner = 'someowner';

Find a table's metadata without using Toad

Simply use this dynamic SQL:

select dbms_metadata.get_ddl('TABLE','tablename','username') from dual;