jpmc26's questions

Considere o seguinte bloco (incompleto) de PL/pgSQL dentro de uma função:

CREATE OR REPLACE FUNCTION my_calc(myvar1 NUMERIC, myvar2 NUMERIC)
    RETURNS NUMERIC
    RETURNS NULL ON NULL INPUT
    IMMUTABLE
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RETURN some_third_party_function(myvar1, myvar2);
    EXCEPTION WHEN internal_error THEN
        IF SQLERRM LIKE 'KnownErrorPrefix:%' THEN
            RETURN 0;
        ELSE
            -- Reraise the original exception here
            RAISE EXCEPTION '%', SQLERRM;
        END IF;
    END
    $$

Quando ocorre um erro imprevisto, esse código lançará uma nova exceção com a mesma mensagem. No entanto, não preservará o tipo ou contexto original.

Como posso aumentar novamente ou relançar a exceção original sem modificações?

Estou testando o Oracle 12c para nosso aplicativo. Eu tenho algo semelhante a essa visão em um esquema específico, que chamaremos MY_OWNER_SCHEMApara esta pergunta:

CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER 
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
    OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
;

A exibição é uma espécie de exibição de "metadados", usada para verificar (programaticamente) que todas as tabelas e exibições têm um comentário. Como MY_OWNER_SCHEMAestá bloqueado, exceto durante a implantação, concedemos permissões para MY_LOGIN_USERa SELECTpartir dele:

GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;

(Isso GRANTé executado durante a implantação enquanto estiver conectado como MY_OWNER_SCHEMA.)

Isso funcionou muito bem no Oracle 11g, e criá-lo também funciona no 12c. No entanto, apenas no 12c, a GRANTfalha com a seguinte mensagem:

SQL Error: ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

Isso é bastante estranho, pois ALL_OBJECTSé uma interface pública acessível a todos os usuários, e mostra apenas objetos aos quais o usuário já tem algum acesso. Alguma coisa mudou com o modelo de permissões ou as configurações de permissões padrão no Oracle 12c? É possível que o banco de dados tenha sido instalado de forma diferente? Existe uma maneira de contornar esse erro sem dar MY_OWNER_SCHEMApermissão explícita GRANTa essa visão global do sistema?

Preciso ver todas as funções de um usuário, incluindo as funções herdadas de uma função.

Considere, por exemplo, este arranjo:

CREATE USER TEMPUSER1 IDENTIFIED BY "TEMPUSER1" ACCOUNT LOCK;
CREATE USER TEMPUSER2 IDENTIFIED BY "TEMPUSER2" ACCOUNT LOCK;

CREATE ROLE TEMP_ROLE1;
CREATE ROLE TEMP_ROLE2;
CREATE ROLE TEMP_ROLE3;
CREATE ROLE TEMP_ROLE4;
CREATE ROLE TEMP_ROLE5;

GRANT TEMP_ROLE1 TO TEMP_ROLE3;
GRANT TEMP_ROLE2 TO TEMP_ROLE3;
GRANT TEMP_ROLE3 TO TEMP_ROLE4;
GRANT TEMP_ROLE3 TO TEMP_ROLE5;
GRANT TEMP_ROLE4 TO TEMP_ROLE6;
GRANT TEMP_ROLE5 TO TEMP_ROLE6;

GRANT TEMP_ROLE2 TO TEMPUSER1;
GRANT TEMP_ROLE4 TO TEMPUSER1;
GRANT TEMP_ROLE6 TO TEMPUSER1;
GRANT TEMP_ROLE2 TO TEMPUSER2;

Então eu quero o resultado

GRANTED_USER   | GRANTED_ROLE
---------------+---------------
TEMPUSER1      | TEMP_ROLE1
TEMPUSER1      | TEMP_ROLE2
TEMPUSER1      | TEMP_ROLE3
TEMPUSER1      | TEMP_ROLE4
TEMPUSER1      | TEMP_ROLE5
TEMPUSER2      | TEMP_ROLE1
TEMPUSER2      | TEMP_ROLE2

Estou usando o Oracle 11.2g, mas também quero respostas para futuras versões do Oracle. Eu gostaria que essa pergunta permanecesse relevante se a Oracle introduzisse novos recursos que facilitassem isso (como uma visão que apenas nos fornece isso diretamente sem escrever nossas próprias consultas).

Considere a seguinte tabela:

