Archive for December, 2010

Oracle – get DDL of a user’s schema

December 22, 2010 Leave a comment
SPOOL schema1_ddl.sql
SELECT DBMS_METADATA.GET_DDL(owner,object_name,object_type)
FROM dba_objects WHERE owner=’SCHEMA1’;
Categories: Oracle

How to tell what OS block size you are using in RHEL (Redhat Enteterprise Linux)

December 10, 2010 1 comment

Run the following command to list attributes of your file system:

#tune2fs -l /dev/vgxx/lvolx

Sample output below:

# tune2fs -l /dev/emcpowera1
tune2fs 1.39 (29-May-2006)
Filesystem volume name:   <none>
Last mounted on:          <not available>
Filesystem UUID:          93c46938-317f-48d9-af94-1339b53c9fcb
Filesystem magic number:  0xEF53
Filesystem revision #:    1 (dynamic)
Filesystem features:      has_journal resize_inode dir_index filetype needs_recovery sparse_super large_file
Default mount options:    (none)
Filesystem state:         clean
Errors behavior:          Continue
Filesystem OS type:       Linux
Inode count:              15728640
Block count:              31457270
Reserved block count:     1572863
Free blocks:              30915647
Free inodes:              15728629
First block:              0
Block size:               4096
Fragment size:            4096
Reserved GDT blocks:      1016
Blocks per group:         32768
Fragments per group:      32768
Inodes per group:         16384
Inode blocks per group:   512
Filesystem created:       Tue Mar 23 23:28:18 2010
Last mount time:          Tue Mar 23 23:29:06 2010
Last write time:          Tue Mar 23 23:29:06 2010
Mount count:              1
Maximum mount count:      31
Last checked:             Tue Mar 23 23:28:18 2010
Check interval:           15552000 (6 months)
Next check after:         Sun Sep 19 23:28:18 2010
Reserved blocks uid:      0 (user root)
Reserved blocks gid:      0 (group root)
First inode:              11
Inode size:               128
Journal inode:            8
Default directory hash:   tea
Directory Hash Seed:      aa2c3208-4edf-4dee-b978-ab6c9a1e88fc
Journal backup:           inode blocks

Categories: Linux, Redhat

Generating statspack quickly for Oracle

December 3, 2010 Leave a comment

This process has been tested from 9i to 11gR2.  Oracle still supports statspack, but preference is to use AWR — an  additional license is required to use, however.

Note the 2 files named:
– spreport_auto.sql
– DayReports.sql

Running spreport_auto.sql will generate a new DayReports.sql.

For example, if today is the 10th day of the month and you want statspack from 2 days ago (8th), then
you will modify this line in spreport_auto.sql:
## from
where trunc(snap_time) = trunc(sysdate -1)

## to
where trunc(snap_time) = trunc(sysdate -2)

Save the file.

sqlplus perfstat/perfstat @spreport_auto.sql
sqlplus perfstat/perfstat @DayReports.sql

Note, the statspack reports generated as sp_MMDDHHHH.txt in the CWD.

Contents of spreport_auto.sql:

spool DayReports.sql

set heading off pages 9999

select ‘define begin_snap=’||snap_id||’;’||chr(10)||
‘define end_snap=’||to_number(snap_id + 1)||’;’||chr(10)||
‘define report_name=sp_’||to_char(snap_time,’mmddhh24mi’)||’.txt’||chr(10)||
from stats$snapshot
where trunc(snap_time) = trunc(sysdate -1)
order by snap_time asc;

spool off

Categories: Oracle