março 2019 – Rodrigo Silva e Silva

Obtendo todas as permissões SELECT do Owner e conceder a um usuário.

SYS@ORCL > select 'GRANT SELECT ON '|| owner ||'.' || object_name || ' TO USER;' from all_objects where owner='NOME';

GRANT SELECT ON NBS.PK_BKP_OPER_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_OPER_ID TO USER;
GRANT SELECT ON NBS.BKP_TABELA TO USER;
GRANT SELECT ON NBS.PK_BKP_TABELA_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_TABELA_ID TO USER;
GRANT SELECT ON NBS.BKP_CAMPO_BLOB TO USER;
GRANT SELECT ON NBS.PK_BKP_CAMPO_BLOB_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_CAMPO_BLOB_ID TO USER;
GRANT SELECT ON NBS.BKP_MOVIMENTO TO USER;
GRANT SELECT ON NBS.PK_BKP_MOV_ID TO USER;
GRANT SELECT ON NBS.SEQ_BKP_MOV_ID TO USER;
GRANT SELECT ON NBS.HYUNDAI_WS_LOG TO USER;
GRANT SELECT ON NBS.SYS_C00101260 TO USER;
GRANT SELECT ON NBS.SEQ_HYUNDAI_WS_LOG TO USER;
GRANT SELECT ON NBS.IDXCONTROLE_SERIE TO USER;
GRANT SELECT ON NBS.IDX_SPED_ITENS_DOCUMENTO_DATA TO USER;
GRANT SELECT ON NBS.FORD_EXT_MOV_OIC TO USER;
GRANT SELECT ON NBS.IDX_FORD_EXT_MOV_OIC TO USER;

13786 rows selected.

SYS@ORCL > select 'GRANT SELECT ON '|| owner ||'.' || object_name || ' TO USER;' from all_objects where owner='NOME';

GRANT SELECT ON NBS.PK_BKP_OPER_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_OPER_ID TO USER;

GRANT SELECT ON NBS.BKP_TABELA TO USER;

GRANT SELECT ON NBS.PK_BKP_TABELA_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_TABELA_ID TO USER;

GRANT SELECT ON NBS.BKP_CAMPO_BLOB TO USER;

GRANT SELECT ON NBS.PK_BKP_CAMPO_BLOB_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_CAMPO_BLOB_ID TO USER;

GRANT SELECT ON NBS.BKP_MOVIMENTO TO USER;

GRANT SELECT ON NBS.PK_BKP_MOV_ID TO USER;

GRANT SELECT ON NBS.SEQ_BKP_MOV_ID TO USER;

GRANT SELECT ON NBS.HYUNDAI_WS_LOG TO USER;

GRANT SELECT ON NBS.SYS_C00101260 TO USER;

GRANT SELECT ON NBS.SEQ_HYUNDAI_WS_LOG TO USER;

GRANT SELECT ON NBS.IDXCONTROLE_SERIE TO USER;

GRANT SELECT ON NBS.IDX_SPED_ITENS_DOCUMENTO_DATA TO USER;

GRANT SELECT ON NBS.FORD_EXT_MOV_OIC TO USER;

GRANT SELECT ON NBS.IDX_FORD_EXT_MOV_OIC TO USER;

13786 rows selected.

Após instalar o Statspack Oracle 11.2.0.4 me deparei com os seguintes eventos

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
LGWR worker group idle                          12,929        41.0
CPU time                                            12       6,923 ######   22.0
heartbeat redo informer                          3,604       3,604   1000   11.4
lreg timer                                       1,201       3,607   3003   11.4
AQPC idle                                          120       3,601  30008   11.4

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

----------------------------------------- ------------ ----------- ------ ------

LGWR worker group idle 12,929 41.0

CPU time 12 6,923 ###### 22.0

heartbeat redo informer 3,604 3,604 1000 11.4

lreg timer 1,201 3,607 3003 11.4

AQPC idle 120 3,601 30008 11.4

Resolvendo Atualização tabela de eventos Statspack

SQL> 
delete from STATS$IDLE_EVENT;
insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';
insert into STATS$IDLE_EVENT values('log file parallel write');
insert into STATS$IDLE_EVENT values('target log write size');
commit;

SQL>

delete from STATS$IDLE_EVENT;

insert into STATS$IDLE_EVENT select name from V$EVENT_NAME where wait_class='Idle';

insert into STATS$IDLE_EVENT values('log file parallel write');

insert into STATS$IDLE_EVENT values('target log write size');

commit;

Mês: março 2019

Oracle SELECT, OBTER TODAS AS PERMISSOES SELECT DO OWNER E CONCEDER A UM USUARIO

Oracle – Statspack Idle events (LGWR worker group idle CPU time heartbeat redo informer lreg timer AQPC idle)