World of Oracle DBA

Problem Description Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts. •The lsnrctl status or lsnrctl stop or lsnrctl reload does not respond. Just like it hangs after displays message connecting to ..... . •No one from outside can connect to database. •Local connection without listener was ok. •Listener process takes high cpu than normal usage. •Listener process forks. The word fork is an UNIX OS related term and it indicates listener process creates a copy of itself. The copied process is called child process and the original process is called a parent process. Due to load of the listener a child listener process is created and it remains persistent. Whenever we give ps -ef then two tnslsnr is shown as below. $ ps -ef | grep tnslsnr oracle 3102 1 0 Jan 01 ? 12:28 /var/opt/oracle/bin/tnslsnr LISTENER -inherit oracle 5012 3102 0 Jan 25 ? 10:15 /var/opt/oracle/bin/tnslsnr LISTENER -inherit Fro...

Many times you have granted a user DBA super role instead of giving individual privilege to a user. Later whenever you want to revoke DBA role you need to care of which privilege you need to give the user. Before example let's take a overview about some views related to privileges and roles in oracle. 1)DBA_SYS_PRIVS describes system privileges granted to users and roles. SQL> desc dba_sys_privs Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) 2)USER_SYS_PRIVS describes system privileges granted to the current user. SQL> desc user_sys_privs Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME ...

