davidhooey

--
-- Segment Advisor for Individual Segments
--

-- 1. Replace all occurances of [OWNER] with the owner of the segments.
-- 2. Replace [TABLE_NAME] or [INDEX_NAME] with the segment to be analyzed.

set echo off
set feedback off
set verify off

davidhooey

--
-- Segment Advisor for All Segments in Schema
--

-- 1. Grant the ADVISOR and SELECT ANY DICTIONARY roles to the schema owner.
-- 2. Execute the script as the schema owner.
-- 3. Revoke the ADVISOR and SELECT ANY DICTIONARY roles from the schema owner.

set echo off
set feedback off

davidhooey

create or replace trigger [SCHEMA_NAME]_startsqltracing after logon on [SCHEMA_NAME].schema
begin
    execute immediate 'alter session set timed_statistics=true';
    execute immediate 'alter session set max_dump_file_size=unlimited';
    execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end;
/


create or replace trigger [SCHEMA_NAME]_endsqltracing before logoff on [SCHEMA_NAME].schema

davidhooey

-- ROW MOVEMENT is required to move rows.
-- SHRINK SPACE COMPACT moves the rows without resetting the high water mark.
-- SHRINK SPACE moves the rows and resets the high water mark which temporarily locks the table.
-- May need to rebuild the indexes after a shrink.

alter table [tableName] enable row movement;
alter table [tableName] shrink space compact;
alter table [tableName] shrink space;

alter index [indexName] shrink space compact;

davidhooey

NOTE:
Oracle9i:	estimate_percent defaults to NULL which is COMPUTE
Oracle10g:	estimate_percent defaults to to_estimate_percent_type (get_param('ESTIMATE_PERCENT'))

-- ********************************
-- * Generating Schema Statistics *
-- ********************************
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree)
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO')
execute dbms_stats.gather_schema_stats(ownname=>'SCHEMAOWNER',estimate_percent=>NULL,cascade=>TRUE,degree=>dbms_stats.default_degree,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1') -- No Histograms

davidhooey

alter table [tableName] compress for oltp;
alter table [tableName] move;

davidhooey

select 
    'alter table ' || table_name || ' modify ( ' || column_name || ' varchar(' || char_length || ' char) );'
from 
    user_tab_columns 
where 
    data_type = 'VARCHAR2'
and
    char_used = 'B' 
order by 
    table_name,

davidhooey

select 
    holders.*,
    ' IS BLOCKING ' is_blocking,
    waiters.*
from
    (
        select 
            dw.holding_session,
            hs.username, 
            do.object_name, 

davidhooey

# -*- mode: ruby -*-
# vi: set ft=ruby :

# -----------------------------------------------------------------------------
#  Rails Development Vagrantfile
# -----------------------------------------------------------------------------
#
# Server Info:
#
#   Ubuntu 12.04 32-bit

davidhooey

set -g prefix C-a
set -sg escape-time 1
set -sg base-index 1
setw -g pane-base-index 1

setw -g mode-keys vi

set -g mouse on

bind r source-file ~/.tmux.conf \; display "Reloaded!"