CREATE TABLE MY_DATA (
    MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
    THE_DATA VARCHAR(10) NOT NULL,
    DATA_TIMESTAMP TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

(Observe que TIMESTAMP, sem mais qualificação, o padrão é o tipo de dados usado acima.)

O que acontece se eu exportar esses dados usando o Data Pump e depois importá-los para outro banco de dados que tenha um fuso horário diferente? O tempo armazenado será ajustado para o novo fuso horário ou se comportará como se o deslocamento tivesse sido alterado sem alterar o restante dos dados?

Por exemplo, se

• O banco de dados original é o horário central dos EUA
• Uma linha contém2017-10-25 12:14:38
• Importamos os dados para um banco de dados usando o horário do leste dos EUA

a hora será ajustada para 2017-10-25 12:14:38 -04:00(como na substituição do deslocamento sem alterar a data/hora) ou 2017-10-25 13:14:38 -04:00(= 2017-10-25 12:14:38 -05:00)?

Minhas desculpas por não testar isso sozinho. Não tenho acesso a outro banco de dados com fuso horário diferente, mas gostaria de saber para informar minha escolha de tipo de dado.

Tenho a seguinte tabela:

CREATE TABLE MY_DATA (
  MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
  GEOM SDO_GEOMETRY
);

Configurei seus metadados SDO e criei um índice espacial:

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('MY_DATA', 'GEOM', 4326, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180.0000,180.0000,0.00000000001),MDSYS.SDO_DIM_ELEMENT('Y',-90.0000,90,0.00000000001)));

CREATE INDEX SPIX_MY_DATA ON MY_DATA (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

A tabela tem cerca de 150.000 linhas.

Eu preciso encontrar linhas onde as geometrias cruzam uma linha específica. Esta parece ser uma tarefa bastante simples. Tudo o que é preciso é uma JOINe uma WHEREcláusula:

SELECT /*+ INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

Mas esta consulta é muito lenta. Leva mais de 85 segundos para buscar apenas 8 linhas relacionadas. Podemos ver no plano de consulta que ele não está usando o índice espacial, apesar da dica de índice:

Plan hash value: 4008712617

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   1 |  NESTED LOOPS                |               |   379 |  9854 |  1390   (1)| 00:00:17 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | MY_DATA       |   379 |  4927 |  1388   (1)| 00:00:17 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668 AND 
              "MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT 
              querytype=window  ')='TRUE')

(A linha 2 é MD1 e a linha 4 é MD2.)

Eu tentei mudar a dica para /*+ INDEX(MY_DATA SPIX_MY_DATA)*/, mas isso também não teve efeito. Tenho certeza de que minhas estatísticas estão atualizadas.

Como posso acelerar esta consulta? Como posso obtê-lo para usar o índice espacial?

Atualmente estou usando o Oracle 11.2.

Eu tenho uma tabela que conterá alguns dados pré-calculados com base em outras tabelas. (Computar em tempo real é muito caro computacionalmente, dado o tamanho dos dados com os quais tenho que lidar.) Estarei gerando de forma incremental à medida que os dados de origem forem adicionados. (Nunca precisarei UPDATEdisso em uso normal; partes podem ser excluídas e regeneradas.) A tabela será bastante grande. Atualmente, são cerca de 50 milhões de linhas e crescerão a cada ano.

A maioria das consultas nesta tabela será filtrada por uma coluna de ID de chave estrangeira. Dessa forma, eles têm um desempenho melhor se todas as linhas desse ID forem agrupadas nas mesmas páginas. Posso garantir essa ordenação no disco criando um índice e chamando CLUSTERperiodicamente, mas isso obviamente não é o ideal, pois exigiria algum tipo de tarefa agendada, coordenando com o uso e outras tarefas agendadas, etc.

No entanto, como estou gerando esses dados em pedaços relacionados à chave estrangeira que quero CLUSTER, posso facilmente colocar uma ORDER BYcláusula no INSERTcomando:

INSERT INTO big_table (source_table1_id,a,b,c)
SELECT
   source_table1_id,
   5 /* some formula */,
   /* ... */
FROM source_table1
JOIN source_table2 ON ...
...
WHERE ... /* some condition indicating what needs to be generated */
ORDER BY source_table1_id

Isso afetará a ordem de armazenamento em disco, agrupando as linhas em um número próximo ao mínimo de páginas? E se isso acontecer, existem outros processos que podem atrapalhar a ordem do disco mais tarde?

Atualmente estou usando o PostgreSQL 9.3, mas gostaria de saber sobre as versões mais recentes também para atualizações.

Preciso realizar uma atualização simples em todas as linhas de uma tabela. A tabela tem 40-50 milhões de linhas. A eliminação de índices e restrições durante os UPDATEresultados resulta em uma grande melhoria de desempenho.

Mas e o autovácuo? O autovacuum pode iniciar um VACUUMou ANALYZEno meio de um UPDATE? Nesse caso, seria um trabalho inútil que consumiria os recursos da máquina. Eu poderia desativá-lo na tabela antes do UPDATEe reativá-lo depois:

ALTER TABLE my_table
SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);
-- Drop constraints, drop indexes, and disable unnecessary triggers

