This tip comes from Muthukumar Singaram, a Technical Architect at Hexaware Technologies, in Germantown, Maryland.
Miss Jansen, here what you asked for.
create or replace trigger ip_restrict
after logon on database
declare
v_user varchar2(30);
begin
select user into v_user from dual;
if v_user = 'SCOTT' then
if sys_context('USERENV','IP_ADDRESS') not in ('IP1','IP2') then
raise_application_error (-20001,'Access restricted for this IP');
end if;
end if;
end;
create or replace procedure INDEX_BUILDER (
–declare
– index_builder , by Hélio Dias at setember 05, 2006
– v1.1 (at august 15, 2007)
–Please GRANT SELECT ANY DICTIONARY TO user
–Even the user is SYSTEMproprietario varchar2 ,
tabela in varchar2 ,
p_colunas in varchar2 ,
tipo_tree_bitmap in char DEFAULT ‘T’,
P_LOCAL_INDICES VARCHAR2 DEFAULT NULL) is
type NUMERO is table of NUMBER;
TYPE PALAVRA IS TABLE OF VARCHAR2(50);
ESPACO1 NUMERO;
ESPACO2 NUMERO;
POSICAO NUMERO;
LISTA_INDICES PALAVRA;
LISTA_COLUNAS PALAVRA;
nome_indice varchar2(50);
coluna_guia varchar2(50);
SCRIPT VARCHAR2(4000);
SUFIXO VARCHAR2(50);
corte_sufixo pls_integer;
NUMERO_COLUNAS pls_integer;
C1 VARCHAR2(50);
LOCAL_INDICES VARCHAR2(50) DEFAULT P_LOCAL_INDICES;
COLUNAS VARCHAR2(4000) DEFAULT P_COLUNAS;
BEGIN
IF LOCAL_INDICES IS NULL THEN
SELECT TABLESPACE_NAME INTO LOCAL_INDICES FROM
(SELECT TABLESPACE_NAME, COUNT(*) TOT FROM SYS.DBA_INDEXES
WHERE OWNER=PROPRIETARIO
GROUP BY TABLESPACE_NAME ORDER BY 2 DESC)
WHERE ROWNUM=1;
END IF;
NUMERO_COLUNAS:= length(colunas)-length(replace(colunas,’,'))+1;
SUFIXO:=SUBSTR(TABELA,INSTR(TABELA,’_',-1,1));
nome_indice:=SUBSTR(TABELA,1,12);
if length(sufixo)>4 then
corte_sufixo:=4;
else
corte_sufixo:=length(sufixo);
end if;
nome_indice:=SUBSTR(nome_indice,1,length(nome_indice)-LENGTH(SUFIXO));
nome_indice:=nome_indice||substr(SUFIXO,1,corte_sufixo);
nome_indice:=nome_indice||’_'||TO_CHAR(NUMERO_COLUNAS);
if numero_colunas>1 then
coluna_guia:=substr(colunas,1,instr(colunas,’,')-1);
else
coluna_guia:=colunas;
end if;
–validar primeira coluna tem prefixo
if instr(coluna_guia,’_')>0 then
nome_indice:=nome_indice||substr(coluna_guia,instr(colunas,’_')+1);
else
nome_indice:=nome_indice||coluna_guia;
end if;
NOME_INDICE:=SUBSTR(NOME_INDICE,1,25);
NOME_INDICE:=SUBSTR(NOME_INDICE||dbms_obfuscation_toolkit.md5(input =>
utl_raw.cast_to_raw(TABELA||COLUNAS)),1,30);
IF tipo_tree_bitmap=’B’ THEN
SCRIPT:=’CREATE BITMAP INDEX ‘;
ELSIF tipo_tree_bitmap=’U’ THEN
SCRIPT:=’CREATE UNIQUE INDEX ‘;
ELSE
SCRIPT:=’CREATE INDEX ‘;
END IF;
SCRIPT:=SCRIPT||PROPRIETARIO||’.'||nome_indice||’ ON ‘;
SCRIPT:=SCRIPT||PROPRIETARIO||’.'||TABELA||chr(10);
SCRIPT:=SCRIPT||’(‘||COLUNAS||’) ‘||chr(10);
SCRIPT:=SCRIPT||’TABLESPACE ‘||LOCAL_INDICES||’ NOLOGGING ‘;
SCRIPT:=SCRIPT||’COMPUTE STATISTICS PARALLEL 4;’;
DBMS_OUTPUT.PUT_LINE(SCRIPT);
DBMS_OUTPUT.PUT_LINE(‘===============================================’);
DBMS_OUTPUT.PUT_LINE(‘===============================================’);
SELECT ‘FREE SPACE ON TABLESPACE: ‘||SUM(BLOCKS*8)/1024 INTO C1
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=LOCAL_INDICES;
DBMS_OUTPUT.PUT_LINE(C1);
SELECT ‘TABLE SIZE: ‘||BLOCKS*8/1024 INTO C1
FROM DBA_SEGMENTS WHERE SEGMENT_NAME=TABELA AND OWNER=PROPRIETARIO
AND SEGMENT_TYPE=’TABLE’;
DBMS_OUTPUT.PUT_LINE(C1);
SELECT (BLOCKS*8/1024),INDEX_NAME
BULK COLLECT INTO ESPACO1,LISTA_INDICES
FROM DBA_SEGMENTS T1,DBA_INDEXES T2 WHERE
T1.OWNER=PROPRIETARIO AND T2.OWNER=PROPRIETARIO
AND TABLE_NAME=TABELA
AND SEGMENT_NAME=INDEX_NAME;
IF ESPACO1 IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(‘—————–’);
DBMS_OUTPUT.PUT_LINE(‘INDEX SIZE’);
FOR C2 IN 1..ESPACO1.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE(ESPACO1(C2)||’ / ‘||LISTA_INDICES(C2));
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘——————————’);
DBMS_OUTPUT.PUT_LINE(‘INDEX AND INDEXED COLUMNS LIST’);
SELECT INDEX_NAME,COLUMN_NAME,COLUMN_POSITION
BULK COLLECT INTO LISTA_INDICES,LISTA_COLUNAS,POSICAO
FROM DBA_IND_COLUMNS
WHERE
INDEX_OWNER=PROPRIETARIO AND
INDEX_NAME IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER=PROPRIETARIO
AND TABLE_NAME=TABELA)
ORDER BY INDEX_NAME,COLUMN_POSITION;
FOR C2 IN 1..LISTA_INDICES.COUNT() LOOP
IF POSICAO(C2)=1 THEN
DBMS_OUTPUT.PUT_LINE(‘—————-’);
DBMS_OUTPUT.PUT_LINE(‘INDICE: ‘||LISTA_INDICES(C2));
END IF;
IF INSTR(COLUNAS,LISTA_COLUNAS(C2))=0 THEN
DBMS_OUTPUT.PUT_LINE (LISTA_COLUNAS(C2));
ELSE
DBMS_OUTPUT.PUT_LINE (‘******’||LISTA_COLUNAS(C2)||’******’);
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE (‘===============================================’);
DBMS_OUTPUT.PUT_LINE (‘===============================================’);
DBMS_OUTPUT.PUT_LINE (‘SELECT COUNT(*) FROM ‘||PROPRIETARIO||’.'||TABELA||’;');
DBMS_OUTPUT.PUT_LINE (‘SELECT ‘);
COLUNAS:=’,'||COLUNAS; – AJUSTE NAS COLUNAS
FOR C2 IN 1..NUMERO_COLUNAS LOOP
DBMS_OUTPUT.PUT_LINE (‘COUNT(DISTINCT ‘||
SUBSTR(COLUNAS,INSTR(COLUNAS,’,',1,C2)+1,INSTR(COLUNAS||’,',’,',1,C2+1)-2)||’)');
IF C2<NUMERO_COLUNAS THEN
DBMS_OUTPUT.PUT_LINE (‘,’);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE (‘FROM ‘||PROPRIETARIO||’.'||TABELA||’;');
END;
/
Lot of reason can make it happen, a simple one is the parameter QUERY_REWRITE_ENABLED=’TRUE’
Try change it to FALSE
SELECT * FROM (
SELECT OWNER,OBJECT_NAME,VALUE FROM V$SEGMENT_STATISTICS WHERE OWNER IN
(‘MANAGER’,'INTEGRA’) AND STATISTIC_NAME=’logical reads’ order by 3 desc)
where rownum<25
http://www.oracle.com/technology/software/products/database/index.html
Last friday my trainne Mr. Silveira started the download , i hope in this afternoon found the 11g read to install. 
I have attended the sql certified expert beta exam last week,
Was 139 questions , but the bargain is that i paid just US$ 50, and i also could comment questions that guess be wrong,
So no matter what´s happen (the result will just arrive at november 15) i happy with the chance to become OCE with the first group in the world.
I also recomend that developer do the OCE SQL to take this handicap over the market.
 | Franklin on Oracle Database 11g Release 1 … |
 | dbametrix on Oracle Unbreakable Linux |
 | Richard Troncoso on Crack Oracle Password |
 | rahul srivastava on Grouping with Rollup |
 | Luciano on OCE – Oracle Certified E… |
