abril 2018 – Página: 4 – Rodrigo Silva e Silva

Listar 10 maiores tabelas Oracle

Publicado em 11 de abril de 2018 by rodrigosilvaesilva

Select para listar as 10 maiores tabela do banco.

SYS@ORCL >
column OWNER format a30
column SEGMENT_NAME format a30
select * from
  (select owner, segment_name, trunc(sum(bytes)/1024/1024/1024,2) "SIZE GB"
      from dba_segments
      where segment_type = 'TABLE'
      group by segment_name, owner
      order by 3 desc)
      where rownum <= 10;

OWNER                          SEGMENT_NAME                      SIZE GB
------------------------------ ------------------------------ ----------
ORCL                           NFSE_MENSAGENS                       7.02
ORCL                           AUD$                                 5.53
ORCL                           ORCL_LOG                              1.93
ORCL                           VENDAS                               1.56
ORCL                           VERSAO_LOG_DETALHES                  1.18
ORCL                           VENDA_ITENS                            .7
ORCL                           COMPRAS_ITENS                         .59
ORCL                           LANCAMENTO_CONTABIL                   .54
ORCL                           ORCL_MOVIMENTO                        .36
ORCL                           C100                                  .29

SYS@ORCL >

column OWNER format a30

column SEGMENT_NAME format a30

select * from

(select owner, segment_name, trunc(sum(bytes)/1024/1024/1024,2) "SIZE GB"

from dba_segments

where segment_type = 'TABLE'

group by segment_name, owner

order by 3 desc)

where rownum <= 10;

OWNER SEGMENT_NAME SIZE GB

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

ORCL NFSE_MENSAGENS 7.02

ORCL AUD$ 5.53

ORCL ORCL_LOG 1.93

ORCL VENDAS 1.56

ORCL VERSAO_LOG_DETALHES 1.18

ORCL VENDA_ITENS .7

ORCL COMPRAS_ITENS .59

ORCL LANCAMENTO_CONTABIL .54

ORCL ORCL_MOVIMENTO .36

ORCL C100 .29

ORA-01000 maximum open cursors exceeded

Publicado em 11 de abril de 2018 by rodrigosilvaesilva

How to. Resolvendo ORA-01000 maximum open cursors exceeded Verificar configuração atual de cursores.

14:54:39 SYS@NBS > show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

14:54:39 SYS@NBS > show parameter cursor

NAME TYPE VALUE

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

cursor_bind_capture_destination string memory+disk

cursor_sharing string EXACT

cursor_space_for_time boolean FALSE

open_cursors integer 300

session_cached_cursors integer 50

Verificar configuração atual de cursores.

SYS@orcl > col MAX_OPEN_CUR format a20
SYS@orcl > select max(a.value) as highest_open_cur, p.value as max_open_cur
from gv$sesstat a, gv$statname b, gv$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name = 'open_cursors'
group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- --------------------
      177             300

SYS@orcl > col MAX_OPEN_CUR format a20

SYS@orcl > select max(a.value) as highest_open_cur, p.value as max_open_cur

from gv$sesstat a, gv$statname b, gv$parameter p

where a.statistic# = b.statistic#

and b.name = 'opened cursors current'

and p.name = 'open_cursors'

group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR

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

177 300

Cursor aberto por usuário.

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

select s.username, max(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid (+)= a.sid
and b.name = 'opened cursors current'
group by s.username;

USERNAME                       MAX(A.VALUE)
------------------------------ ------------
JSOUZA                                   33
BMENEZES                                 57
RALVESMIR                                29
JMACENA2                                 37
MASSAO4                                  49
NLEITAO                                  42
FGSILVA                                  30
FFILHO                                  109
TMIRANDA                                 58
NBS2                                     67
JANE20                                   41
DRALVESFIS                               37
ECARLOSM                                 56
MBRABO                                  110
GSOUZA                                  114
DGALDINO                                 66
CJUNIOR                                 121
CVALE                                    94
ASCIOLI                                  51
LREDIG                                  160
4VICTORIA                                56
SYS                                       5
JMUNIZ                                  177

51 rows selected.

Connected to:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

select s.username, max(a.value)

from v$sesstat a, v$statname b, v$session s

where a.statistic# = b.statistic#

and s.sid (+)= a.sid

and b.name = 'opened cursors current'

group by s.username;

USERNAME MAX(A.VALUE)

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

JSOUZA 33

BMENEZES 57

RALVESMIR 29

JMACENA2 37

MASSAO4 49

NLEITAO 42

FGSILVA 30

FFILHO 109

TMIRANDA 58

NBS2 67

JANE20 41

DRALVESFIS 37

ECARLOSM 56

MBRABO 110

GSOUZA 114

DGALDINO 66

CJUNIOR 121

CVALE 94

ASCIOLI 51

LREDIG 160

4VICTORIA 56

SYS 5

JMUNIZ 177

51 rows selected.

Alterar quantidade cursores.

SYS@orcl > alter system set open_cursors=QUANTIDADE;

1	SYS@orcl > alter system set open_cursors=QUANTIDADE;

ORA-39097: Data Pump job encountered unexpected error -1001

Publicado em 11 de abril de 2018 by rodrigosilvaesilva

Problema ao rodar impdp Oracle Release 12.2.0.1.0

impdp system/senha@orcl directory=BKP_DIR dumpfile=BKP.dmp logfile=imp_full.log EXCLUDE=statistics;

1	impdp system/senha@orcl directory=BKP_DIR dumpfile=BKP.dmp logfile=imp_full.log EXCLUDE=statistics;

Logs

Import: Release 12.2.0.1.0 - Production on Wed Mar 14 16:27:47 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 3588
ORA-06512: at "SYS.KUPM$MCP", line 2628

ORA-39097: Data Pump job encountered unexpected error -1001

Import: Release 12.2.0.1.0 - Production on Wed Mar 14 16:27:47 2018

Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

ORA-39006: internal error

ORA-39065: unexpected master process exception in DISPATCH

ORA-01001: invalid cursor

ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 3588

ORA-06512: at "SYS.KUPM$MCP", line 2628

ORA-39097: Data Pump job encountered unexpected error -1001

Causa: O dataPump não pode usar a API metadados. Isso ocorre devido as folhas de estilo XSL não estarem configuradas corretamente. Solução: Durante o dbms_metadata_util.load_stylesheets, le o diretório $ ORACLE_HOME /dbms /xml /xsl e carregamos os arquivos XSL usando o pacote DBMS_LOB. Se os arquivos…

← Posts recentes