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

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;