No package mysql-server available

By Ravi Karamsetty | October 26, 2016 |

Starting CentOS7 MySQL is replaced with MariaDB. You might encounter the following error “mysql -u root -p4Samsung4” when you try installing mysql using yum. [root@myhost~]# yum install mysql-server Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: * extras: * updates: No package mysql-server available. Error: Nothing to do … Continue reading “No package mysql-server available”

Read More

How to Create an Awesome Database Design?

By webmaster | September 28, 2016 |

Creating an awesome database design asks for the discipline of following certain rules. Here are some of them for you to get an overall idea of the data modeling and the development process. Plan before you start development If you see the work procedure of an architect, he creates the blueprint before starting the work … Continue reading “How to Create an Awesome Database Design?”

Read More

oracle impdp as sysdba and attach job

By Ravi Karamsetty | July 22, 2016 |

impdp allows Oracle DBA to exit out of the current process while the job continues to run in the background.  In the event you have to connect to the job and take some action, below is how that can be accomplished: In the following example job name is – “my_job”   impdp \”/as sysdba\” attach=my_job … Continue reading “oracle impdp as sysdba and attach job”

Read More

oracle – generate kill database sessions script

By Ravi Karamsetty | July 21, 2016 |

Here is the script that comes handy to Oracle DBAs for generating script to kill sessions based on user sessions. For example, this SQL script generates the script to kill all the sessions belong to user – SCOTT. Login to database as sys or system or sysdba or user with DBA role: SQL> select ‘alter … Continue reading “oracle – generate kill database sessions script”

Read More

Enabling and Disabling – auto optimizer stats collection

By Ravi Karamsetty | May 20, 2016 |

Here is how you can query if the Auto Optimizer Stats Collection job is running in Oracle Database 11g: Login to database as SYSDBA: [sql] SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = ‘auto optimizer stats collection’; [/sql] Below is how the job can be disabled: [sql htmlscript=”false”] EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => ‘auto optimizer stats collection’, … Continue reading “Enabling and Disabling – auto optimizer stats collection”

Read More

expdp fails with ORA-27037 on Oracle RAC

By Ravi Karamsetty | November 6, 2015 |

When parallel option is used in expdp, it expects the DIRECTORY path to be available on nodes of the cluster. If you have created the directory on only one node and trying do the export using expdp, your export fails with the below error: ORA-31693: Table data object “SCOTT”.”EMPLOYEE” failed to load/unload and is being … Continue reading “expdp fails with ORA-27037 on Oracle RAC”

Read More

Identifying Blocking Sessions in Oracle Database

By Ravi Karamsetty | November 2, 2015 |

Below SQL gives the blocker and blockee sessions in database. Oracle RAC Environment: select (select username from gv$session where sid=a.sid) blocker, a.sid, ‘is blocking’, (select username from gv$session where sid=b.sid) blockee, b.sid from gv$lock a, gv$lock b where a.block > 0 and b.request > 0 and a.id1=b.id1 and a.id2=b.id2; Oracle Non-RAC Environment: select (select username … Continue reading “Identifying Blocking Sessions in Oracle Database”

Read More

oracle archive log history

By Ravi Karamsetty | October 22, 2015 |

Here is the oracle sql script that comes handy to find out number of archive logs that are generated by database by day by hour. set lines 120 set pages 999 spool log_history.log SELECT to_char(first_time,’YYYY-MON-DD’) day, to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”, to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”, to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”, to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “03”, to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “04”, to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “05”, to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “06”, to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “07”, to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “08”, to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) … Continue reading “oracle archive log history”

Read More

expdp as sysdba

By Ravi Karamsetty | October 21, 2015 |

Here is the simple command to run oracle logical backup/datapump export using sysdba: expdp “/as sysdba” parfile=exp_user.par  

Read More