How to get Partitioning Information of Hive Tables in CDH

Following document has useful information for getting partition information of a hive table.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ShowPartitions

In order to query whole partitioning information on hive databases, hive metadata database can be used. It is in the MySQL server. Just login to MySQL server, switch to hive schema/database.

Following queries print partitioning summary and details

select db.name,t.owner,t.tbl_name,t.tbl_type, count(tp.part_name)
from TBLS t, DBS db, PARTITIONS tp where t.db_id=db.db_id
and t.tbl_id=tp.tbl_id
group by db.name,t.owner,t.tbl_name,t.tbl_type
order by 1,2,3;

select db.name,t.owner,t.tbl_name,t.tbl_type, tp.part_name
from TBLS t, DBS db, PARTITIONS tp where t.db_id=db.db_id
and t.tbl_id=tp.tbl_id
order by 1,2,3,5;

Following query prints bucketed table information

select d.name,t.owner, t.tbl_name,b.bucket_col_name,s.num_buckets
from BUCKETING_COLS b, SDS s, COLUMNS_V2 c, TBLS t, DBS d
where b.sd_id=s.sd_id and s.cd_id=c.cd_id and b.bucket_col_name=c.column_name and s.sd_id=t.sd_id
and t.db_id=d.db_id
order by 1,2,3,4;

ODI-10165: The RunAsUser MyUser is not a valid user from external user identity store

This error occured after i switched authentication mode of ODI repository to external (Microsoft Active Directory) authentication. While developers could run scenarios successfully using configured agent, ODI-10165 was occuring for me.

The problem is that while i was switching authentication mode of ODI to external authentication i added my user to supervisor user list. My user was authenticating as superuser bu not in the security/users list. This causes problem. After i added my user to security/users, problem solved.

Using Debug Mode in Sql Developer with KERBEROS Debug

Sql developer debug mode can be enabled with 2 steps:

1-) Set Log Directory path in Tools->Preferences->Environment

2-) At sqldeveloper\sqldeveloper\bin directory edit sqldeveloper.conf file, comment out "IncludeConfFile  sqldeveloper-nondebug.conf" and "IncludeConfFile  sqldeveloper-debug.conf"

#IncludeConfFile  sqldeveloper-nondebug.conf
IncludeConfFile  sqldeveloper-debug.conf

Restart sql developer. You will see that debug logs will be created under log directory with the name format SQLDeveloper_YYYYMMDDHH24MISS.log

In order to add kerberos debugging you shoud add following java option to sqldeveloper.conf file at sqldeveloper\sqldeveloper\bin directory

AddVMOption -Dsun.security.krb5.debug=true

However kerberos debug information will not be written to sql developer debug log file. It is send to stdout. In order to get kerberos debug output you should start sql developer from command line. Set screen buffer size of cmd window to some value higher then the system default so some debug output will not be lost because of small buffer size. 

Sql Developer: How to set up Hive connection with Kerberos authentication on Windows client

Windows by default has restriction to retrieve TGT session key. Following registry values should be set in order to allow applications:

Windows 2003, Windows Vista, and later:

• Key:HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters

• Value Name: allowtgtsessionkey

• Value Type: REG_DWORD

• Value: 0x01

Windows XP and Windows 2000:

• Key: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos

• Value Name: allowtgtsessionkey

• Value Type: REG_DWORD

• Value: 0x01

Sql developer 4.0.3 and later supports hive connection. My version is 4.1.1.19

Hive JDBC drivers should be downloaded and registered with sql developer as Third Party JDBC drivers. My hadoop distribution is cloudera version is 2.5.4. I downloaded the driver from following URL:

hive-jdbc-v2-5-4.html

Here is how it looks like:

"Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files " should be downloaded. You should extract (overwrite) files local_policy.jar and US_export_policy.jar into sqldeveloper/jdk/jre/lib/security directory. 

krb5.conf is also needed by java for kerberos authentication. if KRB5_CONFIG is not set then it first looks into jre/lib/security then %WINDIR% directory. You should create this file under sqldeveloper/jdk/jre/lib/security (the same directory you put local_policy.jar and US_export_policy.jar files). Parameters in this file depends on your KDC configuration. In general /etc/krb5.conf file on any hadoop cluster node works fine on windows too. My krb5.conf file looks like this:

