Skip to content

SQLT ORA-06598: insufficient INHERIT PRIVILEGES privilege

SQLT ORA-06598: insufficient INHERIT PRIVILEGES privilege published on Комментариев к записи SQLT ORA-06598: insufficient INHERIT PRIVILEGES privilege нет

Hi, faced with this error while generate report to Oracle support:

BEGIN SQLTXADMIN.sqlt$i.xtract(p_statement_id => :v_statement_id, p_sql_id_or_hash_value => '1v4z8fymw07t8', p_password => 'Y'); END;

*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SQLTXADMIN.SQLT$I", line 1
ORA-06512: at line 1

solution is:

grant INHERIT ANY PRIVILEGES to sqltxadmin;

ORA-03297 how-to find how data distributed

ORA-03297 how-to find how data distributed published on Комментариев к записи ORA-03297 how-to find how data distributed нет

original

column segment heading 'Segment Name' format a14
column file_name heading 'File Name' format a50
column segment_type heading 'Segment Type' format a10
select
            tablespace_name,
            file_id,
            file_name,
            dfsizeMB,
            hwmMB,
            dffreeMB,
            trunc((dffreeMB/dfsizeMB)*100,1) "% Free",
            trunc(dfsizeMB-hwmMB,2) "Resizeble"
   from
   (
      select
           df.tablespace_name tablespace_name,
           df.file_id file_id,
           df.file_name file_name,
           round (df.bytes/1024/1024) dfsizeMB,
           round((ex.hwm*(ts.block_size))/1024/1024) hwmMB,
           dffreeMB
      from
           dba_data_files df,
           dba_tablespaces ts,
      (
           select file_id, round(sum(bytes/1024/1024)) dffreeMB
           from dba_free_space
           group by file_id
      ) free,
      (
           select file_id, max(block_id+blocks) hwm
           from dba_extents
           group by file_id
      ) ex
      where df.file_id = ex.file_id
      and df.tablespace_name = ts.tablespace_name
      and df.file_id = free.file_id (+)
      order by df.tablespace_name, df.file_id
    ) 
/


TABLESPACE_NAME      |    FILE_ID | File Name                                          |   DFSIZEMB |      HWMMB |   DFFREEMB |     % Free |  Resizeble
-------------------- | ---------- | -------------------------------------------------- | ---------- | ---------- | ---------- | ---------- | ----------
ADMSF_TS             |         14 | +DATA/S00TESIA2/DATAFILE/admsf_ts.292.924278433    |        100 |         27 |         87 |         87 |         73
ESIA                 |          5 | +DATA/s00tesia2/datafile/esia.262.911677179        |      32767 |      32767 |      25380 |       77.4 |          0
ESIA                 |         12 | +DATA/s00tesia2/datafile/esia.261.911673773        |      32768 |      32768 |      20200 |       61.6 |          0
ESIA                 |         13 | +DATA/s00tesia2/datafile/esia.265.911681735        |       3100 |       3100 | <NULL>     | <NULL>     |          0
ESIA_PDS             |          8 | +DATA/s00tesia2/datafile/esia_pds.271.911682045    |        100 |          6 |         99 |         99 |         94
ESIA_TECH            |          6 | +DATA/s00tesia2/datafile/esia_tech.269.911682035   |        100 |          9 |         93 |         93 |         91
SIA_DB               |         10 | +DATA/s00tesia2/datafile/sia_db.266.911681879      |       1260 |       1199 |         61 |        4.8 |         61
SYSAUX               |          2 | +DATA/s00tesia2/datafile/sysaux.264.911681479      |       4096 |       1591 |       2519 |       61.4 |       2505
SYSTEM               |          1 | +DATA/s00tesia2/datafile/system.267.911681965      |        890 |        886 |         57 |        6.4 |          4
UNDOTBS1             |          3 | +DATA/s00tesia2/datafile/undotbs1.268.911681999    |        910 |        620 |        884 |       97.1 |        290
USERS                |          4 | +DATA/s00tesia2/datafile/users.263.911680523       |       9069 |       8587 |       9030 |       99.5 |        482