In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task. Let's show example by creating table and entering values into it. Create table table_1(id number, code varchar2(20)); insert into table_1 values(1,'First Row'); insert into table_1 values(2, 'Rows to be updated'); Create table table_2(id number, code varchar2(20)); insert into table_2 values(2,'Second Row'); After above statements let's look at the data on the table. SQL> select * from table_1; ID CODE ---------- -------------------- 1 First Row 2 Rows to be updated SQL> select * from table_2; ID CODE ---------- -------------------- 2 Second Row Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated. Method 01: SQL> update table_1 set code= (select t2.code from table_2 t2 JO...

In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task. Let's show example by creating table and entering values into it. Create table table_1(id number, code varchar2(20)); insert into table_1 values(1,'First Row'); insert into table_1 values(2, 'Rows to be updated'); Create table table_2(id number, code varchar2(20)); insert into table_2 values(2,'Second Row'); After above statements let's look at the data on the table. SQL> select * from table_1; ID CODE ---------- -------------------- 1 First Row 2 Rows to be updated SQL> select * from table_2; ID CODE ---------- -------------------- 2 Second Row Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated. Method 01: SQL> update table_1 set code= (select t2.code from table_2 t2 JO...

You sometimes may wish to access to logon to your database filtered by IP address. Suppose you will allow to connect to database having a list of IP address. Or you like to ban a list of IP addresses in order to deny logon as a database user. With oracle this scenario can be achieved, however this seems to me a bit of fun. The secret lies in the SQLNET.ORA file. On UNIX system this file resides in $ORACLE_HOME/network/admin directory along with tnsnames.ora and listener.ora. In order to put any filtering by IP address open the sqlnet.ora file with any editor and insert the following line, tcp.validnode_checking = yes This in fact, turns on the hostname/IP checking for your listeners. After this, with tcp.invited_nodes /tcp.excluded_nodes you can supply lists of nodes to enable/disable, as such: tcp.invited_nodes = (hostname1, hostname2) tcp.excluded_nodes = (192.168.100.101,192.168.100.160) Note that if you only specify invited nodes with tcp.invited_nodes, all other nodes will be excl...

Way 01 SQL> create or replace package number_utils as 2 function d_to_hex(decimal_num in integer) return varchar2; 3 pragma restrict_references (d_to_hex, wnds, wnps, rnps); 4 end; 5 / Package created. SQL> create or replace package body number_utils as 2 function d_to_hex (decimal_num in integer) 3 return varchar2 is 4 v_result varchar2(12); 5 v_hex_digit varchar2(1); 6 v_quotient pls_integer; 7 v_remainder pls_integer; 8 begin 9 if (decimal_num 10 v_result := to_char(decimal_num); 11 elsif (decimal_num 12 v_result := chr(65+(decimal_num-10)); 13 else 14 v_remainder := mod(decimal_num,16); 15 v_quotient := round((decimal_num - v_remainder) /16); 16 v_result :=number_utils.d_to_hex(v_quotient) || number_utils.d_to_hex(v_remainder); 17 end if; 18 return v_result; 19 end ...

There are various ways to know which options are installed on your oracle database. Below is some. 1)Using Oracle Universal Installer: -Go to oracle database software installer. -Under install folder run oracle universal installer. On windows it is oui.exe and on unix it is runIstaller.sh -Select Installed Products. -In the Inventory expand the selection and you can see list of options installed. 2)From V$OPTION: From v$option view the column value's value TRUE means the corresponding option is installed/available and FALSE mean corresponding option is not installed/ not available. SQL> set pages 100 SQL> col value for a5 SQL> set lines 120 SQL> select * from v$option; PARAMETER VALUE ---------------------------------------------------------------- ----- Partitioning TRUE Objects TRUE Real Application Cluster...

In oracle database 10.2g release a lots of performance feature are added in it. 1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration. 2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added. 3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management. 4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly. 5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id. 6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be config...

Oracle database server provides two scripts to configure automatic database startup and shutdown process. The scripts are, $ORACLE_HOME/bin/dbstart $ORACLE_HOME/bin/dbshut Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d . -Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script. In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d . If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively. The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively. Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and startup or shutdown the database. Automate Startu...

Way 1: with data as (select level levels from dual connect by level select f from data model dimension by (levels) measures ( 0 f) rules ( f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1] ); Enter value for how_may_rows: 10 old 2: connect by level new 2: connect by level F ---------- 0 1 1 2 3 5 8 13 21 34 10 rows selected. Way 2: Just a variant of way 1, SQL> select s seq from dual model return all rows dimension by ( 0 d ) measures ( 0 s ) rules iterate (&n) ( s[iteration_number ] = decode( iteration_number, 0, 0, 1, 1, s[iteration_number-2] ) + nvl(s[iteration_number-1],0) ) / Enter value for n: 8 old 4: rules iterate (&n) ( new 4: rules iterate (8) ( SEQ ---------- 0 1 1 2 3 5 ...

In your organization you may assign a user who is only responsible to take data pump export. Suppose everyday evening he will be responsible to take a logical backup of the database. The minimum privilege need to perform data pump export operation is given below. 1)Create Session privilege. This is required to logon to database. 2)Create Table privilege. This is required as while doing export operation he needs to create a master table. 3)Read and write permission on a valid database directory. Or Create Directory privilege. 4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation. In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things - to run a full database Export or - to run a transport_tablespace job or - to run an Export DataPump job with the TRACE parameter or - to run an operation that exports a different schema. Now suppos...

Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution. We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger. Here is a test. Inside test schema I have made an example. Connect as test user and create three tables. SQL> conn test/test Connected. SQL> create table test(a number, b varchar2(4), c varchar2(8)); Table created. SQL> create table test1(a number, b varchar2(3), c varchar2(3)); Table created. SQL> create table test2(a number, b varchar2(3), c varchar2(3)); Table created. 2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2. SQL> create or replace trigger test_t before insert on test for each row be...

1)The AWR is the next evolution of the STATSPACK utility. 2)The AWR repository holds all of the statistics available in STATSPACK as well as some additional statistics which are not. 3)STATSPACK does not store the Active Session History (ASH) statistics which are available in the AWR dba_hist_active_sess_history view. 4)An important difference between STATSPACK and the AWR is that STATSPACK does not store history for new metric statistics introduced in Oracle10g. The key AWR views, dba_hist_sysmetric_history and dba_hist_sysmetric_summary. 5)The AWR also contains views such as dba_hist_service_stat , dba_hist_service_wait_class and dba_hist_service_name , which store history for performance cumulative statistics tracked for specific services. 6)The latest version of STATSPACK included with Oracle10g contains a set of specific tables, which track history of statistics that reflect the performance of the Oracle Streams feature. These tables are stats$streams_capture , stats$streams_apply...

By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days. The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM). However if you want to create, drop or modify snapshot manually then you can use DBMS_WORKLOAD_REPOSITORY procedures. To invoke these procedures, a user must be granted the DBA role. To see the result by using AWR just create snapshot by DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT and then execute your transaction or SQL statements and again create another snapshot by DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT and then follow Generating Automatic Workload Repository Reports section to see report between these two snapshots. Creating Snapshot BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END; / To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view. Dropping Range of Snapshots. Check the DBA_H...