[libdefaults]
default_realm = MYDOMAIN.COM
default_tgs_enctypes = aes256-cts aes128-cts arcfour-hmac-md5 des-cbc-md5 des-cbc-crc
default_tkt_enctypes = aes256-cts aes128-cts arcfour-hmac-md5 des-cbc-md5 des-cbc-crc
permitted_enctypes = aes256-cts aes128-cts arcfour-hmac-md5 des-cbc-md5 des-cbc-crc
dns_lookup_realm = true
dns_lookup_kdc = true
passwd_check_s_address = false
noaddresses = true
udp_preference_limit = 1
ccache_type = 3
kdc_timesync = 1
[domain_realm]
.mydomain.com = MYDOMAIN.COM
mydomain.com = MYDOMAIN.COM
kdcserver.mydomain.com = MYDOMAIN.COM
[realms]

Now, you can create a Hive connection with Kerberos authentication on Windows client:

Oracle Enterprise Manager HTTPS Access Certificate Problem

In order to avoid certification problem for oracle enterprise manager https access, read following doc

Installing Browser Certificates

Moving Datafile from File System to ASM

If datafile is created on file system instead of ASM, following procedure can be applied to move datafile to ASM


SQL > select NAME,STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                       STATUS         ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn         ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME .nnn.nnn         ONLINE         READ WRITE

SQL > ALTER TABLESPACE TS_NAME ADD DATAFILE 'DATA' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 10M;

NAME                                                                              STATUS         ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn               ONLINE         READ WRITE

/u01/app/.../DBNAME/dbs/DATA                             ONLINE         READ WRITE  <- asm="" at="" created="" file="" font="" instead="" of="" system=""> 

Take the datafile to offline:

SQL> alter database datafile '/u01/app/.../DBNAME/dbs/DATA' offline drop;

Database altered.

SQL > select NAME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

/u01/app/.../DBNAME/dbs/DATA                        RECOVER       READ WRITE

Copy datafile to ASM using RMAN. If you use asmcmd for file copy then file will be created as non-oracle managed file (with alias)

RMAN> COPY DATAFILE ' /u01/app/.../DBNAME/dbs/DATA' TO '+DATA';

Starting backup at ....

allocated channel...

...

channel ORA_DISK_1: starting datafile copy

input datafile file number=nnnnn name= /u01/app/.../DBNAME/dbs/DATA

output file name=+DATA/DBNAME/datafile/TS_NAME.nnn.nnn  tag=...

channel ORA_DISK_1: datafile copy complete, elapsed time: ...

Finished backup ...

Update file location

SQL> alter tablespace TS_NAME  rename  datafile '/u01/app/.../DBNAME/dbs/DATA' to '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';

Tablespace altered.

Recover datafile and bring it online

SQL> recover datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn';

Media recovery complete.

SQL > alter database datafile '+DATA/DBNAME/datafile/TS_NAME.nnn.nnn' online;

Database altered.

SQL > select NAME,CREATION_TIME, STATUS, ENABLED from v$datafile where TS#=nn

NAME                                                                        STATUS            ENABLED

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

+DATA/DBNAME/datafile/TS_NAME.nnn.nnn          ONLINE         READ WRITE

ORA-00942 while creating materialized view

I hit following problem while creating the materialized view with option refresh fast although   SCHEMA2.TABNAME  exists and SCHEMA1 user has select privilege on  it

CREATE MATERIALIZED VIEW SCHEMA1.MVIEW ....
SELECT .... FROM SCHEMA2.TABNAME....

ORA-12018: following error encountered during code generation for  "SCHEMA1"."MVIEW "
ORA-00942: table or view does not exist

Problem is not the source table itself but the materialized view log table created before. In order to solve the problem you should identify  materialized view log table and give select permission to  materialized view owner

select LOG_TABLE from ALL_MVIEW_LOGS where log_owner=' SCHEMA2' and master='TABNAME'

grant select on  SCHEMA2.LOG_TABLE to  SCHEMA1;

ODI Object Queries

Following query prints mapping folder path:

set serveroutput on

