estonolose's questions

我有这个查询，我想获得前 5名force_matching_signature。之后，我想显示每个子结果的附加信息。

以下查询在IN语句仅返回一个结果 ( first 1) 并且在主中SELECT使用等号时工作正常，但如果我想在 CTE 部分中返回多行并且我放入in主查询，则查询永远不会结束。

WITH
    top_signature
    AS
        (   SELECT force_matching_signature, COUNT (*) "Count"
            FROM gv$sqlarea
            WHERE force_matching_signature <> 0
            GROUP BY force_matching_signature
            ORDER BY 2 DESC
            FETCH FIRST 1 ROWS ONLY)
  SELECT force_matching_signature,
         module,
         users_executing,
         COUNT (*)     CUANTOS
    FROM gv$sqlarea
   WHERE force_matching_signature IN 
       (
       SELECT force_matching_signature FROM top_signature
       )
GROUP BY force_matching_signature, module, users_executing
ORDER BY 3 DESC;

我想不出如何优化语句，以便花费合理的时间。

我正在尝试使用重复命令创建备用数据库。

Primary : tntdb19
Standby : mntdb19

重复命令开始但在一段时间后失败

[oracle@monta191 admin]$ rman target sys/manager@tntdb19  auxiliary sys/manager@mntdb19

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 1 13:24:19 2020
Version 19.7.0.0.0

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

connected to target database: TNTDB19 (DBID=2747252158)
connected to auxiliary database: TNTDB19 (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 01-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK
...
contents of Memory Script:
{
   sql clone "create spfile from memory" ;
   shutdown clone immediate;
   startup clone nomount;
   restore clone from service  'tntdb19' standby controlfile;
}
executing Memory Script

sql statement: create spfile from memory

Oracle instance shut down

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/01/2020 13:27:12
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

正如您在开始时看到的那样，rman 毫无问题地连接到主数据库和新备用数据库

connected to target database: TNTDB19 (DBID=2747252158)
connected to auxiliary database: TNTDB19 (not mounted)

但问题是我认为当

   shutdown clone immediate;

作为复制过程的一部分，因为听者在这一刻失去了

Service "mntdb19" has 1 instance(s).
  Instance "mntdb19", status UNKNOWN, has 1 handler(s) for this service...
Service "mntdb19.vboxlab.es" has 1 instance(s).
  Instance "mntdb191", status BLOCKED, has 1 handler(s) for this service...

这是备用机器中的 listener.ora。

[grid@monta191 admin]$ cat listener.oa
cat: listener.oa: No such file or directory
[grid@monta191 admin]$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))        # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))        # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))        # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))        # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))      # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON       # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET     # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON        # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF     # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON        # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF     # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON        # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF     # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON      # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET        # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mntdb19)
      (ORACLE_HOME = /u01/app/grid/product/19c/grid_1)
      (SID_NAME = mntdb19)
    )
  )

我认为 SID_LIST_LISTENER 条目如果针对此问题，但它不起作用。

和 tnsnames.ora

[oracle@monta191 admin]$ cat tnsnames.ora 
TNTDB19 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tanto19-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tntdb19.vboxlab.es)
    )
  )

MNTDB19 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = monta19-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mntdb19.vboxlab.es)(UR=A)
    )
  )

Balazs 发布后所做的更改

嗨 Balazs，我已经更改了 listener.ora 中的 GLOBAL_DBNAME 和 ORACLE_HOME 条目

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mntdb19.vboxlab.es)
      (ORACLE_HOME = /u02/app/oracle/product/19c/db_1)
      (SID_NAME = mntdb19)
    )
  )

我为用户网格 (/u01/app/grid/product/19c/grid_1) 和 oracle 用户 (/u02/app/oracle/product/19c/db_1) 提供了不同的主页。在我原来的帖子中，我使用了网格主页，但现在我更改了数据库主页。

服务名称是

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
service_names                string  mntdb19.vboxlab.es

有了这 2 个更改，关闭备用数据库时重复的结果仍然是相同的

Oracle instance shut down

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/01/2020 18:55:30
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

我误会了什么？

问候

我正在尝试将句子的结果转换为列以在 excel 中创建图表。

我有这个 sql

select
hss.instance_number INST,
to_char(snap.begin_interval_time,'dd/mm/yyyy hh24:mi:ss') as Snap_Inicial,
hss.pool,hss.name,trunc(hss.bytes/1024/1024,2) SIZE_IN_MB
FROM DBA_HIST_SGASTAT hss,  dba_hist_snapshot snap
WHERE snap.instance_number = hss.instance_number
and hss.snap_id=snap.snap_id 
and hss.pool='shared pool'
and hss.instance_number=1
and hss.pool is not null
and BEGIN_INTERVAL_TIME BETWEEN sysdate-5 and sysdate
and hss.name in ('KGLHD','SQLA','KGLS')
ORDER BY INST, BEGIN_INTERVAL_TIME;