UPDATE my_table SET ....;

-- Restore constraints, indexes, and triggers
ALTER TABLE my_table
SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);

Isso funciona mesmo se eu não confirmar depois do primeiro ALTER?

Além disso, se eu desativá-lo durante o UPDATE, ele será acionado após a atualização ou ignorará essas atualizações porque foi desativado durante elas? (Minha suspeita é que será executado, mas prefiro ter certeza.)

Estou usando o PG 9.3 agora, mas devo atualizar em breve. Portanto, qualquer menção de mudanças em versões mais recentes é apreciada.

Há um script no final que criará totalmente o esquema e o preencherá com dados de amostra.

Esquema

Considere estas duas tabelas:

Tabela de polígonos:

CREATE TABLE my_polygon (
    my_polygon_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    value1 NUMERIC NOT NULL,
    value2 NUMERIC NOT NULL,
    value3 NUMERIC NOT NULL,
    geom GEOMETRY(Polygon) NOT NULL
)
;

CREATE INDEX ON my_polygon (common_id);
CREATE INDEX ON my_polygon USING GIST (common_id, geom);

Tabela de pontos contidos dentro de polígonos:

CREATE TABLE my_point (
    my_point_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    pointvalue NUMERIC NOT NULL,
    geom GEOMETRY(Point) NOT NULL
);

CREATE INDEX ON my_point (common_id);
CREATE INDEX ON my_point USING GIST (common_id, geom);

O fato de estar usando geometrias não está estritamente relacionado ao problema aqui; no entanto, acho que isso torna os motivos do que estou tentando fazer muito mais claros.

consulta de problema

O problema é que existem sobreposições muito pequenas e insignificantes entre os polígonos. (Tentar limpá-los realmente não é uma opção. As sobreposições vêm de algum tipo de erro de ponto flutuante ao gerá-los, tanto quanto posso imaginar.) Mas alguns pontos podem cair dentro dessas pequenas sobreposições, resultando em duas linhas quando eu JOINos com base na contenção. Mas, na verdade, cada ponto só deve ser associado a um único polígono. Quando um cai dentro de dois deles, realmente não importa com qual deles ele acaba associado, então é bom fazer a consulta, apenas escolha um, assim:

SELECT DISTINCT ON (my_point.my_point_id)
    my_polygon.*,
    my_point.my_point_id,
    my_point.pointvalue,
    my_point.geom AS pointgeom
FROM my_polygon
JOIN my_point ON my_point.common_id = my_polygon.common_id AND ST_Contains(my_polygon.geom, my_point.geom)
WHERE my_polygon.common_id = 1
ORDER BY my_point.my_point_id, my_polygon.my_polygon_id

Como na consulta acima, normalmente desejo SELECTbasear-me no arquivo common_id. Esta consulta funciona bem. Seu plano de consulta se parece com isto:

No entanto, essa é a lógica de que preciso em várias consultas diferentes, por isso queria colocá-la em uma exibição. O resultado é que, no que diz respeito ao planejador de consulta, a consulta se parece com isso:

SELECT *
FROM (
    SELECT DISTINCT ON (my_point.my_point_id)
        my_polygon.*,
        my_point.my_point_id,
        my_point.pointvalue,
        my_point.geom AS pointgeom
    FROM my_polygon
    JOIN my_point ON my_point.common_id = my_polygon.common_id AND ST_Contains(my_polygon.geom, my_point.geom)
    ORDER BY my_point.my_point_id, my_polygon.my_polygon_id
) point_with_polygon
WHERE common_id = 1

O resultado é que agora o PostgreSQL filtra common_id depois de executar o DISTINCT ON, o que significa que ele tem JOINa totalidade de ambas as tabelas. Aqui está o plano de consulta:

Como posso permitir que o PostgreSQL envie o filtro para uma parte inicial da consulta e ainda coloque a consulta geral em uma exibição?

Estou preso no PG 9.3 agora, mas atualizar para 9.5 pode ser uma opção.

Esquema e script de dados de amostra

