Cause:
Graphs are not displayed in the cloudera dashboard and throwing error after starting the cluster
Solution:
Click on the cloudera management service and click restart.
It take sometime for the realtime graphs to reflect on the dashboard!
-bash: locate: command not found (Solved)
Solution:
[root@ip-xxx ~]# which locate /usr/bin/which: no locate in (/usr/local/sbin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)
Use yum to install mlocate package
[root@ip-xxx ~]# yum install mlocate -y Loaded plugins: amazon-id, rhui-lb, search-disabled-repos cloudera-manager | 951 B 00:00:00 mysql-connectors-community | 2.6 kB 00:00:00 mysql-tools-community | 2.6 kB 00:00:00 mysql56-community | 2.6 kB 00:00:00 rhui-REGION-client-config-server-7 | 2.1 kB 00:00:00 rhui-REGION-rhel-server-releases | 2.0 kB 00:00:00 rhui-REGION-rhel-server-rh-common | 2.1 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package mlocate.x86_64 0:0.26-8.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================= Package Arch Version Repository Size ============================================================================================================================================================================================= Installing: mlocate x86_64 0.26-8.el7 rhui-REGION-rhel-server-releases 113 k Transaction Summary ============================================================================================================================================================================================= Install 1 Package Total download size: 113 k Installed size: 379 k Downloading packages: mlocate-0.26-8.el7.x86_64.rpm | 113 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mlocate-0.26-8.el7.x86_64 1/1 Verifying : mlocate-0.26-8.el7.x86_64 1/1 Installed: mlocate.x86_64 0:0.26-8.el7 Complete!
Update the locate repository
[root@ip-xxx ~]# updatedb
/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Cause:
While executing export data from HDFS to RDBMS using sqoop ,because of non existing accumulo home,this error is triggered in hadoop
[root@ip-xxx ~]# [root@ip-172-31-60-27 ~]# sqoop export --connect jdbc:mysql://localhost:3306/tes t \ > --table t \ > --username root --password 123 \ > --export-dir root/data \ > --fields-terminated-by ',' --lines-terminated-by '\n' Warning: /opt/cloudera/parcels/CDH-5.15.1-1.cdh5.15.1.p0.4/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. 21/01/15 18:48:01 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.15.1 Missing argument for option: connect
Solution:
Make a accumulo home directory in var/lib
Add the environment variable for accumulo home in the /etc/profile file
[root@ip-xxx ~]# mkdir -p /var/lib/accumulo [root@ip-xxx~]# vi /etc/profile [root@ip-xxx ~]# cat /etc/profile|grep 'ACCU' ACCUMULO_HOME=/var/lib/accumulo export ACCUMULO_HOME [root@ip-xxx ~]# source /etc/profile
ORA-39123: Data Pump transportable tablespace job aborted;ORA-39185: The transportable tablespace failure list is ORA-29335: tablespace ‘EXAMPLE’ is not read only
Cause:
29335, 00000, "tablespace '%s' is not read only" // *Cause: The tablespace is not read only. // *Action: Make the tablespace read only and retry the operation.
Transportable tablespace got failed during export due to read write tablespaces
Solution:
Check the tablespace for self containment without dependent data
SQL> exec dbms_tts.transport_set_check('EXAMPLE',true);
PL/SQL procedure successfully completed.
Make the tablespace readonly to maintain consistency of data
SQL> exec dbms_tts.transport_set_check('EXAMPLE',true);
PL/SQL procedure successfully completed.
Start the export
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=ttbs.par Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /tmp/ttbs.dmp ****************************************************************************** Datafiles required for transportable tablespace EXAMPLE: /app01/EXAMPLE12625.dbf /app01/EXAMPLE6345.dbf /app01/EXAMPLE7187.dbf /app01/oracle/product/base/oradata/orcl12x/example01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 13 16:22:24 2021 elapsed 0 00:00:14
Export is successful !
~~~Exadata health check script~~~
An exclusive script is required sometimes to perform a rapid triage for a critical issue
This script provides a basic health status,resource statistics and other errors of exadata cell nodes and infiniband switches
Test the script in test environment and customize to type of exadata rack before implementing in production
Use it at your own risk!
#!/bin/bash
####################################################################################
# EXADATA HEALTH SCRIPT #
# AUTHOR~Kishan M #
####################################################################################
# 1/8th RACK
####################################################################################
# DESCRIPTION #
# This script will generate the status report from Exadata cell and switch #
####################################################################################
#print the title
_title()
{
echo " *** -------------------------------- ***"
echo " *** EXADATA HEALTH CHECK ***"
echo " *** -------------------------------- ***"
}
#log
_log()
{
echo "*** $1"
}
#verify_user
_verify_user()
{
if [ $(id -u) != 0 ]; then
_log "Run as root user"
exit 1
fi
}
#Set environment
_env()
{
_log "*** Exporting environment variables *** "
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/11.2.0.4/dbhome_1/bin:/u01/app/12.2.0.1/grid/bin:/u01/app/12.2.0.1/grid/OPatch:/u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch:/u01/app/oracle/ggs_home
CG=/root/cell_group;SG=/root/switch_group;DG=/root/dbs_group;CELLLOG=/tmp/cell.log;IBLOG=/tmp/iberrors.log;HDD=/tmp/hdd.err;FDD=/tmp/fdd.err;IH=/tmp/IMAGEHISTORY.log
export CG;export SG;export CELLLOG;export IBLOG;export HDD;export FDD;export IH
_log "LOGFILES";echo $CELLLOG;echo $IBLOG;echo $HDD;echo $FDD;echo $IH
}
#check file
_file_check(){
ls "$CELLLOG" "$IBLOG" "$FDD" "$HDD" >/dev/null 2>&1 && echo '***'
if [ $? -eq 0 ]
then
echo ''
else
touch $CELLLOG $IBLOG $FDD $HDD
fi
}
#Cell
_cell_monitor()
{
echo "#################################################################################"
echo "# CELL HEALTH CHECK #"
echo "#################################################################################"
_log " Checking the cell status ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list cell"
echo "---------------------------------------------------------------------------------"
_log " Checking cell processes ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g /root/cell_group -l root " hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep '(cellsrvStatus)|(msStatus)|(rsStatus)'"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log " Cumulative CPU utilization on each cell ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list metriccurrent CL_CPUT"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log " Top 5 processes consuming CPU in compute nodes ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $DG -l root " hostname;ps -eo pid,ppid,cmd,%cpu --sort=-%cpu|head -6"|awk '{$1="";print}'|column -t|grep -v "ps"
echo "---------------------------------------------------------------------------------"
_log " Top 5 processes consuming CPU in cell nodes ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root " hostname;ps -eo pid,ppid,cmd,%cpu --sort=-%cpu|head -6"|awk '{$1="";print}'|column -t |grep -v "ps"
echo "---------------------------------------------------------------------------------"
_log " Memory utilization on each cell"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list metriccurrent CL_MEMUT"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
#_log " IOPS on each cell"
#/usr/local/bin/dcli -g $CG -l root cellcli -e "list metriccurrent CD_IO_UTIL"
echo "#################################################################################"
_log " Checking abnormal metrics please wait for sometime ***"
echo " "
echo "#################################################################################"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list metrichistory where alertState!=\'Normal\'"
echo "#################################################################################"
}
_cell_error_check()
{
_log " Checking the critical alerts in the cell ***"
echo "#################################################################################"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list alerthistory attributes name,alertMessage,beginTime,endTime where severity = 'critical' detail;"|tail -10 >$CELLLOG && cat $CELLLOG
grep 'alertMessage' $CELLLOG >/dev/null 2>&1
if [ $? -ne 0 ]
then
echo " *** No cell alerts for now ***"
fi
echo "#################################################################################"
}
#Physical Disk
_hard_disk_check()
{
_log " Checking the abnormal physical disk ***"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list physicaldisk attributes name,id,slotnumber where disktype=\"harddisk\" and status!=\'normal\'" >$HDD && cat $HDD
egrep -i 'poor|predictive' $HDD >/dev/null 2>&1
if [ $? -ne 0 ];then
echo " *** No hard disk issue ***"
fi
echo "#################################################################################"
}
#Flash Disk
_flash_disk_check()
{
echo " *** Flashcache Mode *** "
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g /root/cell_group -l root " hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep 'flashCacheMode'"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
echo " *** Flashcache used in cell nodes *** "
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g /root/cell_group -l root " hostname;echo "-----------------------";cellcli -e "list metriccurrent where name = 'FC_BY_USED'""|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
echo " Checking abnormal flash disk ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list physicaldisk attributes name,id,slotnumber where disktype=\"flashdisk\" and status!=\'normal\'" >$FDD && cat $FDD
egrep -i 'poor|predictive' $FDD >/dev/null 2>&1
if [ $? -ne 0 ];then
echo " *** No flash disk issue ***"
fi
echo "---------------------------------------------------------------------------------"
}
#Switch
_ib_port_check()
{
echo "#################################################################################"
echo "# EXADATA SWITCH PORT HEALTH #"
echo "#################################################################################"
echo " *** Checking the ports status on the switches ***"
/usr/local/bin/dcli -g $SG -l root "listlinkup"|grep -v '13A\|14A\|15A\|8B\|12B\|15B'|grep 'down' >$IBLOG && cat $IBLOG
grep 'down' $IBLOG >/dev/null 2>&1
if [ $? -ne 0 ];then
echo "---------------------------------------------------------------------------------"
echo "# Ports are up!" #
fi
echo "---------------------------------------------------------------------------------"
}
_ib_status(){
echo "#################################################################################"
ibstatus
}
echo "#################################################################################"
echo " "
echo " "
echo "---------------------------------------------------------------------------------"
#Diagnostic Reports
_sundiag_report()
{
while true
do
read -p "Do you want to run sundiag report on all cellnodes: Y/N?" I
case $I in
[Yy]* ) _log "Running sundiag report! please wait ***"; /usr/local/bin/dcli -g $CG -l root "/opt/oracle.SupportTools/sundiag.sh"; break;;
[Nn]* ) _log "=====>Exiting Report"; break;;
* ) echo "Please choose either Yes(Y) or No(N)";;
esac
done
}
echo " "
#exacheck version
_exa_chk_version(){
_log "Please verify if you have the latest exachk utility version from MOS"
echo "#################################################################################"
echo " "
/opt/oracle.ahf/exachk -v
echo " "
echo "#################################################################################"
}
_exa_chk()
{
echo "Do want to run exa_chk report? Choose 1 or 2"
select I in "Yes" "No"
do
case $I in
Yes ) _log "Running exachk report! please wait ***";/opt/oracle.ahf/exachk; break;;
No ) _log "=====>Exiting Report"; break;;
esac
done
}
echo " "
echo " "
#if you face error you can login to any of the nodes
_host_logon()
{
echo '***Do you login to any host? Choose a valid option!***'
hosts=("exacel01" "exacel02" "exacel03" "exacel04" "exasw-ibb01" "exasw-iba01" "Exit")
select h in "${hosts[@]}";do
case $h in
"exacel01")
ssh exacel01
;;
"exacel02")
ssh exacel02
;;
"exacel03")
ssh exacel03
;;
"exacel04")
ssh exacel04
;;
"exasw-ibb01")
ssh exasw-ibb01
;;
"exasw-iba01")
ssh exasw-iba01
;;
"Exit")
_log "=====>End of script"; break
;;
*) echo "Enter correct hostname";;
esac
done
}
echo " "
echo " "
_other_stats()
{
_log "Fan status"
echo "---------------------------------------------------------------------------------"
echo " "
/usr/local/bin/dcli -g $CG -l root " hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep fan"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log "Power status"
echo "---------------------------------------------------------------------------------"
echo " "
/usr/local/bin/dcli -g $CG -l root " hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep power"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log "makeModel of cell"
echo " "
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root " hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep makeModel"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
echo " "
}
echo " "
echo "---------------------------------------------------------------------------------"
_imagehistory_info()
{
_log "Imagehistory details will be dumped to $IH file"
/usr/local/bin/dcli -g $DG -l root "hostname;echo "-----------------------";imagehistory -all"|awk '{$1="";print}'
/usr/local/bin/dcli -g $CG -l root "hostname;echo "-----------------------";imagehistory -all"|awk '{$1="";print}'
}
_title
_env
_verify_user
_file_check
_cell_monitor
_cell_error_check
_hard_disk_check
_flash_disk_check
_ib_port_check
_ib_status
_other_stats
_imagehistory_info > $IH
_sundiag_report
_exa_chk
_host_logon
Hope you like this script !!
PRCR-1079 : Failed to start resource ora.asm;CRS-5017: The resource action “ora.asm start” encountered the following error:;ORA-56865: Invalid IP address in CELLINIT.ORA;. For details refer to “(:CLSN00107:)” in “/apps01/base/product/11.2.0/grid/log/ exdbadm01/agent/ohasd/oraagent_oracle/oraagent_oracle.log”. (EXADATA)
Cause:
ASM cant start because of miscommunication between compute and cell nodes
[oracle@exdbadm01 ~]$ oerr ora 56865 56865, 00000, "Invalid IP address in CELLINIT.ORA" // *Document: YES // *Cause: One or more of the specified IP addresses in CELLINIT.ORA is not // valid. // *Action: Check that all IP addresses in CELLINIT.ORA are valid.
Solution:
Verify the ipaddress in cellinit.ora file on all cell nodes
[root@exceladm00 celladmin]# cat /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/cellsrv/deploy/config/cellinit.ora #CELL Initialization Parameters version=0.0 HTTP_PORT=8888 bbuChargeThreshold=800 SSL_PORT=23943 RMI_PORT=23791 ipaddress1=10.10.1.11/24 bbuTempThreshold=60 DEPLOYED=TRUE JMS_PORT=9127 BMC_SNMP_PORT=162
Check if the rds protocols are enabled on the cellnodes
[root@exceladm00 celladmin]# lsmod|grep rds rds_rdma 106561 0 rds_tcp 48097 0 rds 155561 260 rds_rdma,rds_tcp rdma_cm 73429 2 rds_rdma,ib_iser ib_core 108097 8 rds_rdma,ib_iser,rdma_cm,ib_cm,iw_cm,ib_sa,ib_mad,iw_cxgb3
If not enabled,then start the protocols
[root@exceladm00 celladmin]# /sbin/modprobe rds-tcp [root@exceladm00 celladmin]# /sbin/modprobe rds_rdma [root@exceladm00 celladmin]# modprobe rds
Verify that all the griddisks in cell node are visible from compute node
[root@exdbadm01 oracle]# /grid/stage/ext/bin/kfod op=disks disk=all -------------------------------------------------------------------------------- Disk Size Path User Group ================================================================================ 1: 976 Mb o/192.168.56.69/DATA_CD_cell01_exceladm00 <unknown> <unknown> 2: 976 Mb o/192.168.56.69/DATA_CD_cell02_exceladm00 <unknown> <unknown> 3: 976 Mb o/192.168.56.69/DATA_CD_cell03_exceladm00 <unknown> <unknown> 4: 976 Mb o/192.168.56.69/DATA_CD_cell04_exceladm00 <unknown> <unknown> 5: 976 Mb o/192.168.56.69/DATA_CD_cell05_exceladm00 <unknown> <unknown> 6: 976 Mb o/192.168.56.69/DATA_CD_cell06_exceladm00 <unknown> <unknown> 7: 976 Mb o/192.168.56.69/FRA_CD_cell07_exceladm00 <unknown> <unknown> 8: 976 Mb o/192.168.56.69/FRA_CD_cell08_exceladm00 <unknown> <unknown> 9: 976 Mb o/192.168.56.69/FRA_CD_cell09_exceladm00 <unknown> <unknown> 10: 976 Mb o/192.168.56.69/FRA_CD_cell10_exceladm00 <unknown> <unknown> 11: 976 Mb o/192.168.56.69/MGMT_CD_cell11_exceladm00 <unknown> <unknown> 12: 976 Mb o/192.168.56.69/MGMT_CD_cell12_exceladm00 <unknown> <unknown>
Start the asm
[oracle@exdbadm01 ~]$ . oraenv ORACLE_SID = [oracle] ? +ASM The Oracle base has been set to /apps01/base [oracle@exdbadm01 ~]$ srvctl start asm
Check status of asm
[root@exdbadm01 oracle]# srvctl status asm ASM is running on exdbadm01
ORA-02096: specified initialization parameter is not modifiable with this option
Cause:
You try to modify a parameter online without saving the changes to spfile
02096, 00000, "specified initialization parameter is not modifiable with this option" // *Cause: Though the initialization parameter is modifiable, it cannot be // modified using the specified command. // *Action: Check the DBA guide for information about under what scope // the parameter may be modified
Solution:
There are two types of parameters
one is static and other is dynamic
Dynamic parameters can be modified,when the instance is up and running without a database bounce and can be modified at memory level
Static parameters can be modified,when the instance is up and running but require a reboot for the changes to get reflected on database and it can be modified only at spfile level not in memory
For example,you can see the parameters which can and cant be modified in ISINSTANCE_MODIFIABLE column of v$parameter view
SQL> set lines 200 SQL> set pages 1000 SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter; NAME ISINS <------ -------------------------------------------------------------------------------- ----- lock_name_space FALSE(static) processes FALSE(static) sessions TRUE(dynamic)
If i try to modify the static parameter,i get the error
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 200
SQL> alter system set processes=300;
alter system set processes=300
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified <---------
SQL> alter system set processes=300 scope=spfile; <-------
System altered.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1572861600 bytes
Fixed Size 8910496 bytes
Variable Size 1132462080 bytes
Database Buffers 423624704 bytes
Redo Buffers 7864320 bytes
Database mounted.
Database opened.
In alertlog, you can monitor the number of process gets modified with the value which was set earlier
[oracle@orcl19x ~]$ tail -f /data01/base/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log|grep processes Shutting down archive processes processes = 300 <-----
I can modify the dynamic parameter in fact without a reboot!
SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter where name='optimizer_use_invisible_indexes'; NAME ISINS -------------------------------------------------------------------------------- ----- optimizer_use_invisible_indexes TRUE <---(Dynamic) SQL> show parameter optimizer_use_invisible_indexes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean TRUE SQL> alter system set optimizer_use_invisible_indexes=FALSE; System altered. SQL> show parameter optimizer_use_invisible_indexes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE
PLS-00201: identifier ‘DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION’ must be declared
Cause:
Appropriate permissions are not in place for the procedure,function or package for user!
SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace= >TRUE);
BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=>TRU E); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE);
BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE); END;
*
ERROR at line 1:
ORA-06550: line 1, column 67:
PLS-00201: identifier 'SQL_TRACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Solution:
Even though the user has dba privilege,still the user couldnot execute the packages of SYS and you can also create a public synonym for this package
Grant the privilege using sysdba account
SQL> conn / as sysdba Connected. SQL> create public synonym dbms_system for dbms_system; Synonym created. SQL> grant execute on dbms_system to test; Grant succeeded. SQL> grant all on dbms_system to test; Grant succeeded.
Check the privilege given
SQL> select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE from DBA_TAB_PRIVS where grantee='TEST'; 'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE -------------------------------------------------------------------------------- GRANT EXECUTE ON SYS.DBMS_SYSTEM TO TEST GRANT DEBUG ON SYS.DBMS_SYSTEM TO TEST
It works fine now
SQL> exec dbms_system.set_sql_trace_in_session(45,14530,TRUE); PL/SQL procedure successfully completed. SQL> show user USER is "TEST"
ORA-01743: only pure functions can be indexed
Cause:
While creating function based index,you should give deterministic keyword for oracle to understand the function you create
You should not give sysdate,user etc.. inside a function
01743, 00000, "only pure functions can be indexed" // *Cause: The indexed function uses SYSDATE or the user environment. // *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL // expressions must not use SYSDATE, USER, USERENV(), or anything // else dependent on the session state. NLS-dependent functions // are OK.
Solution:
The function you give to functional based index should return the same result for same parameters
eg:
Don’t
SQL> create index fn_order_date on sales(trunc(sysdate));
create index fn_order_date on sales(trunc(sysdate))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
Do
SQL> create index fn_order_date on sales(trunc(order_date)); Index created.
ORA-10631: SHRINK clause should not be specified for this object
Cause:
The solution for this error by oerr utility is not detailed
However,this issue is due to function based indexes existing on the table and we try to shrink the space in table to reduce fragmentation
SQL> !oerr ora 10631
10631, 00000, “SHRINK clause should not be specified for this object”
// *Cause: It is incorrect to issue shrink on the object
// *Action: Verify the object name and type and reissue the command
SQL> alter table test1.sales shrink space; alter table test1.sales shrink space * ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object
Solution:
Only way to overcome this error is to drop the index and recreate it
Identify the function index and drop it
SQL> select index_name from dba_indexes where owner='TEST1' and table_name='SALES';
INDEX_NAME
--------------------------------------------------------------------------------
PK_SALES_ID
FN_ORDER_DATE
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','FN_ORDER_DATE') from dual;
DBMS_METADATA.GET_DDL('INDEX','FN_ORDER_DATE')
--------------------------------------------------------------------------------
CREATE INDEX "TEST1"."FN_ORDER_DATE" ON "TEST1"."SALES" (TRUNC("ORDER_DATE"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
Drop the index
SQL> alter index test1.FN_ORDER_DATE invisible; Index altered. SQL> alter table test1.sales shrink space; alter table test1.sales shrink space * ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object SQL> alter index test1.FN_ORDER_DATE unusable; Index altered. SQL> alter table test1.sales shrink space; alter table test1.sales shrink space * ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object SQL> drop index test1.FN_ORDER_DATE; Index dropped. SQL> alter table test1.sales shrink space; Table altered.
After space shrink,rebuild the index
SQL> create index fn_order_date on sales(trunc(order_date)); Index created.
