Online DBA

<<Back to DB Administration Main Page while suing oradebug raises error ORA-00074 SQL> oradebug eventdump system; ORA-00074: no process has been specified Solution setmypid , and issue the oradebug statement again SQL>  oradebug setmypid Statement processed. SQL> oradebug eventdump session; 3171 trace name errorstack level 3

<<Back to DB Administration Main Page There are situations when oracle triggers an error which is probably captured at application level but not recorded in alterlog. For example a very simple erro r ORA-00942: table or view does not exist which probably means you don't have permission to access the object or there is typo or or...or... But there are such error which you must want to capture in the alertlog and generate the trace file for further troubleshooting. In such occasions you force oracle to write the error message and generate the trace file by enabling the errorstack. How to Enable Orale ErrorStack You can enable error stack either at session level or system level. Enabling ErrorStack at Session Level $ sqlplus / as sysdba SQL> alter system set max_dump_file_size=unlimited; SQL> alter  session set events '942 trace name errorstack level 3'; SQL> exit Enabling ErrorStack at System Level $ sqlplus / as sysdba SQL> a

<<Back to Oracle RAC Main Page Using crsctl query command in Oracle 12c Display CRS admin list $ crsctl query crs administrator CRS Administrator List: oracle root       Gets the value of automatic start delay and server Count $ crsctl query crs autostart 'Autostart delay':       0 'Autostart servercount': 1 Lists the Oracle Clusterware active Version $ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [12.2.0.1.0] List the cluster state and active patch Level $ crsctl query crs activeversion -f Oracle Clusterware active version on the cluster is [12.2.0.1.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [0]. Lists the Oracle Clusterware release version     $ crsctl query crs releaseversion Oracle High Availability Services release version on the local node is [12.2.0.1.0] Lists the version of Oracle Clusterware software installed $ crsctl query crs softwareversion -all Orac

<<Back to DB Administration Main Page There are instances when a DBA need to start the database using pfile, for example to trouble an instance startup error or to validate init file post parameter changes etc. In such situations you can create a pfile from spfile and once you are done with your changes you can create spfile from updated/modified pfile to start the database. How to Create Pfile from Spfile As sysdba execute following command  SQL> create pfile='/tmp/initOrcl.ora' from spfile; How to Create SPfile from Pfile As sysdba execute following command  SQL> create spfile from  pfile='/tmp/initOrcl.ora'; You can also create the pfile directly from memory How to Create Pfile from Memory As sysdba execute following command  SQL> create  pfile='/tmp/initOrcl.ora' from memory;

<<Back to DB Administration Main Page Connecting to Oracle Database There are various mechanism/tools available to connect to the oracle database eg TOAD, SQL*PLUS, PL/SQL Developer etc. Basic information required to connect to database is same for every tool except if you are using OS authentication Necessary information to connect to oracle database Hostname DB_NAME or DB_Service Port Connecting Using OS Authentication $export ORACLE_SID=orcl $export ORACLE_HOME=/u01/app/oracle/product/18.0.0/db_1 $export PATH=$ORACLE_HOME/bin:$PATH $sqlplus / as sysdba OR $sqlplus /nolog SQL> connect sys as sysdba OR SQL> connect sys@tnsname as sysdba Connecting with Easy Connect Syntax SQL> connect username@"dbhost.example.com/sales.example.com" SQL> connect username @"dbhost.example.com/sales.example.com:dedicated"  Connecting with Easy Connect Syntax with a Nondefault Listener Port SQL> connect username

<<Back to DB Administration Main Page ORA-1652: unable to extend temp segment by 128 in tablespace TEMP Error ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl1p: ************************************************************************* 2017-09-06 08:39:08.701000 +02:00 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [ORCL] ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [ORCL] ORA-1652: unable to extend temp segment by 128 in tablespace TEMP [ORCL] Solution You can resize the tempfile if it is restricted to limited size SQL> alter database tempfile  '/u01/app/oradata/temp01.dbf'  resize 10240M You can now configure the tempfile to grow up to certain size or up the maximum size allowed by oracle (32G)   SQL> alter database tempfile  '/u01/app/oradata/temp01.dbf'   autoextend on next 100m maxsize 20480; or SQL> alter database tempfile  '/u01/app/oradata/temp

<<Back to MySQL & MariaDB How To Page Find  Explain Plan of a Query in Mysql or MariaDB mysql> explain extended <query> For Example: mysql> explain extended select host,user from mysql.user; +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ |  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 228     | NULL |  116 |   100.00 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

<<Back to DB Administration Main Page ORA-01753: column definition incompatible with clustered column definition SQL > CREATE TABLE employees ( department_id number (3) , name varchar2(20))   2  CLUSTER employees_departments_cluster (department_id); CLUSTER employees_departments_cluster (department_id)                                        * ERROR at line 2: ORA-01753: column definition incompatible with clustered column definition Solution: Verify and correct the cluster column definition in create table clause. To find the cluster key column  length use below query SQL> select DATA_PRECISION from dba_tab_columns  where owner='TEST' and TABLE_NAME='EMPLOYEES_DEPARTMENTS_CLUSTER'; SQL>  select  DATA_PRECISION from user_tab_columns  where  TABLE_NAME='EMPLOYEES_DEPARTMENTS_CLUSTER1';  DATA_PRECISION -------------- --------------                             4 1 row selected. SQL > CREATE TABLE employees ( departme

<<Back to DB Administration Main Page Tables in Oracle Database What is a Table? A table is the basic unit of data organization in an Oracle database. Types of tables in Oracle There are 2 types of tables in oracle 1> Relational Tables 2> Object Tables What is Relational Tables? Relational tables have simple columns and are the most common table type. Relational Tables can be created as  Heap Organized Table Index Organized Table External Table What is Heap Organized Table? A heap-organized table does not store rows in any particular order What is Index Organized Table? An index-organized table orders rows according to the primary key values. What is External Table? An external table is a read-only table whose metadata is stored in the database but whose data is stored outside the database. NOTE: - A table is either a temporary table (create with create temp table clause) or a permanent Table

<<Back to DB Administration Main Page What is Oracle Database Block An oracle data block (DB Block) is a logical unit of data storage. And is the smallest unit of IO for oracle database. The data is physically stored in datafiles (disk files) made up of operating system blocks (OS Blocks). The size of OS blocks can be different than the Oracle DB Block. The OS handles IO in OS blocks while oracle always reads, write data in multiple of DB blocks. The size of oracle DB block can be defined using DB_BLOCK_SIZE init parameter (default 8k ) at the time of database creation and can not be changed later. You can configure multiple database block size in a database. To be specific you can define block size for each tablespace (except SYSTEM, SYSAUX). Oracle Data Block Structure Now that you know what is oracle data block, Lets have a look how does it looks like. As you can see in the picture above the oracle DB block got a header (also known as block overhead) on top