Database Stuff: September 2009

Oracle: List freespace by Tablespaces

Use the following script to get sizing (free,used etc) for tablespaces:

------------------------------------------------------------------------------
-- This SQL Plus script lists freespace by tablespace
------------------------------------------------------------------------------
set linesize 200
set pages 100
set verify off
set feedback off

col stat for a4 trunc head Stat
col content for a4 trunc head Type
col tablespace_name for a25 head Tablespace
col MB for 9,999,999 head "Tot|(MB)"
col free for 9,999,999 head "Free|(MB)"
col largest for 99,999,999 head "Largest |Extent (K)"
col percent for a7 head "% Free"
col extent_management for a4 trunc head "Ext.|Mng"
col allocation_type for a4 trunc head "Allc|type"
col segment_space_management for a6 trunc head "Space|Mng"
col pct_increase for 99 head "Pct |Inc."

break on report
compute sum of "free" on report
compute sum of "MB" on report

-- 100 - (NVL (t.bytes / a.bytes * 100, 0)) percent,

SELECT substr(d.status,0,3) stat , substr(d.contents,0,1) content,
   d.tablespace_name,
   NVL (a.bytes / 1024 / 1024, 0) MB,
   NVL (f.bytes / 1024 / 1024, 0) free,
   NVL (f.large / 1024, 0) largest,
   ' '||round(f.bytes/a.bytes*100,0)||'%' percent,
   d.extent_management, d.allocation_type,
   d.pct_increase, d.segment_space_management
  FROM sys.dba_tablespaces d,
   (SELECT tablespace_name, SUM(bytes) bytes
   FROM dba_data_files
   GROUP BY tablespace_name) a,
   (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) large
   FROM dba_free_space
   GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT ( d.extent_management LIKE 'LOCAL'
   AND d.contents LIKE 'TEMPORARY'
   )
UNION ALL
SELECT substr(d.status,0,3) stat, substr(d.contents,0,1) content,
   d.tablespace_name,
   NVL (a.bytes / 1024 / 1024, 0) MB,
   NVL (a.bytes - NVL (t.bytes, 0), 0) / 1024 / 1024 free,
   NVL (t.large / 1024, 0) largest,
   ' '||round((1-NVL(t.bytes,0)/a.bytes)*100,0)||'%' percent,
   d.extent_management, d.allocation_type,
   d.pct_increase, d.segment_space_management
  FROM sys.dba_tablespaces d,
   (SELECT tablespace_name, SUM(bytes) bytes
   FROM dba_temp_files
   GROUP BY tablespace_name) a,
   (SELECT tablespace_name, SUM(bytes_cached) bytes,MAX(bytes_cached) large
   FROM v$temp_extent_pool
   GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management LIKE 'LOCAL'
   AND d.contents LIKE 'TEMPORARY'
/

Oracle: SQL for displaying current processes, userids

use the followings sql for displaying current running processes, userids:

SET TERMOUT ON
SET HEADING ON

SET PAGESIZE 40
SET LINESIZE 180
SET NEWPAGE 0
SET VERIFY OFF
SET ECHO OFF
SET UNDERLINE =
SET FEEDBACK ON
SET LONG 1000
SET EMBED ON

COLUMN sid_ser FORMAT a10 HEADING ' SID/Ser'
COLUMN sid FORMAT 99999 HEADING ' SID'
COLUMN username FORMAT a6 HEADING 'Oracle|User'
COLUMN osuser FORMAT a10 TRUNC HEADING 'O/S User'
COLUMN machine FORMAT a10 HEADING 'Machine'
COLUMN program FORMAT a20 HEADING 'Program'
COLUMN F_Ground FORMAT 99999 HEADING 'F''Ground|Process'
COLUMN B_Ground FORMAT 99999 HEADING 'B''Ground|Process'
COLUMN sql_text FORMAT a45 word_wrap HEADING 'SQL Text'
COLUMN disk_reads FORMAT 99,999 HEADING 'Disk|Reads|(*1000)'
COLUMN buffer_gets FORMAT 9,999,999 HEADING 'Buffer|Gets|(*1000)'
COLUMN rows_processed FORMAT 9,999,999 HEADING 'Rows|Processed|(*1000)'
COLUMN sorts FORMAT 99,999 HEADING 'Sorts'
COLUMN executions FORMAT 9,999,999,999 HEADING 'Exctn'

TTITLE Center 'SQL Currently Executing'

SELECT /*+ ORDERED */
   s.sid || ',' || s.serial# as sid_ser,
   s.username,
   s.osuser,
   x.sql_text,
   x.disk_reads / 1000 AS disk_reads,
   x.buffer_gets / 1000 AS buffer_gets,
   x.rows_processed / 1000 AS rows_processed,
   x.sorts
   ,x.executions
   ,x.address
  FROM v$session S,
   v$process P,
   v$sql X
WHERE LOWER(s.osuser) LIKE LOWER(NVL('&os_user%', '%'))
   AND s.username LIKE UPPER(NVL('&oracle_user%', '%'))
   AND s.sid LIKE NVL('&sid', '%')
   AND s.type != 'BACKGROUND'
   AND s.sql_address = x.address
   AND s.sql_hash_value = x.hash_value
-- AND s.username NOT IN ('SYS','SYSTEM')
   AND s.paddr = p.addr
ORDER
   BY S.sid
;

Oracle: Rename Table

Oracle rename table syntax

Oracle provides a rename table syntax as follows:

alter table
table_name
rename to
new_table_name;

For example, we could rename the customer table to old_customer with this syntax:

alter table
customer
rename to
old_customer;

When you rename an Oracle table you must be aware that Oracle does not update applications (HTML-DB, PL/SQL that referenced the old table name) and PL/SQL procedures may become invalid.

Oracle: Column Statistics SQL

Get column statistics via the following sql.

change xxx to table name of interest.

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY

FROM DBA_TAB_COL_STATISTICS

WHERE TABLE_NAME ='XXXX'

ORDER BY COLUMN_NAME;

Oracle: Optimizer settings

Oracle OPTIMIZER settings

alter session set "_optimizer_cost_model" =choose;

alter session set "_optimizer_cost_model"=io;

alter session set "_optimizer_cost_model"=cpu;

Oracle: Check which user is holding temp tablespace

Check which user holding temp tablespace

SELECT s.username, s.sid, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;

Oracle: SQLplus Prompt to show user, connection (10g)

SQLplus Prompt to show user, connection (Oracle 10g)

run the following in sqlplus:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "

Display:
user@DB (privilege if exist)>

Oracle: Show current user

Show current user

Show user;

or

Select user from dual;

Oracle: Get database name using sqlplus

Get database name using this sql:

select ora_database_name from dual;

Oracle rename table syntax

Labels