oracle stream 详细配置
sqlplus '/ as sysdba'-- 创建LOGMNRTS表空间
DROP TABLESPACE LOGMNRTS INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE LOGMNRTS DATAFILE
'/oradata/stream/logmnrts01.dbf'>
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
-- 创建STRMADMIN用户
DROP USER STRMADMIN CASCADE;
CREATE USER STRMADMIN
IDENTIFIED BY STRMADMIN
DEFAULT TABLESPACE LOGMNRTS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 4 Roles for STRMADMIN
GRANT RESOURCE TO STRMADMIN;
GRANT CONNECT TO STRMADMIN;
GRANT AQ_ADMINISTRATOR_ROLE TO STRMADMIN;
GRANT DBA TO STRMADMIN;
ALTER USER STRMADMIN DEFAULT ROLE ALL;
-- 16 System Privileges for STRMADMIN
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'ENQUEUE_ANY',
GRANTEE => 'STRMADMIN',
ADMIN_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'DEQUEUE_ANY',
GRANTEE => 'STRMADMIN',
ADMIN_OPTION => TRUE);
END;
/
GRANT UNLIMITED TABLESPACE TO STRMADMIN;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'MANAGE_ANY',
GRANTEE => 'STRMADMIN',
ADMIN_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
GRANT RESTRICTED SESSION TO STRMADMIN;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
GRANTEE => 'STRMADMIN',
GRANT_OPTION => TRUE);
END;
/
-- 88 Object Privileges for STRMADMIN
GRANT SELECT ON SYS.AQ$INTERNET_USERS TO STRMADMIN;
GRANT SELECT ON SYS.AQ$_PROPAGATION_STATUS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_CONFLICT_COLUMNS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_DML_HANDLERS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_ENQUEUE TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_ERROR TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_EXECUTE TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_GLOBAL TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_OBJECTS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_INSTANTIATED_SCHEMAS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_KEY_COLUMNS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_PARAMETERS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_PROGRESS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_SPILL_TXN TO STRMADMIN;
GRANT SELECT ON SYS.DBA_APPLY_TABLE_COLUMNS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_AQ_AGENTS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_AQ_AGENT_PRIVS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_CAPTURE TO STRMADMIN;
GRANT SELECT ON SYS.DBA_CAPTURE_EXTRA_ATTRIBUTES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_CAPTURE_PARAMETERS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_CAPTURE_PREPARED_DATABASE TO STRMADMIN;
GRANT SELECT ON SYS.DBA_CAPTURE_PREPARED_SCHEMAS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_CAPTURE_PREPARED_TABLES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_EVALUATION_CONTEXTS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_EVALUATION_CONTEXT_TABLES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_EVALUATION_CONTEXT_VARS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_PROPAGATION TO STRMADMIN;
GRANT SELECT ON SYS.DBA_QUEUES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_QUEUE_PUBLISHERS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_QUEUE_SCHEDULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_QUEUE_SUBSCRIBERS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_QUEUE_TABLES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_REGISTERED_ARCHIVED_LOG TO STRMADMIN;
GRANT SELECT ON SYS.DBA_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_RULESETS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_RULE_SETS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_RULE_SET_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_ADD_COLUMN TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_ADMINISTRATOR TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_DELETE_COLUMN TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_GLOBAL_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_MESSAGE_CONSUMERS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_MESSAGE_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_NEWLY_SUPPORTED TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_RENAME_COLUMN TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_RENAME_SCHEMA TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_RENAME_TABLE TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_SCHEMA_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_TABLE_RULES TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_TRANSFORMATIONS TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_TRANSFORM_FUNCTION TO STRMADMIN;
GRANT SELECT ON SYS.DBA_STREAMS_UNSUPPORTED TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_APPLY_ADM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_AQ TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_AQADM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_AQELM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_AQIN TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_AQ_BQVIEW TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_CAPTURE_ADM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_PROPAGATION_ADM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_RULE_ADM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_STREAMS_ADM TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_STREAMS_MESSAGING TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_STREAMS_RPC TO STRMADMIN;
GRANT EXECUTE ON SYS.DBMS_TRANSFORM TO STRMADMIN;
GRANT SELECT ON SYS.GV_$AQ TO STRMADMIN;
GRANT SELECT ON SYS.GV_$BUFFERED_PUBLISHERS TO STRMADMIN;
GRANT SELECT ON SYS.GV_$BUFFERED_QUEUES TO STRMADMIN;
GRANT SELECT ON SYS.GV_$BUFFERED_SUBSCRIBERS TO STRMADMIN;
GRANT SELECT ON SYS.GV_$STREAMS_APPLY_COORDINATOR TO STRMADMIN;
GRANT SELECT ON SYS.GV_$STREAMS_APPLY_READER TO STRMADMIN;
GRANT SELECT ON SYS.GV_$STREAMS_APPLY_SERVER TO STRMADMIN;
GRANT SELECT ON SYS.GV_$STREAMS_CAPTURE TO STRMADMIN;
GRANT SELECT ON SYS.GV_$STREAMS_TRANSACTION TO STRMADMIN;
GRANT SELECT ON SYS.V_$AQ TO STRMADMIN;
GRANT SELECT ON SYS.V_$BUFFERED_PUBLISHERS TO STRMADMIN;
GRANT SELECT ON SYS.V_$BUFFERED_QUEUES TO STRMADMIN;
GRANT SELECT ON SYS.V_$BUFFERED_SUBSCRIBERS TO STRMADMIN;
GRANT SELECT ON SYS.V_$STREAMS_APPLY_COORDINATOR TO STRMADMIN;
GRANT SELECT ON SYS.V_$STREAMS_APPLY_READER TO STRMADMIN;
GRANT SELECT ON SYS.V_$STREAMS_APPLY_SERVER TO STRMADMIN;
GRANT SELECT ON SYS.V_$STREAMS_CAPTURE TO STRMADMIN;
GRANT SELECT ON SYS.V_$STREAMS_TRANSACTION TO STRMADMIN;
execute dbms_logmnr_d.set_tablespace('LOGMNRTS');
页:
[1]