Requer PostGIS. (É por isso que não há SQL Fiddle.)

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- DROP FUNCTION ST_GeneratePoints(geometry, numeric);
DO $doblock$
BEGIN
    IF NOT EXISTS(SELECT * FROM pg_proc WHERE UPPER(proname) = UPPER('ST_GeneratePoints')) THEN
        -- Create naive ST_GeneratePoints if version of PostGIS is not new enough
        CREATE FUNCTION ST_GeneratePoints(g geometry, npoints numeric)
            RETURNS geometry
            VOLATILE
            RETURNS NULL ON NULL INPUT
            LANGUAGE plpgsql
            AS $$
            DECLARE
                num_to_generate INTEGER := npoints::INTEGER;
                adjustment CONSTANT FLOAT := 0.00000000001;
                x_min FLOAT := ST_XMin(g) + adjustment;
                x_max FLOAT := ST_XMax(g) - adjustment;
                y_min FLOAT := ST_YMin(g) + adjustment;
                y_max FLOAT := ST_YMax(g) - adjustment;
                temp_result GEOMETRY[];
                result_array GEOMETRY[] := ARRAY[]::GEOMETRY[];
            BEGIN
                IF ST_IsEmpty(g) THEN
                    RAISE EXCEPTION 'Cannot generate points inside an empty geometry';
                END IF;

                IF ST_Dimension(g) < 2 THEN
                    RAISE EXCEPTION 'Only polygons supported';
                END IF;

                -- Reduce number of loops to reduce slow array_cat calls
                WHILE num_to_generate > 0 LOOP
                    SELECT ARRAY_AGG(contained.point) INTO temp_result
                    FROM (
                        SELECT point
                        FROM (
                            SELECT ST_MakePoint(
                                x_min + random() * (x_max - x_min),
                                y_min + random() * (y_max - y_min)
                            ) point
                            -- Generate extras to reduce number of loops
                            --
                            -- Each point has a probability of  ST_Area(g) / ST_Area(ST_Envelope(g))  to fall within the polygon.
                            -- So on average, we expect ST_Area(g) / ST_Area(ST_Envelope(g)) of the points generated to fall within.
                            -- Generating  ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate  points means that on average, we'll
                            -- get
                            --
                            --     ST_Area(g) / ST_Area(ST_Envelope(g)) * ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate
                            --      = num_to_generate
                            --
                            -- points within the polygon. (Notice the numerators and denominators cancel out.) This means we'll 
                            -- only run one loop about half the time without generating an excessive number of points.
                            --
                            -- Generate at least 20 to avoid a lot of loops for small numbers, though.
                            FROM generate_series(1, GREATEST(20, CEIL(ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate)::INTEGER))
                        ) candidate
                        WHERE ST_Contains(g, candidate.point)
                        -- Filter out extras if we have too many matches
                        LIMIT num_to_generate
                    ) contained
                    ;
                    IF ARRAY_LENGTH(temp_result, 1) > 0 THEN
                        result_array := array_cat(result_array, temp_result);
                        num_to_generate := npoints - COALESCE(ARRAY_LENGTH(result_array, 1), 0);
                    END IF;
                END LOOP;
                RETURN (SELECT ST_Union(point) FROM UNNEST(result_array) result (point));
            END;
            $$;
        RAISE NOTICE 'Created ST_GeneratePoints';
    ELSE
        RAISE NOTICE 'ST_GeneratePoints exists';
    END IF;
END
$doblock$
;

DROP TABLE IF EXISTS my_polygon;

CREATE TABLE my_polygon (
    my_polygon_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    value1 NUMERIC NOT NULL,
    value2 NUMERIC NOT NULL,
    value3 NUMERIC NOT NULL,
    geom GEOMETRY(Polygon) NOT NULL
)
;

CREATE INDEX ON my_polygon (common_id);
CREATE INDEX ON my_polygon USING GIST (common_id, geom);


WITH common AS (
    SELECT
        common_id,
        random() * 5000 AS common_x_translate,
        random() * 5000 AS common_y_translate
    FROM (
        SELECT TRUNC(random() * 1000) + 1 AS common_id
        FROM generate_series(1, 100)
        UNION 
        SELECT 1
    ) a
),
geom_set_with_small_overlaps AS (
    SELECT
        ST_MakeEnvelope(
            x.translate, 
            y.translate, 
            x.translate + 1.1, 
            y.translate + 1.1
        ) AS geom
    FROM
        generate_series(0, 9) x (translate),
        generate_series(0, 9) y (translate)
)
INSERT INTO my_polygon (common_id, value1, value2, value3, geom)
SELECT
    common_id,
    random() * 100,
    random() * 100,
    random() * 100,
    ST_Translate(geom, common_x_translate, common_y_translate)
FROM common, geom_set_with_small_overlaps
;

DROP TABLE IF EXISTS my_point;

CREATE TABLE my_point (
    my_point_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    pointvalue NUMERIC NOT NULL,
    geom GEOMETRY(Point) NOT NULL
);

INSERT INTO my_point (common_id, pointvalue, geom)
SELECT
    common_id,
    random() * 100,
    (ST_Dump(ST_GeneratePoints(extent, FLOOR(5000 + random() * 15000)::NUMERIC))).geom
