Rodrigo Silva e Silva – Página: 10

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

Publicado em 12 de março de 2019 by rodrigosilvaesilva

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.

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

Publicado em 7 de março de 2019 by rodrigosilvaesilva

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;

Habilitar log de consultas lentas do MySQL

Publicado em 11 de janeiro de 2019 by rodrigosilvaesilva

Edite o my.cnf

shell> vi /etc/my.cnf

1	shell> vi /etc/my.cnf

Adicionar os parâmetros. long_query_time = Tempo gasto por uma consulta MySQL 5.6 ou anterior

slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 2

slow_query_log = 1

log-slow-queries = /var/log/mysql-slow.log

long_query_time = 2

Note: In MySQL 5.7, the variable log-slow-queries is deprecated. Use slow_query_log_file instead. MySQL 5.7

slow_query_log = 1
slow_query_log_file=/var/log/mysql-slow.log
long_query_time = 2

slow_query_log = 1

slow_query_log_file=/var/log/mysql-slow.log

long_query_time = 2

Crie o arquivo mysql-show.log

shell> touch /var/log/mysql-slow.log

1	shell> touch /var/log/mysql-slow.log

Ajustar permissão.

 shell> chown mysql:mysql /var/log/mysql-slow.log

1	shell> chown mysql:mysql /var/log/mysql-slow.log

Iniciar o monitoramento de consultas lentas.

shell> mysqldumpslow -a /var/log/mysql-slow.log

 Reading mysql slow query log from /var/log/mysql-slow.log

shell> mysqldumpslow -a /var/log/mysql-slow.log

Reading mysql slow query log from /var/log/mysql-slow.log

Fonte Plesk

Statspack ERRO ORA-20200 , ORA-06512

Publicado em 31 de dezembro de 2018 by rodrigosilvaesilva

Erro ao gerar report statspack ORA-20200: Database/Instance 2251672657/1 does not exist in STATS$DATABASE_INSTANCE ORA-06512: at line 23

11:47:51 PERFSTAT@ORCL > @?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2251672657 ORCL               1  ORCL

Elapsed: 00:00:00.01


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 2251672657 for database Id
Using          1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 2251672657/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23


Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

11:47:51 PERFSTAT@ORCL > @?/rdbms/admin/spreport

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

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

2251672657 ORCL 1 ORCL

Elapsed: 00:00:00.01

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 2251672657 for database Id

Using 1 for instance number

declare

ERROR at line 1:

ORA-20200: Database/Instance 2251672657/1 does not exist in

STATS$DATABASE_INSTANCE

ORA-06512: at line 23

Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Causa. Erro gerado após o statspack instalado. Solução. Aguardar gerar o Snap Automático ou gerar manualmente.

12:01:52 SYS@ORCL > @?/rdbms/admin/spreport

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2251672657  ORCL              1 ORCL 

Elapsed: 00:00:00.01


Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 2251672657        1 ORCL        ORCL        bdora-01.
                                               local

Using 2251672657 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.



Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
ORCL        ORCL                1 31 Dec 2018 12:00     5

12:01:52 SYS@ORCL > @?/rdbms/admin/spreport

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

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

2251672657 ORCL 1 ORCL

Elapsed: 00:00:00.01

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

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

2251672657 1 ORCL ORCL bdora-01.

local

Using 2251672657 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level Comment

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

ORCL ORCL 1 31 Dec 2018 12:00 5

ERRO ORA-16179: incremental changes to “log_archive_dest_1” not allowed with SPFILE

Publicado em 30 de dezembro de 2018 by rodrigosilvaesilva

Erro ao configurar o “log_archive_dest_1” Verificando o parâmetro log_archive_dest_1

10:34:01 SYS@ORCL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string

10:34:01 SYS@ORCL> show parameter log_archive_dest_1;

NAME TYPE VALUE

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

log_archive_dest_1 string

Erro ORA-16179 ao definir o parâmetro.

10:34:10 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/';
ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


Elapsed: 00:00:00.00

10:34:10 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/';

ALTER SYSTEM SET log_archive_dest_1='/u02/fra/ORCL/archivelog/'

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Elapsed: 00:00:00.00

Solução. Adicionar o LOCATION

10:34:43 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/ORCL/archivelog/';

System altered.

Elapsed: 00:00:00.01

10:34:43 SYS@ORCL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/ORCL/archivelog/';

System altered.

Elapsed: 00:00:00.01

Verificando o parâmetro

SYS@ORCL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u02/fra/ORCL/archi
                                                 velog/

SYS@ORCL> show parameter log_archive_dest_1;

NAME TYPE VALUE

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

log_archive_dest_1 string LOCATION=/u02/fra/ORCL/archi

velog/

← Posts recentes Posts antigos →