declare
mapping_name varchar2(200):='MAPPING_NAME';
folder_id int;
parent_folder_id int;
folder_name varchar(200);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Mapping: '||mapping_name);
select i_folder into folder_id from SNP_MAPPING where name=mapping_name;
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=folder_id;
while parent_folder_id is not null
loop
dbms_output.put_line('Folder: '||folder_name);
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=parent_folder_id;
end loop;
dbms_output.put_line('Folder: '||folder_name);
end;


Following query prints package folder path:


set serveroutput on
declare
pck_name varchar2(200):='PACKAGE_NAME';
folder_id int;
parent_folder_id int;
folder_name varchar(200);
begin
DBMS_OUTPUT.ENABLE(1000000);
dbms_output.put_line('Pck: '||pck_name);
select i_folder into folder_id from SNP_PACKAGE where pack_name=pck_name;
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=folder_id;
while parent_folder_id is not null
loop
dbms_output.put_line('Folder: '||folder_name);
select i_folder,folder_name,par_i_folder into folder_id, folder_name,parent_folder_id from snp_folder where i_folder=parent_folder_id;
end loop;
dbms_output.put_line('Folder: '||folder_name);
end;

ODI 10g: ORA-00001: unique constraint (PK_EXP_TXT) violated

There are 2 type of logging in SNP_EXP_TXT table. First type has the I_TXT column value ending with "101" and second type has value ending with "010". Two types have their own incremental sequences. First type is for persistent definitions. There are not many rows with this type so this type of rows will not cause trouble. However second type(ending with "010") is used for logging and history text. There may be a lot of rows in this type. When I_TXT value reaches "9999999010" (actually 9999999, ignore last 3 digits) it cycles back to "1010", then this problem may occur if old "010" records have not been purged.


following query will print 9999999010 as result:


select max(I_TXT) from SNP_EXP_TXT where mod(I_TXT,1000)=10


following query prints how many rows each type has:


select mod(I_TXT,1000), count(*) from SNP_EXP_TXT group by mod(I_TXT,1000) order by 1


Let say the row with the I_TXT has first_date value X, then following query will print the value until with cycled sequence will go until "ORA-00001: unique constraint (PK_EXP_TXT) violated " occurs:


select min(I_TXT) from SNP_EXP_TXT where mod(I_TXT,1000)=10 and first_date < X


And to find out last row:


select max(I_TXT) from SNP_EXP_TXT where mod(I_TXT,1000)=10 and first_date > X


Following query prints current sequence number:


select * from SNP_ID where id_tbl= upper('snp_exp_txt')


Here it is explained that there is a “bug” in Oracle Data Integrator that doesn’t clean the SNP_EXP_TXT when the “parent” records are deleted. Following query can be used to find useless recods (i added time and type conditions):


select * from snp_exp_txt where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10
 and not exists
          (select 0
             from (     select distinct I_TXT_TASK_MESS I_TXT
                          from SNP_SESS_TASK_LOG
                   union all
                        select distinct I_TXT_SCEN I_TXT
                          from SNP_SCEN
                   union all
                        select distinct I_TXT_SESS_MESS I_TXT
                          from SNP_SCEN_REPORT
                   union all
                        select distinct I_TXT_DESCRIPTION I_TXT
                          from SNP_SCEN_FOLDER
                   union all
                        select distinct I_TXT_SESS_MESS I_TXT
                          from SNP_SESSION
                   union all
                       select distinct I_TXT_SESS_PARAMS I_TXT
                         from SNP_SESSION 
                   union all
                       select distinct I_TXT_STEP_MESS I_TXT
                         from SNP_STEP_REPORT
                   union all
                        select distinct I_TXT_STEP_MESS I_TXT
                          from SNP_STEP_LOG
                   union all
                        select distinct I_TXT_VAR I_TXT
                          from SNP_VAR_SESS
                   union all
                        select distinct I_TXT_DEF_T I_TXT
                          from SNP_VAR_SESS
                   union all
                        select distinct I_TXT_VAR I_TXT
                          from SNP_VAR_SCEN
                   union all
                         select distinct I_TXT_DEF_T I_TXT
                           from SNP_VAR_SCEN
                   union all
                         select DISTINCT I_TXT as I_TXT     
                          FROM SNP_TXT
                   union all
                         select DISTINCT I_TXT_VAR_IN as I_TXT
                          FROM SNP_VAR
                   union all
                         select DISTINCT I_TXT_VAR_T as I_TXT
                          FROM SNP_VAR_DATA
                  ) UNION_TABLES
              where UNION_TABLES.I_TXT = snp_exp_txt.I_TXT)


