Contact

Category: DBA

  1. You are here »
  2. Home »
  3. category »
  4. DBA »

What Are the Major Advantages of Good and Reliable Data Management?

Data management is the process of collecting, storing, organizing, and managing data from distinct sources. This data further gets projected as more meaningful insight and report to aid decision making of top management, stockholders, investors, and similar business personnel. In the era of big data, the database management system is more consolidated to perform all […]

No package mysql-server available

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: mirror.es.its.nyu.edu * extras: mirrors.rit.edu * updates: mirrors.advancedhosters.com No package mysql-server available. Error: Nothing to do […]

How to Create an Awesome Database Design?

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 […]

oracle impdp as sysdba and attach job

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 Once […]

oracle – generate kill database sessions script

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 […]

Enabling and Disabling – auto optimizer stats collection

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’, […]

expdp fails with ORA-27037 on Oracle RAC

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 […]

Identifying Blocking Sessions in Oracle Database

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 […]

oracle archive log history

Here is the Oracle SQL script that comes handy to find out the number of archive logs that are generated by the database by day by the 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”, […]

expdp as sysdba

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

Categories