我得到这个输出

INST SNAP_INICIAL        POOL          NAME           SIZE_IN_MB
---- ------------------- ------------- -------------- ----------
   1 03/05/2020 09:00:30 shared pool   SQLA           150   
   1 03/05/2020 09:00:30 shared pool   KGLS           23    
   1 03/05/2020 09:00:30 shared pool   KGLHD          14    
   1 03/05/2020 09:30:32 shared pool   SQLA           160   
   1 03/05/2020 09:30:32 shared pool   KGLS           33    
   1 03/05/2020 09:30:32 shared pool   KGLHD          10

但我想要这样的输出

   INST SNAP_INICIAL        POOL          SQLA         KGLS    KGLHD 
---- ------------------- ------------- -----------  --------  ---------
   1 03/05/2020 09:00:30 shared pool      150        23        14
   1 03/05/2020 09:30:32 shared pool      160        33        10  

这可能吗？

谢谢。

我希望您过滤日期范围之间的行，但我只希望您返回前一个日期范围中包含的每一天的时间范围的行。

例如，您想过滤掉 2020 年 3 月 1 日到 3 月 3 日之间的所有行，也只过滤掉 14:00 到 18:00 之间的行。

到目前为止，我可以在两个日期或两个小时之间进行过滤，但是当我将这两件事放在一起时它不起作用。我没有任何记录。

and BEGIN_INTERVAL_TIME BETWEEN to_date('19/04/2020', 'DD/MM/YYYY') AND to_date('20/04/2020','DD/MM/YYYY')
and BEGIN_INTERVAL_TIME BETWEEN to_date('15:00:00', 'HH24:MI:SS') AND to_date('16:00:00','HH24:MI:SS')

问候，

我想选择“：”之间的3个数字。我不知道这个字符串的长度和“：”的位置。我有一个例子：

SELECT 'Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:' FROM DUAL;

我认为与regexp，但我不知道如何。

在此查询中，我想显示表的分区和子分区的列。如果NOM_TABLA查询仅获取一个表，则可以正常工作，但是当Select提供多个表格时，它就无法工作。

with nom_tabla as 
(
  select table_name from dba_tables 
  where table_name like 'TE%'
), 
col_part as 
(
  SELECT listagg(column_name, ', ' ) 
    within group (order by column_position) PART_POR 
  FROM dba_part_key_columns WHERE NAME in 
  (
    select * 
    from nom_tabla
  ) 
  GROUP BY name 
  ORDER BY name
), 
col_subpart as 
(
  SELECT listagg(column_name, ', ' ) 
    within group (order by column_position) PART_POR 
  FROM dba_subpart_key_columns 
  WHERE NAME IN 
  (
    select * from nom_tabla
  ) 
  GROUP BY name 
  ORDER BY name
)
select  
  t.OWNER PROPIETARIO, 
  t.TABLE_NAME as TABLA, 
  ' - ' as PARTICION, 
  NVL (pt.PARTITIONING_TYPE,' - ') AS TIPO_PART, 
  ' - ' as SUBPARTICION, 
  NVL(pt.SUBPARTITIONING_TYPE, ' - ') AS TIPO_SUBPART,
  NVL(t.tablespace_name,' - ') as TS, 
  NVL((select * from col_part),'-') , 
  NVL((select * from col_subpart),'-') 
  From dba_tables t, dba_part_tables pt 
  where   t.owner not in
  ('SYS','SYSTEM','DBSNMP','SYSCAT','WMSYS','MDSYS','XDB','ORDDATA')
  and     t.owner=pt.owner(+) 
  and t.table_name=pt.table_name(+)
  and t.TABLE_NAME in (select * from nom_tabla);

我认为问题出在查询的这一部分，因为它返回了多个记录：

, NVL((select * from col_part),'-') , NVL((select * from col_subpart),'-') 

我不知道如何避免这个问题。

我需要知道每个快照在一段时间内连接了多少会话。这在甲骨文中可能吗？

谢谢

我想在 SQL 脚本中询问开始日期和结束日期，accept但出现错误。

accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): '
accept v_FechaFin date    format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de fin    ('DD/MM/YYYY-HH24:MI:SS'):'

SQL> SQL> SQL> SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'): '
SQL> SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'):'
SQL> accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): '
SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'): '

问题是什么？