DBA Blogs

Table dependencies

Tom Kyte - Tue, 2016-11-08 17:26
To find databse table dependencies I ran below sql, select rpad( '*', (level-1)*2, '*' ) || table_name table_name from ( select p.table_name , p.constraint_name primary_cons , f.constraint_name foreign_cons , f.r_constr...
Categories: DBA Blogs

finding regular expression

Tom Kyte - Tue, 2016-11-08 17:26
Hi i am trying to match a pattern in a string. It is web log data so basically i need to look as follows find a comma followed by two spaces followed by anynumber of characters other than a quote follwed by = Eg: is string is abc=1, name='1...
Categories: DBA Blogs

Casting complex object IN parameter of a procedure multiple time for inserting into multiple tables VS Looping through the object and inserting into all the tables

Tom Kyte - Tue, 2016-11-08 17:26
Hello Tom First, thanks for all the help and advice you provide to the Oracle world. I have a procedure which takes IN parameter as a complex object (Ex. Array Starts with Product Events which contains Engines(object) which in turn contains par...
Categories: DBA Blogs

Receiving the error: "Couldn't switch user" while executing a job

Tom Kyte - Tue, 2016-11-08 17:26
Hi Experts, I have a job under oracle scheduler that executes a chain. This chain has 3 steps: Step 1: executes a stored procedure Step 2: executes a unix shell and Step 3 executes another stored procedure. The JOB used to work fine but suddenl...
Categories: DBA Blogs

Data Archival

Tom Kyte - Tue, 2016-11-08 17:26
Hi Tom, Couple of questions here There is requirement of data archival, one option i can find is create separate database and load data using dblink. Is there any efficient way to do archival? Post archival there is a requirement where c...
Categories: DBA Blogs

Regarding high CPU usage for index query

Tom Kyte - Tue, 2016-11-08 17:26
Hi As part of a query execution we observed high CPU usage. The query is using the indexed column in where condition. Table : Select * from mytable where mycol1 <= 200000 and xxxx This table is having index on column mycol1. This tab...
Categories: DBA Blogs

Loading The XML like data into columns

Tom Kyte - Tue, 2016-11-08 17:26
Hi Tom, I have a data in a csv file like this: something_here, "{ "entityValue" : { "vlanId" : { "type" : "string" , "value" : "121a" }, "vlanDescription" : { "type" : "string" , "value" : "asdf" ...
Categories: DBA Blogs

I am trying to access a function stored in a package and it gives me invalid sql statement error

Tom Kyte - Tue, 2016-11-08 17:26
HERE IS MY <b>PACKAGE</b> AND ITS <b>BODY</b>: CREATE OR REPLACE PACKAGE TBOOK AS FUNCTION TBOOK1(JID VARCHAR2) RETURN VARCHAR2; END; CREATE OR REPLACE PACKAGE BODY TBOOK AS JID2 VARCHAR2(6); JID1 VARCHAR2(6); FUNCTION TBOOK1(JID VARCHAR2)...
Categories: DBA Blogs

Data retrieval from two databases

Tom Kyte - Tue, 2016-11-08 17:26
Hi Team, I have requirement - Consider : 1. Table1 is holding data current 5 years of data in primary database 2. Table1 is holding data older 5 years of data in other database(archived) is there any way we can manage records retr...
Categories: DBA Blogs

Copy table data From One DB to Another DB

Tom Kyte - Mon, 2016-11-07 23:06
Hi Team, I need to Copy table data From One DB to Another DB. One approach I can recollect from one of the conversation asked in asktom.oracle.com is that create database link and simply execute - insert into local_table select * from table@...
Categories: DBA Blogs

export import

Tom Kyte - Mon, 2016-11-07 23:06
Hi team, I wanted to know that when i export entire schema from one database and import into anothere database then- Objects like - tables,functions,triggers,procedures,dblinks,synonyms,public synonyms and many more. Which objects export dur...
Categories: DBA Blogs

SQL not using index

Tom Kyte - Mon, 2016-11-07 23:06
Tom, There is an index on a table, but that index is not being used by SQL(even with hint). Can you please tell if something is wrong with the syntax? Below is the definition of the index on the table <b>XLA.XLA_TRANSACTION_ENTITIES</b> (PS:...
Categories: DBA Blogs

How to find the tables of a particular string

Tom Kyte - Mon, 2016-11-07 23:06
Hello there, I'm trying to find the name of the table and column in which it has the particular string. The below code searches for the string in the whole database and prints it out. However I wanted to use wild card because there are instance...
Categories: DBA Blogs

TEMPORARY TABLESPACE

Tom Kyte - Mon, 2016-11-07 23:06
HOW CAN I DETERMINE THAT WHAT SHOULD BE THE SIZE OF TEMPORARY TABLESPACE FOR ORACLE DATABASE? WHAT ARE THE MEASURES TO BE CONSIDERED FOR SIZING TEMPORARY TABLESPACE? HOW SHOULD I RESIZE THE TEMPORARY TABLESPACE SO THAT WE DO NOT ENCOUNTER ER...
Categories: DBA Blogs

Could not able to drop an empty tablespace. ORA-23515: materialized views and/or their indices exist in the tablespace

Tom Kyte - Mon, 2016-11-07 23:06
Hi Tom, I have been trying to drop a tablespace, but it is showing the below error. I've checked under dba_segments from any Mviews but I couldn't find anything. 1) SQL> drop tablespace GOLFX including contents and datafiles; drop tablespace ...
Categories: DBA Blogs

Drop Schema

Tom Kyte - Mon, 2016-11-07 23:06
Hi, If i Drop a shema with DROP SCHEMA <name> RESTRICT,Will oracle also drop tha USER associated with that Schema?
Categories: DBA Blogs

should i use users tablespace?

Tom Kyte - Mon, 2016-11-07 23:06
hi tom. i am junior dba. my senior dba said to me today that i should not create users in USERS tablespace, if this users create objects, cause this objects will be created in USERS tablespace and that is somehow bad. so my question to him was why...
Categories: DBA Blogs

Enqueue Bytes – Is that a Pun?

Pythian Group - Mon, 2016-11-07 13:53

Sometimes it is necessary to put on your uber-geek hat and start using cryptic bits of code to retrieve information from an Oracle database. Troubleshooting enqueue locking events in Oracle databases is one of the times some advanced SQL may be necessary.

Likely you have used SQL similar to the following when troubleshooting Oracle enqueue’s, probably in connection with row lock contention.

SQL# l
  1  SELECT
  2     s.username username,
  3     s.sid,
  4     e.event event,
  5     e.p1text,
  6     e.p1,
  7     e.state
  8  FROM v$session s, v$session_wait e
  9  WHERE s.username IS NOT NULL
 10     AND s.sid = e.sid
 11     AND e.event LIKE '%enq:%'
 12* ORDER BY s.username, UPPER(e.event)
 
USERNAME    SID EVENT                          P1TEXT                P1 STATE
---------- ---- ------------------------------ ---------- ------------- ----------
JKSTILL      68 enq: TX - ROW LOCK contention  name|mode     1415053318 WAITING
 
1 ROW selected.

The value for P1 is not very useful as is; Oracle has encoded the type of enqueue and the requested mode into the column. When working with current events such as when selecting from v$session, it is simple to determine the type of lock and the mode requested by querying v$lock, such as in the following example:

  1* SELECT sid, TYPE, request, block FROM v$lock WHERE sid=68 AND request > 0
SQL# /
 
 SID TY    REQUEST      BLOCK
---- -- ---------- ----------
  68 TX          6          0
 
1 ROW selected.

Session 68 is waiting on a TX enqueue with requested lock mode of 6. Seasoned Oracle DBA’s will recognize this as classic row lock contention.

Why bother to find out just which type of enqueue this is? There are many types of locks in Oracle, and they occur for differing reasons. The TX lock is interesting as it can occur not only in Mode 6 but Mode 4 as well; Mode 4 refers to locks that involve unique keys, such as when 2 or more sessions try to insert the same value for a primary key. The following example shows just that:

SQL# @s
 
USERNAME    SID EVENT                          P1TEXT                P1 STATE
---------- ---- ------------------------------ ---------- ------------- ----------
JKSTILL      68 enq: TX - ROW LOCK contention  name|mode     1415053316 WAITING
 
 
1 ROW selected.
 
SQL# @l
 
 SID TY    REQUEST      BLOCK
---- -- ---------- ----------
  68 TX          4          0
 
1 ROW selected.

Knowing just which lock mode is requested is vital, as the troubleshooting for TX Mode 4 locks will be different from what is used to troubleshoot Mode 6.

Though we can find the lock name and mode information in v$lock, there is still value in being able to decipher that cryptic P1 column.

The ASH and AWR facilities do not include any historical information for the lock name and mode; the P1 column found in v$active_session_history and dba_hist_active_sess_history does not have a corresponding dba_hist_lock view. Any research done after an event has occurred does require decoding this information.

Deciphering v$session.p1

Oracle Support document 34566.1 is the enqueue reference note that provides information needed to get the lock name and mode from the p1 column. As you will see this information is a bit puzzling.

The rest of this article will focus on TX Mode 6 locks. The value shown for this lock and mode in the P1 column is always 1415053318. Following is the SQL recommended by Oracle:

 SELECT chr(to_char(bitand(p1,-16777216))/16777215)||
         chr(to_char(bitand(p1, 16711680))/65535) "Lock",
         to_char( bitand(p1, 65535) )    "Mode"
    FROM v$session_wait
   WHERE event = 'enqueue'

As I currently have some planned row lock contention in a test database we can run this query:

  1   SELECT chr(to_char(bitand(p1,-16777216))/16777215)||
  2           chr(to_char(bitand(p1, 16711680))/65535) "Lock",
  3           to_char( bitand(p1, 65535) )    "Mode"
  4      FROM v$session_wait
  5*    WHERE event LIKE '%enq:%'
SQL# /
 
Lo Mode
-- ----------------------------------------
TX 4

Probably it is not very clear why this SQL works. Let’s try and understand it.
(Note that a small change had to be made to the WHERE clause.)

Converting the P1 value to hex may be helpful

1415053318 = 0x54580006

The two lower order bytes represent the lock mode that has been requested. This can be seen here to be 0x06, which is simple translate to decimal 6 (I can do this one in my head)

The next two bytes are also in hex and represent the two letters of the lock name.

0x54 = 84 = ‘T’
0x58 = 88 = ‘X’

Using string functions it is simple to extract the values from the hex string, convert them to numbers and retrieve the lock name and mode.

SQL# define p1 = 1415053318
 
SQL# l
  1  WITH hex AS (
  2     SELECT TRIM(to_char(&p1,'XXXXXXXXXXXXXXXX')) hexnum FROM dual
  3  ),
  4  hexbreak AS (
  5     SELECT hexnum
  6        , to_number(substr(hexnum,1,2),'XXXXXXXX') enq_name_byte_1
  7        , to_number(substr(hexnum,3,2),'XXXXXXXX') enq_name_byte_2
  8        , to_number(substr(hexnum,5),'XXXXXXXX') enq_mode
  9  FROM hex
 10  )
 11  SELECT
 12     hexnum
 13     , chr(enq_name_byte_1)
 14     || chr(enq_name_byte_2) enqueue_type
 15     , enq_mode
 16* FROM hexbreak
SQL# /
 
HEXNUM            EN   ENQ_MODE
----------------- -- ----------
54580006          TX          6

While that does work, my inner geek wants to investigate those bitmasks and find out why they work. Next are the bitmasks in decimal along with the hex equivalent.

-16777216 = 0xFFFFFFFFFF000000
 16777215 = 0xFFFFFF
 16711680 = 0xFF0000
    65535 = 0xFFFF

The bitand function is used to mask all unwanted bits to 0. The number is then divided by value needed to remove all of the now-zeroed-out lower order bytes.

The values being used as bitmasks are -16777216 and 16711680. The use of -16777216 does not seem to make sense. As the intent is to mask all but one byte, I would expect to find an FF surrounded by a number of zeroes. The bit mask of 16711680, however, looks fine.

Now let’s run the Oracle support query again, but modified to show just the integer values rather than converting them to ASCII.

 
  1  SELECT bitand(p1,-16777216)/16777215,
  2           bitand(p1, 16711680)/65535,
  3           bitand(p1, 65535)
  4      FROM v$session_wait
  5*    WHERE event LIKE '%enq:%'
SQL# /
 
BITAND(P1,-16777216)/16777215 BITAND(P1,16711680)/65535 BITAND(P1,65535)
----------------------------- ------------------------- ----------------
                    84.000005                88.0013428                6

Well, that is interesting. An implicit conversion is taking place with to_char() that is removing the decimal portion of these numbers. Is that being done with trunc(), round(), or something else? I don’t know the answer to that. What seems more important is just doing the math correctly.

There are a couple of things here that can be changed to make this work as expected.

A New BitMask

Let’s modify the first bitmask to something that seems more reasonable than -16777216. Let’s use this instead, as it masks only the single byte we need:

4278190080 = 0xFF000000

Lets’ try it out:

SQL# l
  1  SELECT bitand(p1,4278190080)/16777215,
  2           bitand(p1, 16711680)/65535,
  3           bitand(p1, 65535)
  4      FROM v$session_wait
  5*    WHERE event LIKE '%enq:%'
SQL# /
 
BITAND(P1,4278190080)/16777215 BITAND(P1,16711680)/65535 BITAND(P1,65535)
------------------------------ ------------------------- ----------------
                     84.000005                88.0013428                6

While the new bitmask didn’t break anything, it does not appear to have helped either.

Off By One Error

The solution is to consider the divisors used to remove the lower order zeroes; each of them is off by one. That is easy enough to verify:

SQL# l
  1  SELECT bitand(p1,4278190080)/16777216,
  2           bitand(p1, 16711680)/65536,
  3           bitand(p1, 65535)
  4      FROM v$session_wait
  5*    WHERE event LIKE '%enq:%'
SQL# /
 
BITAND(P1,4278190080)/16777216 BITAND(P1,16711680)/65536 BITAND(P1,65535)
------------------------------ ------------------------- ----------------
                           84                        88                6

Ah, that did it! But what was the problem previously?

Old Divisor Values

The original divisors are off by 1, which does not completely remove the lower order values.

 16777215 = 0xFFFFFF
    65535 = 0xFFFF

Increasing each by one has the desired effect.

New Divisor Values
 16777216 = 0x1000000
    65536 = 0x10000
Conclusion

Those odd bitmasks have been in the back of my mind for some time, and today it seemed like a good idea to dig in and find out more about them. It isn’t too hard to imagine that in some cases the wrong values might be returned, leading to some long and unproductive troubleshooting sessions.

There is a demo script enqueue-bitand.sql containing much of the SQL found in this article. There is also a script awr-top-sqlid-events.sql that incorporates the enqueue lock decoding. This script could be made better than it is, so please issue a pull request if you have some useful modifications.

Categories: DBA Blogs

Moving objects from one tablespace to another

DBA Scripts and Articles - Mon, 2016-11-07 10:57

This script will help you to move all objects from one tablespace to another, note that you will need downtime to move tables to other tablespace with this script, the operation can be done online using the dbms_redefinition package thought. Moving objects [crayon-5820d56021c6e347798014/] You can also modify the script to move objects of a specific … Continue reading Moving objects from one tablespace to another

The post Moving objects from one tablespace to another appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Flashback Database -- 1 : Introduction to Operations

Hemant K Chitale - Mon, 2016-11-07 04:24
Continuing on my previous post,  ....

In 11gR2,  ALTER DATABASE FLASHBACK ON   and OFF can be executed when the database is OPEN.  Setting FLASHBACK OFF results in deletion of all Flashback Files.

Here is some information that I have pulled from my test database environment :

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL>
SQL> select oldest_flashback_scn, oldest_flashback_time,
2 retention_target, flashback_size
3 from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET FLASHBACK_SIZE
-------------------- ------------------ ---------------- --------------
7140652 07-NOV-16 10:53:30 180 314572800

SQL> select sysdate from dual;

SYSDATE
------------------
07-NOV-16 17:46:54

SQL>
SQL> select begin_time, end_time, flashback_data, estimated_flashback_size
2 from v$flashback_database_stat
3 order by begin_time;

BEGIN_TIME END_TIME FLASHBACK_DATA ESTIMATED_FLASHBACK_SIZE
------------------ ------------------ -------------- ------------------------
06-NOV-16 18:56:28 06-NOV-16 21:20:55 202129408 251873280
06-NOV-16 21:20:55 07-NOV-16 09:53:26 107102208 62054400
07-NOV-16 09:53:26 07-NOV-16 10:53:30 51609600 67866624
07-NOV-16 10:53:30 07-NOV-16 13:14:45 10682368 60887040
07-NOV-16 13:14:45 07-NOV-16 14:14:51 66002944 67986432
07-NOV-16 14:14:51 07-NOV-16 15:14:57 10018816 66112512
07-NOV-16 15:14:57 07-NOV-16 16:15:01 10190848 64441344
07-NOV-16 16:15:01 07-NOV-16 17:15:05 53559296 68751360
07-NOV-16 17:15:05 07-NOV-16 17:47:57 52862976 0

9 rows selected.

SQL>
SQL> select log#, sequence#, bytes/1048576 Size_MB, first_time
2 from v$flashback_database_logfile
3 order by sequence#;

LOG# SEQUENCE# SIZE_MB FIRST_TIME
---------- ---------- ---------- ------------------
6 6 50 07-NOV-16 09:00:46
1 7 50 07-NOV-16 10:36:01
2 8 50 07-NOV-16 13:13:22
3 9 50 07-NOV-16 13:43:28
4 10 50 07-NOV-16 16:43:49
5 11 50 07-NOV-16 17:44:42

6 rows selected.

SQL>


Firstly, we note (as in my previous blog post), that the available flashback that is from 10:53am to 5:46pm (almost 7hours) exceeds the Flashback Target of 3hours (180minutes).  Apparently, Flashback Logfiles 1 to 5 have already been purged (but I find no entries for the deletions in the alert log).

Note how the "earliest time" does not match in all three views.  The OLDEST_FLASHBACK_TIME is 10:53am although V$FLASHBACK_DATABASE_STAT reports statistics from the previous day (I had enabled Flashback in the database at 18:56:27 of 06-Nov) while V$FLASHBACK_DATABASE_LOGILE shows an existing logfile from 09:00am to 10:36am.

Let me do a Flashback.  I must rely on the V$FLASHBACK_DATABASE_LOG view to know that I  cannot flashback beyond 10:53am.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL>
SQL> flashback database to timestamp trunc(sysdate)+11/24;

Flashback complete.

SQL>
SQL> alter database open read only; --- to verify data if necessary

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL>


A FLASHBACK DATABASE requires an OPEN RESETLOGS to open READ WRITE.

Let's look at the alert log for messages about the Flashback operation itself :

Mon Nov 07 17:56:36 2016
flashback database to timestamp trunc(sysdate)+11/24
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
Flashback Media Recovery Log /u02/FRA/ORCL/archivelog/2016_11_07/o1_mf_1_81_d2052ofj_.arc
Mon Nov 07 17:56:43 2016
Incomplete Recovery applied until change 7141255 time 11/07/2016 11:00:01
Flashback Media Recovery Complete
Completed: flashback database to timestamp trunc(sysdate)+11/24
Mon Nov 07 17:57:08 2016
alter database open read only


What happens if I disable and re-enable Flashback ?

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter database flashback off;

Database altered.

SQL>

From the alert log :
Mon Nov 07 18:03:02 2016
alter database flashback off
Stopping background process RVWR
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y32vjv_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y32xq0_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y3bhkx_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y3dd8r_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y6r6bf_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1ycky3v_.flb
Flashback Database Disabled
Completed: alter database flashback off

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter database flashback on;

Database altered.

SQL>

From the alert log :
Mon Nov 07 18:04:21 2016
alter database flashback on
Starting background process RVWR
Mon Nov 07 18:04:21 2016
RVWR started with pid=30, OS id=12621
Flashback Database Enabled at SCN 7142426
Completed: alter database flashback on

From the FRA :
[oracle@ora11204 flashback]$ pwd
/u02/FRA/ORCL/flashback
[oracle@ora11204 flashback]$ ls -ltr
total 102416
-rw-rw----. 1 oracle oracle 52436992 Nov 7 18:04 o1_mf_d20nf7wc_.flb
-rw-rw----. 1 oracle oracle 52436992 Nov 7 18:05 o1_mf_d20nf5nz_.flb
[oracle@ora11204 flashback]$

SQL> alter session set nls_date_Format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select log#, sequence#, bytes/1048576 Size_MB, first_time
2 from v$flashback_database_logfile
3 order by sequence#;

LOG# SEQUENCE# SIZE_MB FIRST_TIME
---------- ---------- ---------- ------------------
2 1 50
1 1 50 07-NOV-16 18:04:22

SQL>



So, I can set FLASHBACK OFF and ON when the database is OPEN.  (But I can't execute a FLASHBACK TO .... with the database OPEN).
.
.
.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs