Database Stuff: 2009

Oracle: Initialization Parameters (10g)

Initialization parameters for Oracle 10g can be obtained from v$parameter;

sample script to get values:
set pagesize 1000

set linesize 150
column name format a40
column value format a40
column description format a40

select name, value, description from v$parameter order by name;

useful links:
http://www.dba-oracle.com/t_oracle10g_initialization_parms.htm

Oracle: Commonly Used System Tables

Below is an alphabetical listing of commonly used Oracle system tables.

System Table	Description
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT_VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

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