FROM (
    SELECT
        common_id,
        -- Small negative buffer prevents lying on the outer edge
        ST_Buffer(ST_Extent(geom), - 0.0001) AS extent
    FROM my_polygon
    GROUP BY common_id
) common
UNION ALL
SELECT
    common_id,
    random() * 100,
    (ST_Dump(ST_GeneratePoints(intersection, TRUNC(random() * 5)::NUMERIC))).geom
FROM (
    SELECT
        p1.common_id,
        p1.my_polygon_id AS id1,
        p2.my_polygon_id AS id2,
        ST_Intersection(p1.geom, p2.geom) AS intersection
    FROM my_polygon p1
    JOIN my_polygon p2 ON (
        p1.my_polygon_id < p2.my_polygon_id AND
        p1.common_id = p2.common_id AND
        ST_Intersects(p1.geom, p2.geom)
    )
) a
;

CREATE INDEX ON my_point (common_id);
CREATE INDEX ON my_point USING GIST (common_id, geom);

Você provavelmente quer VACUUM ANALYZEdepois disso.

Planos de consulta como texto

WHEREcláusula dentro (bom desempenho):

Unique  (cost=1195.74..1207.74 rows=2400 width=216)
  ->  Sort  (cost=1195.74..1201.74 rows=2400 width=216)
        Sort Key: my_point.my_point_id, my_polygon.my_polygon_id
        ->  Nested Loop  (cost=5.34..1060.99 rows=2400 width=216)
              ->  Bitmap Heap Scan on my_polygon  (cost=4.93..191.74 rows=100 width=164)
                    Recheck Cond: (common_id = 1)
                    ->  Bitmap Index Scan on my_polygon_common_id_geom_idx  (cost=0.00..4.90 rows=100 width=0)
                          Index Cond: (common_id = 1)
              ->  Index Scan using my_point_common_id_geom_idx on my_point  (cost=0.41..8.68 rows=1 width=52)
                    Index Cond: ((common_id = 1) AND (my_polygon.geom && geom))
                    Filter: _st_contains(my_polygon.geom, geom)

WHEREcláusula externa (desempenho ruim):

Subquery Scan on a  (cost=209447.85..215842.18 rows=1827 width=212)
  Filter: (a.common_id = 1)
  ->  Unique  (cost=209447.85..211274.80 rows=365390 width=212)
        ->  Sort  (cost=209447.85..210361.33 rows=365390 width=212)
              Sort Key: my_point.my_point_id, my_polygon.my_polygon_id
              ->  Nested Loop  (cost=0.41..63285.00 rows=365390 width=212)
                    ->  Seq Scan on my_polygon  (cost=0.00..338.00 rows=9800 width=164)
                    ->  Index Scan using my_point_common_id_geom_idx on my_point  (cost=0.41..6.41 rows=1 width=52)
                          Index Cond: ((common_id = my_polygon.common_id) AND (my_polygon.geom && geom))
                          Filter: _st_contains(my_polygon.geom, geom)

Digamos que eu altere permanentemente meu banco de dados search_pathassim:

ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;

Como posso redefini-lo para qualquer padrão que um banco de dados intocado e inalterado neste cluster teria?

Isso geralmente é "$user",public, mas pode ser alterado no arquivo de configuração . Ter uma maneira de ler o valor dos parâmetros provenientes do arquivo de configuração funcionaria (para que eu pudesse criar uma consulta dinâmica na pior das hipóteses), mas não tive sorte em encontrar nada do tipo.

Estou tentando aumentar a cota de espaço em um esquema específico. Estou usando a seguinte consulta para fazer isso:

ALTER USER MY_SCHEMA QUOTA "100 M" ON "USERS";

Isso resulta no seguinte erro:

SQL Error: ORA-02187: invalid quota specification
02187. 00000 -  "invalid quota specification"
*Cause:    In a grant or revoke statement, the user attempted to grant
           a tablespace quota above the upper limit.
*Action:   Grant a smaller tablespace quota.

Mas isso não faz o menor sentido. O usuário já tem uma cota de 50 MB e uma consulta que tenta defini-la para 25 MB falha com a mesma mensagem de erro:

ALTER USER MY_SCHEMA QUOTA "25 M" ON "USERS";

Confirmei que meu tablespace tem espaço suficiente. (Seus arquivos de dados podem adquirir cerca de 2 GB a mais de espaço em disco.) O que está causando esse erro?

Eu tenho uma função no Oracle que nem eu nem minha equipe escrevemos e é fortemente ofuscada. No entanto, ele possui um parâmetro de saída do cursor. Como posso descobrir o número e os tipos que o cursor contém?

Para um exemplo do que preciso, considere este cursor:

