Chmelej's Blog

Většinu času jsem bojoval s Oracle 11G a tady jsem sesumíroval poznamky a poznatky na které jsem narazil.

Zahozeni hacku a carek

lower(convert(s.street,'US7ASCII'))

Funkce decode

select decode(country,'Finland','FI','Czech Republic', 'CZ',...) from ...

Dotaz MERGE

MERGE INTO uni_listing dst 
USING ( select * from ... ) src 
    ON (dst.uni_listing_id = src.uni_listing_id) 
WHEN MATCHED THEN 
    UPDATE SET dst.url = src.key_value 
WHEN NOT MATCHED THEN 
    INSERT (international_phone, cdc_status, cdc_last_update) 
    VALUES (src.international_phone, src.recdc_status, src.cdc_last_update);

Jak vytahnout data pre xpath

SELECT ad_id, extract(xmltype(string_value),'/openingHours/note/text()') 
FROM ad_attribute 
WHERE attribute_definition_id = 1011 AND string_value is not null AND existsNode(xmltype(string_value),'/openingHours/note/text()') = 1;

Rozsirene agregacni fce*

SELECT row_number() OVER (PARTITION BY zzz ORDER BY qqq ) rn FROM xxx;

Hromadné povoleni constraintu

SELECT 'ALTER TABLE '|| table_name ||' ENABLE CONSTRAINT '|| constraint_name || ';' 
FROM user_constraints WHERE constraint_type = 'R' AND status = 'DISABLED';

Jak zjistit velikost tabulek

select segment_name, sum(bytes)/1024/1024 "M" from user_segments group by segment_name order by sum(bytes), segment_name;

-- velikost databaze podle schema
SELECT round(sum(bytes)/1024/1024/1024,2) data_GB, owner FROM dba_segments group by owner

-- velikost databaze vsechny data
SELECT round(sum(bytes)/1024/1024/1024,2) data_GB FROM dba_segments 

Truncate table

Aby sla tabulka smaznout pres truncate tak je nutne mit vypnute vsechny FK to lze udelat pres

begin 
    for cur in (select owner, constraint_name , table_name from all_constraints	where owner = 'CDC' and	TABLE_NAME = 'XXX' and constraint_type = 'R') 
    loop 
        execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE'; 
    end loop; 
end;

Jenom pozor pri zapinani constraintu je potreba aby existoval nadrazeny constraint (napr coz byva PK, ale ten se nemusi volat jako prvni)

Start db serveru

sudo su - oravyvoj
sqlplus / as sysdba 
shutdown abort;
startup; ^D 
$ lsnrctl start

Jak vytahnout vsechny indexy

SELECT * FROM all_ind_columns WHERE index_name IN ( 
    SELECT index_name FROM all_indexes WHERE index_type not like 'FUN%' AND index_type not like 'IOT%' 
        AND owner in ('CDL','CDC','CDA','TUMOR','MMC')
    ) ORDER BY index_name, column_position

Locked User

kdyz pouzivam connection pool a do konfigurace zadam spatny heslo, tak pool zacne bombardovat databazi pokusy o prihlaseni, naces oracle ucet zablokuje. odblokovat se to da v EM > Server > Security Users > Action "Unlock User"

Merge nejde prerusit!"

Merge nejde prerusit, resp pokud zabiju program uprostred merge tak operace zustava viset na serveru a musi se potom rucne zabit prispusna session!

SELECT username, sid||','||SERIAL# sid, status, osuser, process, machine 
    FROM v$session WHERE username = 'CDM_BE' AND status = 'ACTIVE';

ALTER SYSTEM KILL SESSION 'sid,serial#';

Grant

SELECT 'GRANT SELECT ON '||owner||'.'||table_name||' TO CDM_RU;' FROM all_tables WHERE owner = 'CDM_FI' AND not regexp_like(table_name,'\$');

Oracle Link

SELECT * FROM user_db_links;

CREATE DATABASE LINK CDM_RU_PROD 
    CONNECT TO CDM_RU IDENTIFIED BY CDM_RU_PROD 
    USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2103.mediatel.cz)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MEDIATEL)))';

CREATE TABLE std_address_copy AS SELECT * fROM std_address@cdm_ru_prod;

Jak prohledat nazvy sloupecku ve vsech tabulkach

SELECT * FROM dba_tab_columns WHERE column_name like '%ZIP%' and owner = 'SLPROD'

Kontrola po importu dat

