Google Looker Oracle Connection
Connect to Oracle database as System
or sys
user as
sysdba
. For the following instructions, let us assume that the Campaign Schema name is
CAMPAIGN_1211.
GRANT CREATE SESSION TO CAMPAIGN_1211;
To ensure CAMPAIGN_1211 can see all tables
EXEC DBMS_STATS.GATHER_DATABASE_STATS;
Setting up main database objects
CREATE OR REPLACE VIEW LOOKER_SQL
AS
SELECT
sql.SQL_ID,
sql.SQL_TEXT
FROM
V$SQL sql,
v$session sess
WHERE
sess.SQL_ADDRESS = sql.ADDRESS AND
sess.username='CAMPAIGN_1211';
CREATE OR REPLACE SYNONYM CAMPAIGN_1211.LOOKER_SQL FOR LOOKER_SQL;
GRANT SELECT ON CAMPAIGN_1211.LOOKER_SQL TO CAMPAIGN_1211;
-- Pay special attention to the comments below:
-- the following view will be different for clustered Oracle deployments
CREATE OR REPLACE VIEW LOOKER_SESSION
AS
SELECT
SID,
USERNAME,
TYPE,
STATUS,
SQL_ID,
-- If using a single node Oracle deployment
"SERIAL#",
-- If using a clustered Oracle deployment, like Oracle Real Application Clusters
-- (SERIAL# || ',' || INST_ID) AS "SERIAL#",
AUDSID
-- If using a single node Oracle deployment
FROM V$SESSION
-- If using a clustered Oracle deployment, like Oracle Real Application Clusters
-- FROM GV$SESSION
WHERE
USERNAME='CAMPAIGN_1211';
CREATE OR REPLACE SYNONYM CAMPAIGN_1211.LOOKER_SESSION FOR LOOKER_SESSION;
GRANT SELECT ON CAMPAIGN_1211.LOOKER_SESSION TO CAMPAIGN_1211;
Setting up symmetric aggregates
CREATE OR REPLACE FUNCTION CAMPAIGN_1211_HASH(bytes raw, prec number)
RETURN raw AS
BEGIN
return(dbms_crypto.HASH(bytes, prec));
END;
/
CREATE OR REPLACE SYNONYM CAMPAIGN_1211.CAMPAIGN_1211_HASH FOR CAMPAIGN_1211_HASH;
GRANT EXECUTE ON CAMPAIGN_1211.CAMPAIGN_1211_HASH TO CAMPAIGN_1211;
GRANT EXECUTE ON SYS.CAMPAIGN_1211_HASH TO CAMPAIGN_1211;
Setting up persistent derived tables
GRANT UNLIMITED TABLESPACE TO CAMPAIGN_1211;
GRANT CREATE TABLE TO CAMPAIGN_1211;
Setting up query killing
CREATE OR REPLACE PROCEDURE LOOKER_KILL_QUERY(p_sid in varchar2,
p_serial# in varchar2)
IS
cursor_name pls_integer default dbms_sql.open_cursor;
ignore pls_integer;
BEGIN
SELECT
COUNT(*) INTO ignore
-- If using a single node Oracle deployment
FROM V$SESSION
-- If using a clustered Oracle deployment, like Oracle Real Application Clusters
-- FROM GV$SESSION
WHERE
username = USER
AND sid = p_sid
-- If using a single node Oracle deployment
AND serial# = p_serial#;
-- If using a clustered Oracle deployment, like Oracle Real Application Clusters
-- AND (SERIAL# || ',' || INST_ID) = p_serial#;
IF (ignore = 1)
THEN
dbms_sql.parse(cursor_name,
'ALTER SYSTEM KILL SESSION '''
|| p_sid || ',' || p_serial# || '''',
dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
ELSE
raise_application_error(-20001,
'You do not own session ''' ||
p_sid || ',' || p_serial# ||
'''');
END IF;
END;
/
CREATE OR REPLACE SYNONYM CAMPAIGN_1211.LOOKER_KILL_QUERY FOR SYS.LOOKER_KILL_QUERY;
GRANT EXECUTE ON SYS.LOOKER_KILL_QUERY TO CAMPAIGN_1211;