DECLARE
  TEMP1 VARCHAR2(500);
  TEMP2 NUMBER;
  TEMP3 RAW(16);
  TEMP SYS_REFCURSOR;
  PROCEDURE MAKE_TEMP(TEMP_RETURN OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN TEMP_RETURN FOR SELECT DUMMY, 5, HEXTORAW('111') FROM DUAL;
  END;
BEGIN
  MAKE_TEMP(TEMP);
  LOOP
    FETCH TEMP INTO TEMP1, TEMP2, TEMP3;
    EXIT WHEN (TEMP%NOTFOUND);
    DBMS_OUTPUT.PUT_LINE('1: '||TEMP1);
    DBMS_OUTPUT.PUT_LINE('2: '||TO_CHAR(TEMP2));
    DBMS_OUTPUT.PUT_LINE('3: '||RAWTOHEX(TEMP3));
  END LOOP;
  CLOSE TEMP;
END;
/

Se MAKE_TEMPfosse a função que estou tentando usar, precisaria saber que ela tem 3 valores por linha e que os valores são a VARCHAR2, a NUMBERe a RAWnessa ordem para que eu possa definir variáveis ​​para armazenar os resultados.

Estou usando o Oracle 11.2.0.1.0, embora eu agradeça se também existam respostas para versões mais recentes.

Por que não posso simplesmente olhar para a fonte

Se você precisa saber, a função faz parte do Oracle ASP.NET Membership Provider e foi escrita pela Oracle. Estou tentando depurar um problema e gostaria de ver o que esta função está retornando. O código-fonte da função começa assim:

create or replace 
FUNCTION  "ORA_ASPNET_MEM_GETALLUSERS" wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
33

Como você pode ver, será (na melhor das hipóteses) extremamente difícil extrair qualquer informação sobre o cursor disso.

Considere a seguinte tabela:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL 
---+----------+-----------+-----------+-----
1  | 1        | 1         | (null)    | 3   
2  | 1        | 2         | (null)    | 2   
3  | 1        | 3         | (null)    | 1   
4  | 1        | 4         | 4         | 2   
5  | 1        | 5         | (null)    | 1   
6  | 2        | 1         | (null)    | 4   
7  | 2        | 2         | 2         | 3   
8  | 2        | 3         | (null)    | 4   
9  | 2        | 4         | (null)    | 2   
10 | 2        | 5         | (null)    | 2   
11 | 2        | 6         | (null)    | 4   
12 | 2        | 7         | 14        | 2   
13 | 2        | 8         | (null)    | 2   

Para cada linha, preciso calcular a soma cumulativa de VALtodas as linhas anteriores (ordenadas por ORDER_VALe agrupadas por GROUP_ID), mas cada vez que um não- NULL RESET_VALé encontrado, preciso usar esse valor para a soma. As linhas a seguir também precisam ser construídas em RESET_VALvez de usar a soma real. Observe que cada grupo pode ter vários valores de redefinição.

Este é o resultado que espero para a tabela acima:

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1  | 1        | 1         | (null)    | 3   | 0
2  | 1        | 2         | (null)    | 2   | 3
3  | 1        | 3         | (null)    | 1   | 5
4  | 1        | 4         | 4         | 2   | 4
5  | 1        | 5         | (null)    | 1   | 6
6  | 2        | 1         | (null)    | 4   | 0
7  | 2        | 2         | 2         | 3   | 2
8  | 2        | 3         | (null)    | 4   | 5
9  | 2        | 4         | (null)    | 2   | 9
10 | 2        | 5         | (null)    | 2   | 11
11 | 2        | 6         | (null)    | 4   | 13
12 | 2        | 7         | 14        | 2   | 14
13 | 2        | 8         | (null)    | 2   | 16

Se não fosse pelos valores redefinidos, eu poderia usar uma consulta de janela:

SELECT temp.*,
       COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                0) AS cumsum
FROM temp;

SQLFiddle acima

Originalmente, pensei erroneamente que poderia apenas colocar RESET_VALno início do COALESCE, mas isso não funcionará, pois não redefinirá o valor das linhas seguintes.

Eu também tentei esta solução , mas ela redefine apenas para zero, não para um valor na coluna. Adaptá-lo para fazer isso não é trivial porque esse valor deve se propagar para todas as linhas seguintes.

Uma consulta recursiva parece um ajuste natural, mas ainda não consegui descobrir como fazer isso.

Provavelmente devo mencionar que a tabela com a qual realmente tenho que lidar é muito maior (centenas de milhares a alguns milhões de linhas) do que o exemplo acima, portanto, mencione se houver alguma armadilha de desempenho com qualquer resposta.

Considere a seguinte tabela:

T_ID | T_START | T_END
-----+---------+------
   1 | 0.25    | 0.5
   2 | 0.8     | 1
   3 | 0.4     | 0.6
   4 | 0.2     | 0.3
   5 | 0.7     | 0.8

