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;