column segment heading 'Segment Name' format a40
column file_name heading 'File Name' format a50
column segment_type heading 'Segment Type' format a20
select
                file_name,
                segment_type,
                owner||'.'||segment_name segment,
                block_id,
                blockIdMB
        from
        (
         select
              ex.owner owner,
              ex.segment_name segment_name,
              ex.segment_type segment_type,
              ex.block_id block_id,
              df.file_name file_name,
              trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
      from
              dba_extents ex, dba_data_files df, dba_tablespaces ts
              where df.file_id = &file_id
              and df.file_id = ex.file_id
              and df.tablespace_name = ts.tablespace_name
              order by ex.block_id desc
      )
      where rownum <= 100
/


File Name                                          | Segment Type         | Segment Name                             |   BLOCK_ID |  BLOCKIDMB
-------------------------------------------------- | -------------------- | ---------------------------------------- | ---------- | ----------
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194168 |   32766.93
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194160 |   32766.87
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_INT_R_OBJ                   |    4194152 |   32766.81
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194144 |   32766.75
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194136 |   32766.68
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194128 |   32766.62
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194120 |   32766.56
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194112 |    32766.5
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194104 |   32766.43
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194096 |   32766.37
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194088 |   32766.31
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194080 |   32766.25
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194072 |   32766.18
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194064 |   32766.12
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194056 |   32766.06
+DATA/s00txxxx2/datafile/xxxx.262.911677179        | INDEX SUBPARTITION   | XXXX.AUD_EVT_CRT_ON                      |    4194048 |      32766

how to get ash report from sqlplus

how to get ash report from sqlplus published on Комментариев к записи how to get ash report from sqlplus нет
Select *
    from table(dbms_workload_repository.ASH_report_html(
             l_dbid => 2316837477,
             l_inst_num => 1,
             l_btime =>  TIMESTAMP'2016-11-14 14:36:00' ,
             l_etime =>  TIMESTAMP'2016-11-14 14:41:00' ,
             l_options => 0,
             l_slot_width => 0,
             l_sid => null,
            l_sql_id => null,
            l_wait_class => null,
            l_service_hash => null,
            l_module => null,
            l_action => null,
            l_client_id => null,
            l_plsql_entry => null))
   ;

Upload csv files to postgresql database

Upload csv files to postgresql database published on Комментариев к записи Upload csv files to postgresql database нет

here is my drafts of how-to automate upload a lot of csv files to database ( use text fieds because there is no need to mach all types of columns in this case )

 for i in $(ls -1 *.csv); do echo "create table $i (" ; echo $(head -1 $i | sed 's/,/ text,\n/g' ); echo "text );" ;echo copy $i | awk -F "." '{print $1}'; echo "from '/u01/import/$i' DELIMITER ',' CSV HEADER;" ; done

postgresql script to find excess indexes

postgresql script to find excess indexes published on Комментариев к записи postgresql script to find excess indexes нет

here how excess indexes can be found, excess mean that there is a single column index which is covered by first value of complex index

select * from (
select count(*),pg_size_pretty(pg_relation_size(t.table_name)),t.table_name,t.indkey_names[1],min(array_length(t.indkey_names,1)) min_index_elements_cnt from (
SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass table_name,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
where idx.indrelid::text not like 'pg_toast%' 
and pg_relation_size(indexrelid::regclass)>100*1024*1024 ) t
group by t.indkey_names[1],t.table_name
having count (*) >1
) tt where min_index_elements_cnt =1;

oracle how to get disk event waits estimates

oracle how to get disk event waits estimates published on Комментариев к записи oracle how to get disk event waits estimates нет

base took from ASH Architecture and Advanced Usage — RMOUG

