Grant select on all tables belonging to another user

SELECT 'GRANT SELECT ON '||owner||'.'|| table_name ||' TO your_user;' FROM all_tables WHERE owner = 'your_owner';

Formatted query for database links (dba_db_links)

COL OWNER FORMAT a10
COL USERNAME FORMAT A8 HEADING "USER"
COL DB_LINK FORMAT A30
COL HOST FORMAT A7 HEADING "SERVICE"
SELECT * FROM DBA_DB_LINKS
/

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