You can delete these records (don't forget to take a backup). However, also there may be records which have references. To find out these you can execute previous query with "exists" instead of "not exists". If so look at the following tables to find corresponding records:


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_SESS_MESS from snp_session)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_SESS_PARAMS from snp_session)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_VAR from snp_var_sess)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_DEF_T from snp_var_sess)


select * from SNP_EXP_TXT where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10 and I_TXT in ( select I_TXT_VAR_T from SNP_VAR_DATA)


Sum of all records from these queries should be equal to the number of records returned from the query with unions executed with "exists " instead of "not exists". If not look further in repository tables...


All of them except SNP_VAR_DATA should have no effect on scenario execution. However SNP_VAR_DATA is important because if a variable reused, it takes last value from SNP_EXP_TXT history record. If you delete this, variable cannot get its last value.


In order to find out which variables has history in our range and what are their last values:


select VAR_NAME, max(last_date) from SNP_VAR_DATA where VAR_NAME in (
select VAR_NAME from SNP_VAR_DATA where I_TXT_VAR_T in (select I_TXT from snp_exp_txt where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10))
group by VAR_NAME order by 2 desc


Variables which have history in our range but refreshed recently should not be effected by deleting the rows in SNP_EXP_TXT within our time range. However if variable has not been refreshed recently (max(last_date) is within our time range) then these variable should be refreshed manually.


Then it is safe to delete referenced rows from SNP_EXP_TXT table. History records will print null for these expressions (do not forget to tale backup)


delete from snp_exp_txt where last_date between to_date('01.01.2013 00:00:00','dd.mm.yyyy hh24:mi:ss') and 
to_date('01.01.2014 00:00:00','dd.mm.yyyy hh24:mi:ss') and mod(I_TXT,1000)=10

Using DBMS_SERVICE to manage service

Services can be ceate/delete/stop/start by DBMS_SERVICE package

exec DBMS_SERVICE.DELETE_SERVICE('MYSERVICE');

exec DBMS_SERVICE.CREATE_SERVICE('MYSERVICE','MYSERVICE');

exec DBMS_SERVICE.START_SERVICE('MYSERVICE','MYINSTANCE1');
exec DBMS_SERVICE.START_SERVICE('MYSERVICE','MYINSTANCE2'); -- rac
...

In RAC services should be managed by "srvctl ... service". However there are some exceptions, for example you can not manage default database service with srvctl.

Trace user sessions with login trigger

In order to trace sessions created by a specific user, you can use following login trigger:

CREATE OR REPLACE TRIGGER tgardba.TRG_SESSION_TRACE
AFTER LOGON
ON DATABASE
DECLARE
 sqlstr VARCHAR2(200) := 'ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''';
BEGIN
  IF (USER = 'USERNAME') THEN
    execute immediate sqlstr;
  END IF;
END TRG_SESSION_TRAC;
/

OBJECT_GROWTH_TREND

DBMS_SPACE.OBJECT_GROWTH_TREND can be used for object size growth trend analysis.For example, for weekly analysis:

SELECT *
FROM
table(
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner =>'TABLE_OWNER',
object_name  =>'TABLE_NAME',
object_type  =>'TABLE PARTITION',
partition_name =>'01',
start_time =>NULL,
end_time   =>NULL,
interval   =>to_dsinterval('7 00:00:00')  ,
skip_interpolated => 'FALSE',
timeout_seconds =>NULL,
single_datapoint_flag =>'TRUE')
)
order by 1
/

Setting multiple ports for RAC listener

srvctl command for setting multiple ports for local listener. Listener should be restarted later:

srvctl modify listener -l LISTENER -p "TCP:1521/TCP:1522"

RMAN-06216: WARNING: db_unique_name mismatch, RMAN-06218