T_IDé único. Cada linha representa um intervalo contínuo de números e é um subconjunto de 0 a 1. T_STARTé menor que T_END.

Preciso identificar quaisquer intervalos não incluídos entre 0 e 1. Observe que alguns intervalos se sobrepõem. A exclusividade dos endpoints não é relevante para meu caso de uso; Eu só preciso identificar quais são os pontos finais das lacunas. (Como tal, lacunas de ponto único não são consideradas.)

Para este conjunto de dados específico, eu esperaria que o resultado fosse

GAP_START | GAP_END
----------+--------
0         | 0.2
0.6       | 0.7

O conjunto de dados real é grande e será agregado sobre alguns outros dados (centenas de milhares de linhas, com possivelmente 100 linhas por grupo agregado), portanto, o desempenho é importante. (Respostas que não têm bom desempenho, mas podem ser melhoradas, são bem-vindas.)

Considerei tentar primeiro determinar quais são os intervalos cobertos e, em seguida, tentar reverter isso, mas não consegui descobrir como calcular os intervalos cobertos. Um simples GROUP BYé insuficiente, pois temos cadeias de intervalos sobrepostos que seriam mesclados em um único intervalo, embora nem todos os intervalos se sobreponham. Acredito que uma consulta recursiva pode ser útil, mas ainda não consegui descobrir a lógica para isso.

Eu criei um SQLFiddle com este conjunto de dados de exemplo .

Infelizmente (e improdutivamente), não tenho liberdade para modificar a representação subjacente.

O Oracle permite algumas sintaxes diferentes para especificar onde encontrar seu serviço de banco de dados. O que eu vi com mais frequência (nos poucos anos em que desenvolvi software profissionalmente) é mais ou menos assim:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice)))

Não acho que essa sintaxe tenha um nome específico, mas é o que o Oracle gera em seus arquivos TNS se você usar ferramentas gráficas para modificá-la. Mas o Oracle também permite a seguinte sintaxe muito mais compacta, pelo menos com ferramentas como SQL*Plus e aplicativos de mergulho:

myserver:1521/myservice

Isso é conhecido como Easy Connect e li que foi introduzido pelo Oracle 10g. Isso faz com que tenha cerca de 10 anos. Não tenho certeza se isso pode ser usado em um arquivo TNS e, por mais curto e simples que seja, não vejo muita necessidade de colocá-lo em um arquivo TNS. O que é bom nisso, na minha opinião: você pode usá-lo diretamente com seu aplicativo, em vez de precisar manter um arquivo TNS.

No entanto, pelo trabalho que fiz e pelos clientes com quem trabalhei, seu uso parece ser bastante incomum. A única razão pela qual eu sei disso é porque a ESRI começou a recomendar seu uso para estabelecer conexões em versões mais recentes do ArcGIS. Isso me deixa pensando por que é incomum, então há algum tipo de desvantagem em usar o Easy Connect que estou perdendo?

(Eu agradeceria se alguém pudesse adicionar mais tags. Não consegui pensar/encontrar mais nada que fizesse sentido.)

Estou trabalhando com o módulo arcpy da ESRI e preciso especificar uma WHEREcláusula como parâmetro para esta função . Como o resultado será eventualmente usado para recuperar dados do banco de dados, o texto da WHEREcláusula é passado diretamente para o banco de dados em algum momento, mas, infelizmente, o ESRI não me dá nenhuma opção para parametrizar a consulta. Como quero me proteger contra uma possível injeção de SQL, preciso encontrar algum meio alternativo de proteger meu banco de dados.

Uma opção que vem à mente é limitar a entrada e lançar um erro antes dessa chamada de função se ela não estiver em conformidade. No meu caso, os chamadores precisarão apenas de caracteres alfanuméricos ASCII. Nenhum outro tipo de caractere é permitido na coluna a ser usada para filtragem. Limitar o usuário a apenas caracteres alfanuméricos seria suficiente para impedir a injeção de SQL, especialmente porque o texto deve ser citado como uma string?

Pela primeira vez, me encontro totalmente perdido em termos de encontrar um bom título de pergunta.

Considere as três tabelas a seguir. Cada um tem colunas adicionais, mas apenas as colunas relevantes para os relacionamentos entre as tabelas são mencionadas:

• A tabela atem uma chave primáriaa_id
• A tabela btem uma chave primária b_ide uma coluna a_idque é uma chave estrangeira para a(relação um-para-muitos)
• A tabela referencetem uma chave primáriaref_id