1)SHOW PARAMETER option: If you use SQL*Plus then you can use SHOW PARAMETER parameter_name to display the values of parameters currently in use. Note that with toad or tora you can't use it as it is SQL*plus option. SQL>SHOW PARAMETERS sga_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 160M 2)Query from V$PARAMETER: You can query value column of this view to display the values of parameters currently in effect. SQL> SELECT VALUE FROM v$PARAMETER WHERE name='sga_target'; VALUE -------------------------------------------------------------------------------- 167772160 3)Query from V$PARAMETER2: You can query value column of this view to display the values of parameters currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row. 4)Query from V$SPPARAMET...

V$PARAMETER It displays the information about initialization parameters that are currently in effect for the session. V$SYSTEM_PARAMETER In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values. V$SPPARAMETER It displays the information about contents of the server parameter file. If a server parameter file was not used to start the instance, then ISSPECIFIED column contains FALSE value. V$SYSTEM_PARAMETER2 It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. You may mess up in this stage. An example will make you clear through these parameter as well as ALTER SYSTEM SET value settings. Let's experiment over audit_file_dest parameter. SQL> show parameter audit_file_dest NAME TYPE VALUE ------------------ ------ -------------------------------------- aud...

We know that any changes to database is recored in online redo logfiles. If your database archival mode on then online redo log files are archived which contains the database changes. With these logfile we can analysis any changes in database. In simple we can say an undo entry provides the values of data stored before a change and the redo entry provides the values of data stored after a change. Because undo segments are database changes, they also generate redo entries. So we can get them from online redo logs and then to archived logs. So from online redo logs and from archived redo logs we can get database redo and undo information. But online and archived logs have an unpublished format and are not human-readable. With the DBMS_LOGMNR package we can analysis redo log files and can get back undo and redo information in a human readable format. Another scenario of use of logminer is to investigate database past in time. With Flashback Query we can get prior values of the record in t...

1)CREATE MATERIALIZED VIEW definition size Limit Maximum size: 64K Bytes 2)GROUP BY clause size Limit Maximum length: Must fit within a single database block. 3)Indexes Limit Maximum per table: Unlimited Total size of indexed column: 75% of the database block size minus some overhead 4)Columns Limit Maximum Per table: 1000 Maximum Per index (or clustered index): 32 Maximum Per bitmapped index: 30 5)Constraints Maximum per column: Unlimited 6)Subqueries Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited Maximum levels of subqueries in a SQL statement in the WHERE clause: 255 7)Partitions Maximum length of linear partitioning key: 4 KB - overhead Maximum number of columns in partition key: 16 columns Maximum number of partitions allowed per table or index: 1024K - 1 8)Rows Maximum number per table: Unlimited 9)Stored Packages Maximum size: Limits typically range from 2000 to 3000 lines of code. 10)Trigger Cascade Limit Maxi...

1)BFILE: Maximum size: 4 GB-1 which is power(2,32)-1 bytes. Maximum size of the file name: 255 characters Maximum size of the directory name: 30 characters Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow. 2)BLOB: Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size. So, if db_block_size=8024K then maximum size=32T SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual; 4*1024*1024*1024*8*1024/1024/1024/1024/1024 ------------------------------------------- 32 As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB. (8 TB to 128 TB) Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000. 3)CHAR: Maximum size: 2000 bytes Minimum and Default Size: 1 byte 4)CHAR VARYING Maximum size:...

1)Data Block Size Limit Minimum: 2048 bytes; must be a multiple of operating system physical block size Maximum: Operating system dependent; Not more than 32 KB 2)Data Blocks Limit Minimum in initial extent of a segment: 2 data blocks Maximum per datafile: Platform dependent; typically power(2,22) - 1 blocks 3)Controlfiles Limit Number of control files: 1 minimum; 2 or more (on separate devices) strongly recommended. Size of a control file: Maximum of 20,000 * (database block size) 4)Datafiles Limit Maximum per tablespace: 1022 Maximum per database: 65533 Also, limited by the DB_FILES initialization parameter for a particular instance. 5)Database extents Limit Maximum per dictionary managed tablespace: 4 GB * physical block size. Maximum per locally managed (uniform) tablespace: 2 GB * physical block size. 6)Database file size Limit Limited by maximum operating system file size. 7)MAXEXTENTS Limit Default value: Derived from tablespace default storage or DB_BLOCK_SIZE initialization pa...