-- vypise vsechny nevalidni objekty 
SELECT * FROM user_objects WHERE status!='VALID'; 

-- na par nevalidnich view, je dobre po importu spustit toto
execute SYS.DBMS_UTILITY.COMPILE_SCHEMA('CDM_SK')

Vytvoreni uctu pro pristup k DB

tento ucet (nema vlastni tabulky)

CREATE USER cdm_lv_app IDENTIFIED BY cdm_lv_app;
GRANT CREATE SESSION To cdm_lv_app;

import/export dat

nastroje pouzivaji preddefinovane adresare DIRS:

select * from dba_directories;

Jak prepocitat statistiky:

-- aby statistika mela smysl musi byt v tabulce data

-- odemknuti statistik
execute dbms_stats.UNLOCK_TABLE_STATS('CDM_HR','MD_IMPORT_LOG');

-- nalezeni zamknutych statistik
select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

-- pregeneruje statistiky co nebyly kontrolovany 3 avic dni
select ' execute dbms_stats.gather_table_stats(''CDM_HR'',''' || table_name || ''',cascade=>true);'  from user_tables where LAST_ANALYZED  *Oprava vadnych objektu*  Spool recompile.sql Select 'alter '||object_type ||' '|| object_name ||' compile;' From user_objects Where status <> 'VALID' And object_type IN ('VIEW','SYNONYM', 'PROCEDURE','FUNCTION', 'PACKAGE','TRIGGER');

Jak zjistit ktery constrant je vlastne porusen

SELECT sc.constraint_name, sc.table_name slave_table, mc.table_name  master_table 
FROM user_constraints sc  
JOIN user_constraints mc ON sc.r_constraint_name = mc.constraint_name 
WHERE sc.constraint_type = 'R' 

Logovani v PLSQL

SET SERVEROUTPUT ON BEGIN dbms_output.put_line('Output from PL/SQL...'); END;

Divný chyby

• ORA-01461: can bind a LONG value only for insert into a LONG column

  • do VARCHAR2(4000) se pokousim vlozit delsi text nez 4000 znaku!

• ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

  • z nejakeho duvodu maji byt LOBy pri vkladani na konci dotazu, takze to chce preoriontovat dotaz.

• ORA-01882: timezone region not found

  • mi to pise pri pokusu pripojit se k Oracle9 v Bukuresti
  • pomuze pridat parametr java -Duser.timezone=Europe/Warsaw
  • seznam podporovanych variant: SELECT * FROM V$TIMEZONE_NAMES WHERE tzname LIKE 'Europe/%'

• ORA-21561: OID generation failed

  • Oracle client se nechce pripojit. Make sure your host name and the name asociated to 127.0.1.1 ip address in /etc/hosts are the same:

# hostname
    desktop-machine 
# grep 127.0.1.1 /etc/hosts
    desktop-machine  

Oracle a Java, Groovy

• V groovy jsem vytvoril statement s vice otazniky ? a jdbc driver hazi tuto divnou chybu: java.lang.ArrayIndexOutOfBoundsException: 9 at oracle.jdbc.driver.OracleSql.computeBasicInfo(OracleSql.java:950) at oracle.jdbc.driver.OracleSql.getSqlKind(OracleSql.java:623) at oracle.jdbc.driver.OraclePreparedStatement.(OraclePreparedStatement.java:1212) at oracle.jdbc.driver.T4CPreparedStatement.(T4CPreparedStatement.java:28)

  • pomohl uprade ojdbc6.jar na novejsi verzi.

• Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #9 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: *Invalid column type: 1111* ; uncategorized SQLException for SQL []; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111

  • ibatis hazi tuhle chybu, znamena to ze v dotazu u parametru musi byt nastaveny jdbcType neb ho nelze urcit automaticky (volam to s null)

Jak se koukat na data zpet v case

• na produkci lze koukat az 7dni zpet

SELECT * FROM AD_ATTRIBUTE_P AS of timestamp to_timestamp('2017-01-27 14:28:00','yyyy-mm-dd hh24:mi:ss') 
WHERE ad_id = 48255341

### Prava potrebna pro debug
Kdyz to hazi ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68 ORA-06512: at line 1

GRANT DEBUG ANY PROCEDURE TO CDM_BE; grant DEBUG CONNECT SESSION to CDM_BE; grant execute on DBMS_DEBUG_JDWP to CDM_BE; – tohle mi sice neproslo ale uz debuguju :-) listagg(, ‘,') within group (order by ) as