Kill sessions for a certain schema / user

select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = ''

Dynamic SQL to drop tables inside a schema

select 'drop table '||table_name||' cascade constraints;' from dba_tables WHERE owner = 'some_owner';

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')
;