You may not be able to delete rman backups if they were taken with a different db_unique_name. Although it is a backup for current database rman do not allow to delete it if you  are connected using a different db_unique_name.

RMAN> DELETE FORCE NOPROMPT backuppiece 'PPPPPPPPP';

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
NNNNN ####### 1   1   AVAILABLE   SBT_TAPE    PPPPPPP

RMAN-06216: WARNING: db_unique_name mismatch - 1 objects could not be updated
RMAN-06218: List of objects requiring same operation on database with db_unique_name  DDDDD
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    PPPPPPP

How this happen?

 * Maybe you switched to physical standby database some time ago, taken backup there and switched back. If this is the case, you can delete these backups connecting rman using standby database.

 * You created physical standby database for database migration, switched to new server and then deleted the old one. If this is the case you don't have any database with required db_unique_name to connect rman  and delete these backups (if you have not converted db_unique_name of new server to old one, after migration). In this case you should identify db_unique_names registered with rman which are no longer exist:

RMAN> LIST DB_UNIQUE_NAME ALL;
 List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
######  DBNAME #########        PRIMARY          DBUNIQUENAME     
######  DBNAME #########        STANDBY          DBUNIQUENAME_STBY           
######  DBNAME #########        STANDBY          DBUNIQUENAME_STBY_TMP      

Then you should unregister db_unique_names which are no longer exist:

RMAN> UNREGISTER DB_UNIQUE_NAME DBUNIQUENAME_STBY_TMP;

database db_unique_name is "DBUNIQUENAME_STBY_TMP", db_name is "DBNAME" and DBID is #########

Now you can crosscheck and delete expired/obsolete backups.

ORA-28030: Server encountered problems accessing LDAP directory service

This error may occur while logging into oracle database using oracle internet directory (OID) authentication. First thing to do is to dump actual error:

SQL>alter system set events '28033 trace name context forever, level 9';
regenerate the error
SQL>alter system set events '28033 trace name context off';

if trace output is

kzld found pwd in wallet

KZLD_ERR: Failed to bind to LDAP server. Err=-1
KZLD_ERR: -1
KZLD is doing LDAP unbind
KZLD_ERR: failed from kzldob_open_bind.

then probably there is problem with the OID server connection parameters. Check parameters in ldap.ora:

#mkstore -wrl -viewEntry ORACLE.SECURITY.DN
Enter wallet password:
ORACLE.SECURITY.DN =
#mkstore -wrl  -viewEntry ORACLE.SECURITY.PASSWORD
Enter wallet password:

ORACLE.SECURITY.PASSWORD =


#ldapbind -h -p 389 -D  -w ""

if output produces error then correct the problem. if output is "bind successful" then check forward and reverse DNS lookup of :

#ping 

PING  () XX bytes of data.

...

#host 

it reverse dns is not successful or reverse DNS is different from the one in ldap.ora then you should correct this. A workaround is to put IP and DNS entry of directory server in /etc/hosts file. 

if trace output is

kzld_discover received ldaptype: OID

KZLD_ERR: failed to get cred from wallet

KZLD_ERR: Failed to bind to LDAP server. Err=28032

KZLD_ERR: 28032

KZLD is doing LDAP unbind

KZLD_ERR: found err from kzldini.

then check sqlnet.ora and be sure that wallet path is defined in it:

WALLET_LOCATION=

  (SOURCE=

      (METHOD=file)

      (METHOD_DATA=

         (DIRECTORY=)))

java.sql.SQLException: unsupported verifier type

You may receive following error while trying to login to database using EUS. This is a known problem with java. You should add following option to java process:

-Doracle.jdbc.thinLogonCapability=o3

or you should simple not use EUS user, instead use a normal database user account.

EUSException: There is no such user in directory

In order to do user operations on a oracle internet directory(OID) entry like granting role or  proxy permission, that entry should have "orclUser" object class:

without this object class following error occurs:

EUSException: There is no such user in directory

that is because OID searches user with a similar search query as:

ldapsearch -h oid_host -p 389 -D "user_dn" -w pasword -b "search_user_dn" -s base "objectclass=orcluser"

