personal blog
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;
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
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))
;
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
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;
cat file | egrep -v '(^#|^\s*$|^\s*\t*#)'
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;
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'
exec dbms_sqltune.alter_sql_profile ( NAME => 'SYS_SQLPROF_0251acbeeb080004', attribute_name => 'NAME', VALUE => 'FIX_FTS_ON_SMEVLOG');
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)