referenceé uma lista de fontes que são citadas para substanciar as informações contidas nas outras 2 tabelas. Como tal, preciso de tabelas para relacioná-los. Ambos ae btêm relacionamentos muitos-para-muitos com reference, mas há uma complicação adicional: um número de nota de rodapé. Este número de nota de rodapé deve ser único para cada par ae reference, mas só é necessário quando existe uma relação entre a be o mesmo reference. (Claro, isso bdeve estar relacionado ao mesmo a.) A melhor maneira que encontrei para representar esse relacionamento até agora é um par de tabelas:

bridge_a_reference:

• Colunas: a_id, ref_id, footnote_num(anulável)
• Chave primária: a_id,ref_id
• Chaves estrangeiras: a_idpara tabela a; ref_idpara mesareference

bridge_b_reference:

• Colunas: b_id, a_id,ref_id
• Chave primária: b_id,ref_id
• Chaves estrangeiras: b_id, a_idpara tabela b; a_id, ref_idà mesabridge_a_reference

Isso garante a consistência dos dados. Só pode haver uma relação entre be referencese uma relação correspondente entre ae referenceexistir e bestiver relacionada com o correto a. Também garante que o número da nota de rodapé seja consistente para todos os pares a/ . referenceNo entanto, é redundante, armazenando a relação entre ae buma segunda vez (embora imposta para ser consistente, pelo menos) e não garante que o número da nota de rodapé seja necessário para as relações b/ reference.

Eu tentei reorganizar as tabelas na minha cabeça algumas vezes, mas tudo o mais que eu posso criar permite algum tipo de inconsistência ( bpara relacionamentos sem o referencecorrespondente ou apara números de notas de rodapé inconsistentes para os pares / ). estruturar minhas tabelas de ponte para eliminar a redundância e garantir os requisitos de nota de rodapé? É mesmo possível?referencebaareference

O PostgreSQL possui o comando CLUSTER para agrupar linhas fisicamente no disco. Ao agrupar as informações em que as linhas "vizinhas" (por falta de um termo melhor) são frequentemente acessadas juntas, o desempenho melhora, pois menos blocos de disco precisam ser lidos em uma determinada consulta. A Oracle tem algo parecido? Isso ajudaria no desempenho em uma tabela grande que quase nunca é atualizada se houver essa opção?

Eu tenho uma tabela Bcom uma chave estrangeira para tabela A. Eu quero DELETEalgumas linhas na tabela Be também quero DELETEsuas linhas pai na tabela A. No entanto, os critérios de exclusão são baseados na tabela B. As linhas na tabela Anão podem ser excluídas primeiro porque a referência na tabela Brestringe a exclusão, mas também preciso obter as chaves das Alinhas para excluir em B.

Aqui está um SQLFiddle com uma estrutura de tabela de exemplo: http://sqlfiddle.com/#!4/f156c/4/0 .

Minha primeira inclinação foi tentar salvar as chaves SELECTinserindo-as de Bem uma variável e, em seguida, usá -las em DELETEde A.

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

Mas isso só dá um PLS-00382: expression is of wrong typeerro. O erro em si vem DELETEdo A; Eu sei disso porque se eu comentar, o bloco será executado.

Como posso contornar o expression is of wrong typeerro ou qual é outra maneira de abordar isso?

Versão do Oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

(Sim, estou bem ciente de quantos anos isso tem. A escolha do banco de dados do cliente, não nossa.)

Eu tenho uma tabela onde as linhas podem estar relacionadas entre si e, logicamente, a relação ocorre nos dois sentidos (basicamente, é sem direção) entre as duas linhas. (E se você está se perguntando, sim, isso realmente deveria ser uma tabela. São duas coisas exatamente da mesma entidade/tipo lógico.) Posso pensar em algumas maneiras de representar isso:

1. Armazene a relação e seu reverso
2. Armazene o relacionamento de uma maneira, restrinja o banco de dados de armazená-lo de outra maneira e tenha dois índices com ordens opostas para os FKs (um índice sendo o índice PK)
3. Armazene o relacionamento de uma maneira com dois índices e permita que o segundo seja inserido de qualquer maneira (parece meio nojento, mas ei, completude)
4. Crie algum tipo de tabela de agrupamento e tenha um FK para ela na tabela original. (Levanta muitas questões. A tabela de agrupamento teria apenas um número; por que ter a tabela? Tornar FK NULLable ou ter grupos com uma única linha associada?)

Quais são alguns dos principais prós e contras dessas formas e, claro, existe alguma maneira que eu não tenha pensado?

Aqui está um SQLFiddle para brincar: http://sqlfiddle.com/#!12/7ee1a/1/0 . (Acontece que é o PostgreSQL, já que é o que estou usando, mas não acho que essa pergunta seja muito específica do PostgreSQL.) Atualmente, ele armazena o relacionamento e seu reverso apenas como exemplo.