if entry does not have orclUser object class then search does not return any result.

How to Capture SQL Error

Here is a way to capture session errors except following errors (modified from here):
ORA-01034 ORACLE not available
ORA-01403 no data found
ORA-01422 exact fetch returns more than requested number of rows,
ORA-01423 error encountered while checking for extra rows in exact fetch
ORA-04030 out of process memory when trying to allocate string bytes.

create table myschema.caught_errors (
  dt        date,              
  username  varchar2( 30),
  msg       varchar2(4000),
  stmt      varchar2(4000)
);


create or replace trigger myschema.catch_errors
   after servererror on database
declare
   sql_text ora_name_list_t;
   msg_     varchar2(4000) := null;
   stmt_    varchar2(4000) := null;
   i int;
begin
  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
    if (nvl(length(stmt_),0) + length(sql_text(i))) < 4000 then
        stmt_ := stmt_ || sql_text(i);
    else
        insert into     caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
         stmt_ :=sql_text(i);
    end if;
  end loop;
  i:= ora_sql_txt(sql_text);
  insert into
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
end;
/

alter trigger myschema.catch_errors disable;
truncate table myschema.caught_errors;

Exadata Flash Cache Hit Ratio

Here is the cumulative numbers for system physical I/Os and cell flash cache read hits:

SQL>select name,value  from v$sysstat where name in('physical read total IO requests','cell flash cache read hits');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                  3052130616
cell flash cache read hits                                        452644513

Flash cache hit ratio is approximately %15

Following sql calculates cumulative stats and ratios using dba_hist_sysstat:

SQL> select c.snap_time, sum(a.value) physical_read_IO_requests, sum(b.value) flash_cache_read_hits,  round((sum(b.value)/sum(a.value) * 100),2) flash_cache_hit_ratio from
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) a,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) b,
(select to_char(begin_interval_time,'YYYY-MM-DD hh24') snap_time,snap_id from DBA_HIST_SNAPSHOT where instance_number=1) c
where
a.snap_id=b.snap_id and a.instance_number=b.instance_number and a.snap_id=c.snap_id
group by c.snap_time
order by 1
/

In order to find not cumulative but delta stats and ratios:

select c.snap_time, sum(a.value-d.value) physical_read_IO_req_delta, sum(b.value-e.value) flash_cache_read_hits_delta,  round((sum(b.value-e.value)/sum(a.value-d.value) * 100),2) flash_cache_hit_ratio from
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) a,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) b,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=3343375620) d,
(select SNAP_ID, instance_number,value  from dba_hist_sysstat where stat_id=2923827453) e,
(select to_char(begin_interval_time,'YYYY-MM-DD hh24') snap_time,snap_id from DBA_HIST_SNAPSHOT where instance_number=1 and begin_interval_time > sysdate-3) c
where
a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.snap_id=(d.snap_id+1) and a.instance_number=d.instance_number
and a.snap_id=(e.snap_id+1) and a.instance_number=e.instance_number
and a.snap_id=c.snap_id
group by c.snap_time
order by 1
/

How to Modify Table or Table Partition Tablespace Attibute

If you are moving a partitioned table from one tablespace to an other with following similar command:

alter table owner.table_name move partition partition_name tablespace tablespace_name ...;

Partitions will be moved to new tablespace but default tablespace attribute of the partitioned table will be remained the old one. You can see default tablespace attribute from the dba_part_tables system view:

select def_tablespace_name from dba_part_tables where table_owner='OWNER' and table_name='TABLE_NAME';

In order to set this attribute to new tablespace use following alter command:

alter table owner.table_name modify default attributes tablespace tablespace_name;

Similar case also occurs when you move subpartitions of a partition with following similar command:

alter table owner.table_name move subpartition subpartition_name tablespace tablespace_name ...;

Subpartitions will be moved to new tablespace but default tablespace of the partition will be remained the old one. You can see it from the dba_tab_partitions system view:

select tablespace_name from dba_tab_partitions where table_owner='OWNER' and table_name='TABLE_NAME' and partition_name='PARTITION_NAME';

In order to alter partition tablespace value use following alter command:

alter table owner.table_name modify default attributes for partition partition_name tablespace tablespace_name;