Buscar en el Blog

jueves, 7 de febrero de 2013

Cómo resolver problemas de rendimiento de Pentaho Schema Workbench sobre Oracle

Al intentar ejecutar Pentaho Schema Workbench (PSW) y crear una conexión a una base de datos Oracle, sucede que aparentemente el aplicativo se queda congelado. Lo que sucede es que se está trayendo todo el diccionario de los esquemas a los que tiene acceso la conexión.

Para solucionar esto sobre el esquema que queremos conectarnos es útil correr el siguiente script SQL:
CREATE OR REPLACE VIEW ALL_TABLES AS SELECT USER AS OWNER, A.* FROM SYS.USER_TABLES A;
CREATE OR REPLACE VIEW ALL_VIEWS AS SELECT USER AS OWNER, A.* FROM SYS.USER_VIEWS A
WHERE VIEW_NAME NOT IN ('ALL_CONSTRAINTS','ALL_CONS_COLUMNS','ALL_INDEXES','ALL_IND_COLUMNS',
'ALL_OBJECTS','ALL_SYNONYMS','ALL_TABLES','ALL_USERS','ALL_VIEWS','DBA_CONSTRAINTS','DBA_CONS_COLUMNS',
'DBA_INDEXES','DBA_IND_COLUMNS','DBA_OBJECTS','DBA_SYNONYMS','DBA_TABLES','DBA_USERS','DBA_VIEWS');
CREATE OR REPLACE VIEW ALL_CONSTRAINTS AS SELECT A.* FROM SYS.USER_CONSTRAINTS A WHERE CONSTRAINT_TYPE IN ('R','P','U');
CREATE OR REPLACE VIEW ALL_CONS_COLUMNS AS SELECT A.* FROM SYS.USER_CONS_COLUMNS A;
CREATE OR REPLACE VIEW ALL_INDEXES AS SELECT USER AS OWNER, A.* FROM SYS.USER_INDEXES A;
CREATE OR REPLACE VIEW ALL_IND_COLUMNS AS SELECT USER AS INDEX_OWNER, USER AS TABLE_OWNER, A.* FROM SYS.USER_IND_COLUMNS A;
CREATE OR REPLACE VIEW ALL_SYNONYMS AS SELECT USER AS OWNER, A.* FROM USER_SYNONYMS A;
CREATE OR REPLACE VIEW ALL_OBJECTS AS SELECT USER AS OWNER, A.* FROM USER_OBJECTS A;
CREATE OR REPLACE VIEW ALL_USERS AS SELECT * FROM SYS.ALL_USERS A WHERE USERNAME=USER;
CREATE OR REPLACE VIEW DBA_TABLES AS SELECT USER AS OWNER, A.* FROM SYS.USER_TABLES A;
CREATE OR REPLACE VIEW DBA_VIEWS AS SELECT USER AS OWNER, A.* FROM SYS.USER_VIEWS A
WHERE VIEW_NAME NOT IN ('ALL_CONSTRAINTS','ALL_CONS_COLUMNS','ALL_INDEXES','ALL_IND_COLUMNS',
'ALL_OBJECTS','ALL_SYNONYMS','ALL_TABLES','ALL_USERS','ALL_VIEWS','DBA_CONSTRAINTS','DBA_CONS_COLUMNS',
'DBA_INDEXES','DBA_IND_COLUMNS','DBA_OBJECTS','DBA_SYNONYMS','DBA_TABLES','DBA_USERS','DBA_VIEWS');
CREATE OR REPLACE VIEW DBA_CONSTRAINTS AS SELECT A.* FROM SYS.USER_CONSTRAINTS A WHERE CONSTRAINT_TYPE IN ('R','P','U');
CREATE OR REPLACE VIEW DBA_CONS_COLUMNS AS SELECT A.* FROM SYS.USER_CONS_COLUMNS A;
CREATE OR REPLACE VIEW DBA_INDEXES AS SELECT USER AS OWNER, A.* FROM SYS.USER_INDEXES A;
CREATE OR REPLACE VIEW DBA_IND_COLUMNS AS SELECT USER AS INDEX_OWNER, USER AS TABLE_OWNER, A.* FROM SYS.USER_IND_COLUMNS A;
CREATE OR REPLACE VIEW DBA_SYNONYMS AS SELECT USER AS OWNER, A.* FROM USER_SYNONYMS A;
CREATE OR REPLACE VIEW DBA_OBJECTS AS SELECT USER AS OWNER, A.* FROM USER_OBJECTS A;
CREATE OR REPLACE VIEW DBA_USERS AS SELECT * FROM SYS.ALL_USERS A WHERE USERNAME=USER;
CREATE OR REPLACE VIEW ALL_TAB_COLUMNS AS SELECT USER AS OWNER, A.* FROM SYS.USER_TAB_COLUMNS A;


2 comentarios:

  1. Hola Mauricio, tengo una duda,
    Al ejecutar este script y remplazar estas tablas, no afecta en ningun sentido en los otros schemas de la base de datos??

    ResponderBorrar