select event,round (est_dbtime_ms/
CASE WHEN est_waits=0 THEN est_dbtime_ms ELSE est_waits END
,1) as est_avg_latency_ms,time#
from (
      select event,round(sum(case when time_waited >0 then greatest(1,1000000/time_waited) else 0 end )) as est_waits,
      sum(1000) as est_dbtime_ms ,TRUNC( SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  SAMPLE_TIME), 1) /(24 * 60) as time#
      from v$active_session_history where  wait_class ='User I/O' 
      group by event,TRUNC( SAMPLE_TIME, 'MI') - mod( EXTRACT(minute FROM  SAMPLE_TIME), 1) /(24 * 60) 
) order by 3 desc;

postgresql configure separate directory for config files

postgresql configure separate directory for config files published on 1 комментарий к записи postgresql configure separate directory for config files

first of all we need to create directory for config files outside $PGDATA:
in my case this is /u01/postgres/9.5/main/conf/

next we need to modify service and add option with path of postgresql.conf

vim /usr/lib/systemd/system/postgresql-9.5.service
[Unit]
Description=PostgreSQL 9.5 database server
After=syslog.target
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=/u01/postgres/9.5/main/pgdata

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

ExecStartPre=/usr/pgsql-9.5/bin/postgresql95-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 -o "-c config_file=/u01/postgres/9.5/main/conf/postgresql.conf"
ExecStop=/usr/pgsql-9.5/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.5/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

in config file we modify location of other config files:

data_directory='/u01/postgres/9.5/main/pgdata'
hba_file='/u01/postgres/9.5/main/conf/pg_hba.conf'
ident_file='/u01/postgres/9.5/main/conf/pg_ident.conf'

postgresql query to find duplicate indexes

postgresql query to find duplicate indexes published on Комментариев к записи postgresql query to find duplicate indexes нет
     
SELECT sub.table, pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
     (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
     (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4 
            FROM (  SELECT indrelid::regclass as table,indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| 
                    COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY 
                    FROM pg_index
                     where  pg_relation_size(indexrelid::regclass)>100*1024*1024 ) sub 
GROUP BY sub.table, KEY HAVING COUNT(*)>1 
ORDER BY SUM(pg_relation_size(idx)) DESC;

sample output

       table       |  size  |            idx1             |                idx2                | idx3 | idx4
-------------------+--------+-----------------------------+------------------------------------+------+------
 mdm_record_column | 797 MB | mdm_record_column_column_fk | mdm_record_column_record_fk        |      |
 fin_bill_generate | 300 MB | fin_bill_generate_fk        | fin_bill_generate_spec_item_id_idx |      |

also usefull to look at usage stat on this indexes

SELECT 
    relid::regclass AS table, 
    indexrelid::regclass AS index, 
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, 
    idx_tup_read, 
    idx_tup_fetch, 
    idx_scan,
    pg_get_indexdef(pg_index.indexrelid) as indexdef
FROM 
    pg_stat_user_indexes 
    JOIN pg_index USING (indexrelid) 
WHERE 
     (relid::regclass)::text ='mdm_record_column' order by idx_scan desc ;

       table       |              index              | index_size | idx_tup_read | idx_tup_fetch | idx_scan |                                         indexdef
-------------------+---------------------------------+------------+--------------+---------------+----------+-------------------------------------------------------------------------------------------
 mdm_record_column | mdm_record_column_record_fk     | 399 MB     |       758024 |        758024 |     2992 | CREATE INDEX mdm_record_column_record_fk ON mdm_record_column USING btree (column_id)
 mdm_record_column | mdm_record_column_record_id_idx | 399 MB     |         1922 |          1922 |      442 | CREATE INDEX mdm_record_column_record_id_idx ON mdm_record_column USING btree (record_id)
 mdm_record_column | mdm_record_column_pk            | 399 MB     |            0 |             0 |        0 | CREATE UNIQUE INDEX mdm_record_column_pk ON mdm_record_column USING btree (id)
 mdm_record_column | mdm_record_column_column_fk     | 399 MB     |            0 |             0 |        0 | CREATE INDEX mdm_record_column_column_fk ON mdm_record_column USING btree (column_id)

Primary Sidebar

Яндекс.Метрика