'오라클'에 해당되는 글 22건

  1. 2013.11.15 Oracle Trim Function
  2. 2013.08.21 Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology
  3. 2013.04.09 A Comparison of Oracle's DATE and TIMESTAMP Datatypes
  4. 2013.04.09 Comparison Timestamp vs. Date Data Type
  5. 2013.03.06 Oracle PL/SQL 기초 교육 문서
  6. 2013.01.21 Oracle Data Record별 Text File 내보내는 방법 1
  7. 2012.07.26 Compound Triggers in Oracle 11g
  8. 2012.05.15 ORA-20023: Missing start and end values for time model stat
  9. 2011.08.11 DataPump Export (EXPDP) Client Gets UDE-8 ORA-31626 ORA-39086 [ID 549781.1] 3
  10. 2011.08.11 Oracle 10gR2이상에서 Automatic Tuning of Undo_retention Causes Space Problems 1

Oracle Trim Function

Oracle 2013. 11. 15. 18:39

TRIM

Syntax

Description of trim.gif follows
Description of the illustration trim.gif

Purpose

TRIM enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, then you must enclose it in single quotation marks.

  • If you specify LEADING, then Oracle Database removes any leading characters equal to trim_character.

  • If you specify TRAILING, then Oracle removes any trailing characters equal to trim_character.

  • If you specify BOTH or none of the three, then Oracle removes leading and trailing characters equal to trim_character.

  • If you do not specify trim_character, then the default value is a blank space.

  • If you specify only trim_source, then Oracle removes leading and trailing blank spaces.

  • The function returns a value with datatype VARCHAR2. The maximum length of the value is the length of trim_source.

  • If either trim_source or trim_character is null, then the TRIM function returns null.

Both trim_character and trim_source can be VARCHAR2 or any datatype that can be implicitly converted to VARCHAR2. The string returned is of VARCHAR2 datatype if trim_source is a character datatype and a LOB if trim_source is a LOB datatype. The return string is in the same character set as trim_source.

Examples

This example trims leading zeros from the hire date of the employees in the hr schema:

SELECT employee_id,
      TO_CHAR(TRIM(LEADING 0 FROM hire_date))
      FROM employees
      WHERE department_id = 60
      ORDER BY employee_id;

EMPLOYEE_ID TO_CHAR(T
----------- ---------
        103 3-JAN-90
        104 21-MAY-91
        105 25-JUN-97
        106 5-FEB-98
        107 7-FEB-99


LTRIM

Syntax

Description of ltrim.gif follows
Description of the illustration ltrim.gif

Purpose

LTRIM removes from the left end of char all of the characters contained in set. If you do not specify set, then it defaults to a single blank. If char is a character literal, then you must enclose it in single quotation marks. Oracle Database begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

Both char and set can be any of the datatypes CHARVARCHAR2NCHARNVARCHAR2CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if char is a character datatype, NVARCHAR2 if char is a national character datatype, and a LOB if char is a LOB datatype.


Examples

The following example trims the redundant first word from a group of product names in the oe.products table:

SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name"
   FROM products
   WHERE product_name LIKE 'Monitor%';

PRODUCT_NAME         Short Name
-------------------- ---------------
Monitor 17/HR        17/HR
Monitor 17/HR/F      17/HR/F
Monitor 17/SD        17/SD
Monitor 19/SD        19/SD
Monitor 19/SD/M      19/SD/M
Monitor 21/D         21/D
Monitor 21/HR        21/HR
Monitor 21/HR/M      21/HR/M
Monitor 21/SD        21/SD
Monitor Hinge - HD   Hinge - HD
Monitor Hinge - STD  Hinge - STD

RTRIM

Syntax

Description of rtrim.gif follows
Description of the illustration rtrim.gif

Purpose

RTRIM removes from the right end of char all of the characters that appear in set. This function is useful for formatting the output of a query.

If you do not specify set, then it defaults to a single blank. If char is a character literal, then you must enclose it in single quotation marks. RTRIM works similarly to LTRIM.

Both char and set can be any of the datatypes CHARVARCHAR2NCHARNVARCHAR2CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if char is a character datatype, NVARCHAR2 if expr1 is a national character datatype, and a LOB if char is a LOB datatype.

Examples

The following example trims all the right-most occurrences of period, slash, and equal sign from a string:

SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" FROM DUAL;
 
RTRIM exam
----------
BROWNING:

reference : Oracle® Database SQL Language Reference - Oracle11gR1


그동안 너무 RTRM과 LTRIM만 사용한듯.... 

:     

TISTORY에 Login하려면 여기를 누르세요.


Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology

Oracle 2013. 8. 21. 18:14

Oracle Database 12c : Tom’s Top 12 Things About the Latest Generation of Database Technology

Tom’s Top 12 Things About the Latest Generation of Database Technology
  • Functions (and procedures used within those functions) can be defined in the WITH clause. Performance boost compared to regular unit defintion. Pragma to allow regular functions to benefit from these performance benefits.
  • Default value of column can use a sequence.nextval.
  • Identity columns : Multiple levels of control of how it is used. Can use simple or more complex syntax.
  • Metadata only default of optional columns. Previous versions this was possible only for mandatory columns.
  • VARCHAR2(32767) in the database. Less than 4K is stored inline. More than 4K is stored out of line, similar to LOB, but simpler. Not available by default.
  • Top-N now using Row limiting clause eg. “OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY”. Similar to mySQL syntax.
  • Row pattern matching. Quite a lot of new analytic syntax here.
  • Partitioning Improvements:
    - Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.
    - Cascade for TRUNCATE and EXCHANGE partition.
    - Multiple partition operations in a single DDL
    - Online move of a partition(without DBMS_REDEFINTIION).
    - Interval  + Reference Partitioning.
  • Adaptive Execution Plans:
    - If the optimizer notices the cardinality is not what is expected, so the current plan is not optimal, it can alter subsequent plan operations to take allow for the differences between the estimated and actual cardinalities.
    - The stats gathered during this process are persisted as Adaptive Statistics, so future decisions can benefit from this.
    - You will see STATISTICS COLLECTOR steps in the SQL Trace. Can make the trace harder to read as it can contain information about the expected plan and the actual plan.
  • Enhanced Statistics:
    -  Some dynamic sampling operations are persistent, so they are not lost when the SQL is aged out.
    - Hybrid histograms. When the number of distinct values is greater than 254, “almost popular” values can get “lost” in the mix. A single bucket can now store the popularity of than value, effectively increasing the number of buckets, without actually increasing it.
    - Possible the max number of buckets can be increased based on a parameter. (demo grounds)
    - Statistics gathered during loads. CTAS and INSERT … SELECT automatically compute stats.
    - Global temporary tables can have “session private statistics”. Previously, we had one-size-fits-all.
  • Temporary Undo (ALTER SESSION SET temp_undo_enabled=true):
    - UNDO for temporary tables can now be managed in TEMP, rather than the regular UNDO tablespace.
    - Reduces contents of regular UNDO, allowing better flashback operations.
    - Reduces the size of redo associated with recovering the regular UNDO tablespace.
  • Data Optimization:
    - Information Lifecycle Management: Uses heat map. Colder data is compressed and moved to lower tier storage. Controlled by declarative DDL policy.
  • Transaction Guard:
    - If a failure happens, your application may not know the actual status of a transaction. If it was successful, issuing it again could cause a duplication transaction.
    - In these cases, you can mark a transaction with an “unknown” state (as far as the application is concerned) as failed, so even though they may have been successful, it will never be considered, or recovered. You’ve guaranteed the outcome.
  • Pluggable database:
    - Oracle provided metadata and data is kept in the container database (CDB).
    - User metadata and data is kept in the plugable database (PDB) .
    - One container can have multiple plugable databases.
    - No namespace clashes. Allows public synonyms and database links at the PDB level, rather than the CBD level.
    - Cloning is quick and simple as only user metadata and data needs to be cloned.
    - Upgrades have the potential to just unplug from old version (12cR1) to new version (12cR2).
    - Reduce total resource usage is reduced on lower use databases.

Oracle Database Optimizer: An Insider’s View of How the Optimizer Works
Oracle database 12c is the first step on the way to making an adaptive, or self-learning optimiser.
Alternative subplans are precomputed and stored in the cursor, so no new hard parsing will be needed as part of the adaption of an already executing plan. Statistics collectors are included in the plan execution. If the collectors cross a threshold, the plan might switch during execution from a nested loops to a hash join.
You can see information about the adaptive actions that have occurred using the DBMS_XPLAN package, with the format of “+all_dyn_plan +adaptive”. If a plan has been adapted, you will see it indicated in the  v$sql.is_resolved_dynamic_plan column.
If this functionality scares you, you can turn it off using the OPTIMIZER_APADPTIVE_REPORTING_ONLY parameter. Same work is done, but no actual adaptive action is taken.
During parallel execution, collectors can influence the distribution method (HASH > Distribution). Shown in the plan as the HYBRID HASH operations.
Dynamic statistics replace dynamic sampling. The resulting stats are cached as SHARED DYNAMIC STATS specific for the statement, including the bind values. This information is used for any session using the same statement.
Cardinality feedback can be used to re-optimize subsequent operations. Join statistics are monitored. Works with adaptive cursor sharing. Persisted on disk. New column v$sql.is_reoptimizable shows that a subsequent run will take this into consideration. Collectors are kept, even if the SQL statement is killed part way through. The plan shows that cardinality feedback is used.
SQL Plan Directives are based on a SQL phrase (a specific join) rather than the whole statement. Cached in the directive cache, but persisted in the SYSAUX tablespace. Managed using the DBMS_SPD package.
Information gathered by the optimizer, may prompt automatic creation of column groups, so next time stats are gathered, the extended stats will be gathered.

What’s New in Security in the Latest Generation of Database Technology
  • Privilege Analysis:
    - Track direct privileges and privileges via roles being used, so you can determine the least privileges needed.
    - Monitoring controlled using DBMS_PRIVILEGE_CAPTURE.
    - Report what is used and what is not used.
  • Data Redaction: A variation in column masking of VPD, but it doesn’t just blank the value and still allows queries against the column in the WHERE clause.
  • Enhanced Security of Audit Trail:
    - Single unified audit trail.
    - Extension of the audit management package.
    - Multiple audit management privileges.
  • Encryption Enhancements:
    - Allow SQL creation and management of wallets, rather than command line utilities. Allows easier remote management.
    - Export and import wallets/keys between plugable databases.
    - Storage of wallets in ASM.
    - Much more…
  • Code-Based Access Control (CBAC):
    - A PL/SQL unit can have roles granted to it.
    - When the unit runs, any dynamic SQL running can have the privileges granted via the role.
    - Doesn’t affect compile time, so focussing very much on dynamic SQL.
    - Useful on invoker rights, since now the PL/SQL can run with user privileges and explicitly granted roles for the unit.
  • Invoker Rights:
    - INHERITED RIGHTS : Control accidental privilege escalation when a privileged user calls an invoker rights unit containing malicious code.
    - Invokers rights for views.
  • Separation of Duties:
    - SYSDBA – God
    - SYSOPER – More limited than SYSDBA, but still very powerful.
    - SYSBACKUP – Just enough to do a backup.
    - SYSDG – Just enough for data guard administration.
    - SYSKM – Just enough to perform basic key management tasks.
    - Roles for audit management.
출처 : ORACLE-BASE

 

:     

TISTORY에 Login하려면 여기를 누르세요.


A Comparison of Oracle's DATE and TIMESTAMP Datatypes

Oracle 2013. 4. 9. 09:50

A Comparison of Oracle's DATE and TIMESTAMP Datatypes

By James Koopmann

If you want to store date and time information in Oracle, you really only have two different options for the column's datatype. Lets take a quick look at these two datatypes and what they offer.

DATE datatype

This is the datatype that we are all too familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved later in this article when we discuss the TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date as in Listing A.

 

LISTING A:

Formatting a date

SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table;
Date
---------------------------
06/20/2003 16:55:14
06/26/2003 11:16:36

 

About the only trouble I have seen people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned. Check out Listing B for my solution on how to extract the individual time intervals for a subtraction of two dates. I am aware that the fractions could be reduced but I wanted to show all the numbers to emphasize the calculation.

 

LISTING B:

Determine the interval breakdown between two dates for a DATE datatype

  1         SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
  2         TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
  3         trunc(86400*(date2-date1))-
  4         60*(trunc((86400*(date2-date1))/60)) seconds,
  5         trunc((86400*(date2-date1))/60)-
  6         60*(trunc(((86400*(date2-date1))/60)/60)) minutes,
  7         trunc(((86400*(date2-date1))/60)/60)-
  8         24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
  9         trunc((((86400*(date2-date1))/60)/60)/24) days,
 10         trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks
 11*        FROM date_table
DATE1             DATE2                SECONDS    MINUTES      HOURS       DAYS      WEEKS
----------------- ----------------- ---------- ---------- ---------- ---------- ----------
06202003:16:55:14 07082003:11:22:57         43         27         18         17          2
06262003:11:16:36 07082003:11:22:57         21          6          0         12          1


 

TIMESTAMP datatype

One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function as I do in Listing C. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a straight INSERTSELECT FROM and Oracle will do the conversion for you. Look at Listing D for a formatting of the new TIMESTAMP datatype where everything is the same as formatting the DATE datatype as we did in Listing A. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at Listing E. In Listing E, we are only showing 3 place holders for the fractional seconds.

 

 

LISTING C:

Convert DATE datatype to TIMESTAMP datatype

SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;
Date
-----------------------------------------------------
20-JUN-03 04.55.14.000000 PM
26-JUN-03 11.16.36.000000 AM

 

 

Formatting of the TIMESTAMP datatype

  1  SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table
Date
-------------------
06/20/2003 16:55:14
06/26/2003 11:16:36

 

 

LISTING E:

Formatting of the TIMESTAMP datatype with fractional seconds

 1  SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
Date
-----------------------
06/20/2003 16:55:14:000
06/26/2003 11:16:36:000

 

 

Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE

datatype. Look at what happens when you just do straight subtraction of the columns in Listing F. As you can see, the results are much easier to recognize, 17days, 18hours, 27minutes, and 43seconds for the first row of output. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen in Listing G.


LISTING F:

Straight subtraction of two TIMESTAMP datatypes

1  SELECT time1,  time2,  (time2-time1)
  2*   FROM date_table
TIME1                          TIME2                          (TIME2-TIME1)
------------------------------ ----------------------------   ----------------------
06/20/2003:16:55:14:000000     07/08/2003:11:22:57:000000     +000000017 18:27:43.000000
06/26/2003:11:16:36:000000     07/08/2003:11:22:57:000000     +000000012 00:06:21.000000

 

 

LISTING G:

Determine the interval breakdown between two dates for a TIMESTAMP datatype

  1  SELECT time1,
  2         time2,
  3         substr((time2-time1),instr((time2-time1),' ')+7,2)                 seconds,
  4         substr((time2-time1),instr((time2-time1),' ')+4,2)                 minutes,
  5         substr((time2-time1),instr((time2-time1),' ')+1,2)                 hours,
  6         trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))   days,
  7         trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
  8*   FROM date_table
TIME1                       TIME2                      SECONDS MINUTES HOURS DAYS WEEKS
-------------------------   -------------------------- ------- ------- ----- ---- -----
06/20/2003:16:55:14:000000  07/08/2003:11:22:57:000000  43     27      18    17    2
06/26/2003:11:16:36:000000  07/08/2003:11:22:57:000000  21     06      00    12    1

 

 

System Date and Time

In order to get the system date and time returned in a DATE datatype, you can use the SYSDATE function such as :

SQL> SELECT SYSDATE FROM DUAL;

In order to get the system date and time returned in a TIMESTAMP datatype, you can use the SYSTIMESTAMP function such as:

SQL> SELECT SYSTIMESTAMP FROM DUAL;

You can set the initialization parameter FIXED_DATE to return a constant value for what is returned from the SYSDATE function. This is a great tool for testing date and time sensitive code. Just beware that this parameter has no effect on the SYSTIMESTAMP function. This can be seen in Listing H.

 

 

LISTING H:

Setting FIXED_DATE and effects on SYSDATE and SYSTIMESTAMP

SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
System altered.

SQL> select sysdate from dual;
SYSDATE
---------
01-JAN-03

SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------
09-JUL-03 11.05.02.519000 AM -06:00

 

 

When working with date and time, the options are clear. You have at your disposal the DATE and TIMESTAMP datatypes. Just be aware, while there are similarities, there are also differences that could create havoc if you try to convert to the more powerful TIMESTAMP datatype. Each of the two has strengths in simplicity and granularity. Choose wisely.

:     

TISTORY에 Login하려면 여기를 누르세요.


Comparison Timestamp vs. Date Data Type

Oracle 2013. 4. 9. 09:36

Michael -- Thanks for the question regarding "Timestamp vs. Date - is Date still appropriate for new systems?", version 10.2

Submitted on 26-Nov-2006 2:49 UTC
Last updated 18-Feb-2010 9:54

You Asked

The new Timestamp datatypes are described as extensions of the Date datatype in Oracle's 
documentation.

They are clearly more powerful and include capabilities such as tracking times down to 
milliseconds, incorporating time zones, etc.

It's also generally accepted that you should minimize the complexity of a system where 
possible, and that one way of doing this is not to use redundant entities (in the general 
sense, not the ER diagram sense).

Given these points, is there any reason to still use Dates in new system development?  
Are there issues with performance, compatibility, etc?  Or is this like the switch from 
LONG and LONG RAW to LOBs where all new development should use the LOB?  Oracle's 
decision not to extend Date to also include Date With Timezone and Date With Local 
Timezone suggests that Oracle doesn't see the point in extending Date... presumably 
because it will be deprecated and replaced with Timestamp.

-----------------------

BTW, on a closely related topic, I don't understand the logic behind making Timestamp 
With Time Zone and Timestamp With Local Time Zone datatypes.

Seems to be that a database (or any other system) should store all Date / Times in a 
canonical form - presumably seconds since an agreed on starting second.  Then all of this 
Time Zone stuff is just a matter of Locale and client settings - my client should specify 
its Timezone to the Server and then all dates / times should be converted to the 
canonical form using that timezone unless I specify another time zone together with the 
date / time.

The With Local Timezone and With Timezone datatypes seem to describe how to present the 
information to clients more than what the datatype is. 

and we said...

I think dates will be here for a long time to come.

Timestamp is an ANSI thing, for compliance. 

The choice is yours.

And you cannot really store things in a canonical format - there are too many variations.

Dates are 7 bytes always

Timestamps are also 7 (no fractional seconds)
or 11 (fractional seconds)
or 13 (fractional seconds with timezone)

Probably - just using timestamp would be good going forward (there might be tools that 
cannot "deal" correctly with a timestamp yet - they would cause it to convert to a date - 
you'll use to_timestamp and such with them).

But in reality, date is just coming out of my fingers still :)  I typically have no need 
for the fractional seconds nor timezone. 

Reviews    
3 stars What about variations?   November 26, 2006 - 10am UTC
Reviewer: Mike Friedman from Shenzhen, China
Mind explaining this a bit more?

"And you cannot really store things in a canonical format - there are too many variations."

Does this mean that Oracle doesn't store dates internally as seconds since a reference date / time? 
 I know Windows does... and they have to deal with as many variations as Oracle, don't they? 


Followup   November 26, 2006 - 11am UTC:

The date datatype does, but not a "seconds since something".  

(windows cannot do a wide range of dates?  hmmm, you might not be entirely accurate on that seconds 
thing, I'll bet windows uses more than one format to store dates, or they cannot store dates that 
fall outside a very very very small range!)

<quote src=Expert Oracle Database Architecture>
DATE Type

The DATE type is a fixed-width 7-byte date/time datatype. It will always contain the seven 
attributes of the century, the year within the century, the month, the day of the month, the hour, 
the minute, and the second. Oracle uses an internal format to represent that information, so it is 
not really storing 20, 05, 06, 25, 12, 01, 00 for June 25, 2005, at 12:01:00. Using the built-in 
DUMP function, we can see what Oracle really stores:

ops$tkyte@ORA10G> create table t ( x date );
Table created.
 
ops$tkyte@ORA10G> insert into t (x) values
  2  ( to_date( '25-jun-2005 12:01:00',
  3             'dd-mon-yyyy hh24:mi:ss' ) );
1 row created.
 
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
 
X         D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1

The century and year bytes (the 120,105 in the DUMP output) are stored in an excess-100 notation. 
You would have to subtract 100 from them to determine the correct century and year. The reason for 
the excess-100 notation is support of BC and AD dates. If you subtract 100 from the century byte 
and get a negative number, it is a BC date, for example:

ops$tkyte@ORA10G> insert into t (x) values
  2  ( to_date( '01-jan-4712bc',
  3             'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
 
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
 
X         D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1

So, when we insert 01-JAN-4712BC, the century byte is 53 and 53 – 100 = –47, the century we 
inserted. Because it is negative, we know that it is a BC date. This storage format also allows the 
dates to be naturally sortable in a binary sense. Since 4712 BC is “less than” 4710 BC, we’d like a 
binary representation that supports that. By dumping those two dates, we can see that 01-JAN-4710BC 
is “larger” than the same day in 4712 BC, so they will sort and compare nicely:

ops$tkyte@ORA10G> insert into t (x) values
  2  ( to_date( '01-jan-4710bc',
  3             'dd-mon-yyyybc hh24:mi:ss' ) );
1 row created.
 
ops$tkyte@ORA10G> select x, dump(x,10) d from t;
 
X         D
--------- -----------------------------------
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
01-JAN-10 Typ=12 Len=7: 53,90,1,1,1,1,1

The month and day bytes, the next two fields, are stored naturally, without any modification. So, 
June 25 used a month byte of 6 and a day byte of 25. The hour, minute, and second fields are stored 
in excess-1 notation, meaning we must subtract 1 from each component to see what time it really 
was. Hence midnight is represented as 1,1,1 in the date field.

This 7-byte format is naturally sortable, as you have seen—it is a 7 byte field that can be sorted 
in a binary fashion from small to larger (or vice versa) very efficiently. Additionally, its 
structure allows for easy truncation, without converting the date into some other format. For 
example, truncating the date we just stored, 25-JUN-2005 12:01:00, to the day (remove the hours, 
minutes, seconds) is very straightforward. Just set the trailing three bytes to 1,1,1 and the time 
component is as good as erased. 
</quote>


Timestamps get more complicated - there are timestamps without timezones, with local timezones - 
you cannot store those in "one fashion" - one preserves the original timezone for example (so you 
cannot put it into UTC, you need to remember what timezone it used to be) and the other puts it 
into the database timezone.
 

5 stars Thanks   November 26, 2006 - 12pm UTC
Reviewer: Michael Friedman from Shenzhen, China


3 stars to_timestamp   September 10, 2008 - 8pm UTC
Reviewer: Paras Bansal from Milpitas, CA
Can you explain this - 

SELECT cast(to_timestamp('01-may-2005 23:59:59.501','dd-mon-yyyy hh24:mi:ss:ff') AS DATE) FROM 
dual;

It returns me 2nd may as DATE. Can we do something so that it truncs the millisec portion and we 
get only 1st May.

regards,
Paras Bansal


Followup   September 16, 2008 - 11am UTC:

substr the string you are trying to convert and just use to_date. 

ops$tkyte%ORA10GR2> SELECT to_date(substr( '01-may-2005 23:59:59.501', 1, 20),'dd-mon-yyyy 
hh24:mi:ss') from dual;

TO_DATE(SUBSTR('01-M
--------------------
01-may-2005 23:59:59


2 stars curious TO_TIMESTAMPing around   September 16, 2008 - 2pm UTC
Reviewer: Duke Ganote from Amelia, Ohio USA
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 16 13:35:17 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the OLAP and Data Mining Scoring Engine options

[glogin.sql includes:
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';
]

select cast(ts1 as date) dt1
     , ts1
     , cast(ts2 as date) dt2
     , ts2
  from
(
select to_timestamp('01-may-2005 23:59:59.501'
                   ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
     , systimestamp ts2
 from dual
)
/
DT1                 
TS1
DT2
TS2
--------------------------------------------------
2005-05-02 00:00:00 
2005-05-01 23:59:59.501000000
2008-09-16 13:33:39
16-SEP-08 01.33.38.512485 PM -04:00


Followup   September 16, 2008 - 11pm UTC:

do you have access to support to file a bug? 
1 stars where is the bug ?   September 17, 2008 - 4am UTC
Reviewer: Sokrates 
sorry, I don't see it.
Can you please explain ?
What was expected output ?


Followup   September 17, 2008 - 9am UTC:



when you cast a to_timestamp value as a date - the time component disappeared. 

when you cast a timestamp value as a date - the time component did NOT disappear. 
3 stars Isn't it just rounding   February 16, 2010 - 8pm UTC
Reviewer: Warwick from Sydney, Australia
Hi Tom, 

Isn't this due to rounding? I tried this using both 9i and 11r1 

alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';


Running the original query returned the results above. Changing it to 
select cast(ts1 as date) dt1
     , ts1
     , cast(ts2 as date) dt2
     , ts2
  from
(
select to_timestamp('01-may-2005 23:59:58.501'
                   ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
     , systimestamp ts2
 from dual
)
/

DT1
TS1
DT2
TS2
---------------------
2005-05-01 23:59:59
2005-05-01 23:59:58.501000000
2010-02-17 12:49:57
17-FEB-10 12.49.56.986028 PM +11:00


Cheers 
Warwick 



Followup   February 17, 2010 - 8am UTC:

9i and 10g (and 11gr1) all lose the time component: 

ops$tkyte%ORA9IR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';

Session altered.

ops$tkyte%ORA9IR2> alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';

Session altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select cast(ts1 as date) dt1
  2       , ts1
  3       , cast(ts2 as date) dt2
  4       , ts2
  5    from
  6  (
  7  select to_timestamp('01-may-2005 23:59:59.501'
  8                     ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
  9       , systimestamp ts2
 10   from dual
 11  )
 12  /

DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2010-02-17 09:54:28
17-FEB-10 09.54.27.579168 AM -05:00




10g: 
ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


ops$tkyte%ORA10GR2> select cast(ts1 as date) dt1
  2       , ts1
  3       , cast(ts2 as date) dt2
  4       , ts2
  5    from
  6  (
  7  select to_timestamp('01-may-2005 23:59:59.501'
  8                     ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
  9       , systimestamp ts2
 10   from dual
 11  )
 12  /

DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2010-02-17 09:55:29
17-FEB-10 09.55.28.592716 AM -05:00



and 11gR1: 

ops$tkyte%ORA11GR1> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

ops$tkyte%ORA11GR1> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';

Session altered.

ops$tkyte%ORA11GR1> alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';

Session altered.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select cast(ts1 as date) dt1
  2       , ts1
  3       , cast(ts2 as date) dt2
  4       , ts2
  5    from
  6  (
  7  select to_timestamp('01-may-2005 23:59:59.501'
  8                     ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
  9       , systimestamp ts2
 10   from dual
 11  )
 12  /

DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-02 00:00:00
2005-05-01 23:59:59.501000000
2010-02-17 09:56:01
17-FEB-10 09.56.01.087210 AM -05:00




but 11gr2: 

ops$tkyte%ORA11GR2> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

ops$tkyte%ORA11GR2> alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';

Session altered.

ops$tkyte%ORA11GR2> alter session set nls_timestamp_format = 'yyyy-mm-dd HH24:MI:SS.FF';

Session altered.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select cast(ts1 as date) dt1
  2       , ts1
  3       , cast(ts2 as date) dt2
  4       , ts2
  5    from
  6  (
  7  select to_timestamp('01-may-2005 23:59:59.501'
  8                     ,'dd-mon-yyyy hh24:mi:ss:ff') ts1
  9       , systimestamp ts2
 10   from dual
 11  )
 12  /

DT1
-------------------
TS1
---------------------------------------------------------------------------
DT2
-------------------
TS2
---------------------------------------------------------------------------
2005-05-01 23:59:59
2005-05-01 23:59:59.501000000
2010-02-17 09:57:22
17-FEB-10 09.57.22.406995 AM -05:00



4 stars Isn't it rounding to nearest second?   February 18, 2010 - 9am UTC
Reviewer: John Abate from Missouri, USA
It looks to me that it is rounding the .501 fraction of a second to the nearest whole second

01-may-2005 23:59:59.501 rounds to 02-may-2005 00:00:00

Or am I missing something?


Followup   February 18, 2010 - 9am UTC:

doh, you are correct, I didn't notice the DATE flipped! 

and I now do vaguely recall that the behavior changed - in that the timestamp would trunc() not round in 11g. 

You are correct - I did not see the forest for all of the trees. I was so concentrated on the time going to 00:00:00, that I did not even really look at what the time was beforehand... 

Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:77562635789106

:     

TISTORY에 Login하려면 여기를 누르세요.


Oracle PL/SQL 기초 교육 문서

Oracle 2013. 3. 6. 15:35

Oracle Club에서 작성된 Oracle PL/SQL 기초 교육 문서입니다.

 

자세한 내용은 첨부파일의 내용을 확인해보세요.

 

pl_sql.doc

:     

TISTORY에 Login하려면 여기를 누르세요.


Oracle Data Record별 Text File 내보내는 방법

Oracle 2013. 1. 21. 17:47

Oracle DB에서 특정 Table의 Data를 Record별로 Text File을 생성하여 내보내고 싶을 때 어떻게 할까?

 

UTL_FILE 패키지를 이용하여 PL/SQL에서 파일의 입력 or 출력을 할 수 있다.

 

이는 PL/SQL에서 Log를 File별로 생성하고 싶을 때도 사용이 가능하다.

 

먼저, Oracle Directory를 생성해주어야 한다.

 

▣ Create Directory

 

    DBA 계정으로 oracle에 로그인을 한 후, Directory를 생성하고, 이를 사용하고자 하는 계정에 부여하면 된다.

 

   1. Create Directory exp_text as 'c:\temp';     --Temp Folder를 Directory로 설정하고, 이를 exp_text로 이름을 부여함

 

   2. grant read, write on directory exp_text to scott;    -- Scott 계정에 exp_text Directory에 대한 읽고, 쓰기 권한을 부여함.

 

 

 

위와 같이 Directory 생성 및 권한 부여가 되었다면, Scott 계정에서 아래와 같이 간단하게 PL/SQL을 이용하여 Text File로 Data를

 

추출할 수 있다.

 

▣ Export to Text File

 

create or replace
procedure Export2Text
IS
    vHandle    UTL_FILE.FILE_TYPE;
    vDirectory  varchar2(100);
    vFile   varchar2(256);
    vOutput    varchar2(32767);
 
    cursor c_cur is
    select empno, ename, hiredate, sal from emp;

begin
    vDirectory  := 'exp_text';
   
     for r_cur in c_cur
     loop  
          if r_cur.empno > 0 then  
               vFile := r_cur.empno || '.txt';
               vHandle := UTL_FILE.FOPEN(vDirectory ,vFile,'W', 32767);
               vOutput := r_cur.ename || ', ' || r_cur.hiredate || ', ' || r_cur.sal;
               UTL_FILE.PUT_LINE(vHandle, vOutput);
               UTL_FILE.FCLOSE(vHandle);     
          end if;
    end loop;

exception
    when others then
        UTL_FILE.FCLOSE_ALL;
        raise;
end;
/

 

 아래는 Directory내에 존재하는 file을 읽어오고자 할 때, 사용할 수 있는 PL/SQL이다.

 

▣ Import to Text File

 

create or replace
procedure Import2Text
is
     vHandle   UTL_FILE.FILE_TYPE;
     vDirectory  varchar2(100);
      vFile   varchar2(256);
     vInput    varchar2(32767);
begin
     vDirectory := 'exp_text';
     vFile := 'test.txt';                        
     vHandle   := UTL_FILE.FOPEN(vDirectory ,vFile,'R', 32767);
 
     begin
         loop
               UTL_FILE.GET_LINE(vHandle, vInput,32767);
               DBMS_OUTPUT.PUT_LINE(vInput);
         end loop;
     exception
        when NO_DATA_FOUND then
              dbms_output.put_line('last line of file');
     end;
 
     UTL_FILE.FCLOSE(vHandle);
exception
    when others then
        UTL_FILE.FCLOSE_ALL;
        raise;
end;
/

 

:     

TISTORY에 Login하려면 여기를 누르세요.


Compound Triggers in Oracle 11g

Oracle 2012. 7. 26. 13:19

Oracle 11g에서는 Compound Triggers 기능이 생겨서 각 Event별로 Trigger를 만들 필요가 없어졌네요.

 

In earlier versions of Oracle, triggers were the database objects whose ability to collide with the current database state of an object and session state maintenance, kept the database developers alert while designing triggers.  Oracle11g offers considerable improvements in database triggers. These additions have not only enhanced the language usability but also promise a better performance in a real application environment. 

Oracle 11g fixes major issues by the enhancements as listed below.

  • Trigger firing sequence can be set in Oracle 11g using FOLLOWS keyword
  • Compound Triggers to encapsulate multiple triggering timings in one body
  • Triggers can be created in ENABLED/DISABLED mode
  • The DML triggers in 11g are 25% faster than their earlier versions in terms of compilation, execution and firing

In the tutorial, you will learn how Compound Triggers works and its benefits to the users.

1. Compound Triggers: Introduction

Compound triggers are the database DML triggers which ‘compounds’ or combines all the triggering timings under one trigger body. The triggering timings available for database triggers are BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, and AFTER EACH ROW. Trigger level variables can be defined which would be visible and accessible in all the timing blocks.

Compound trigger body does not contain any exception section but recommends each block to contain its own exception handler under exception section.

2. Compound Triggers: Reaping the Benefits

Compound trigger resolves few big issues, which have been the nightmare for the developers in the past.

  1. Mutating table error ORA-04091
  2. Multithread behavior maintains persistent state of session variables till the statement execution finishes. These are defined in the declaration section of the compound trigger.
  3. Enhanced performance in bulk operations
  4. Supports encapsulation of multiple program units; thus enhances code interactivity

3. Compound Triggers: Usage Guidelines

As a new induction to the database family, there are some introductory guidelines which must be followed while working with compound triggers

  • Compound trigger are meant only for DML operations. No support still for DDL and system operations.
  • Exception handling process has to be done for each timing block.
  • Compound trigger remains passive, if the DML does not changes any rows
  • :OLD and :NEW variable identifiers can exist only in ROW level blocks, where :NEW values can only be modified in BEFORE EACH ROW block
  • No support for WHEN clause. Earlier, WHEN clause could be utilized to impose condition on the trigger action.
  • No support for PRAGMA_AUTONOMOUS_TRANSACTION

There are many debates over the above guidelines, which many identify as compound trigger restrictions. But from developer’s perspective, the benefits from compound triggers are much heavier than these restrictions. The assumptions and restrictions can be melted into code practice and as ‘usage guidelines’.

4. Compound Triggers: Syntax

The syntax shows the four timing blocks in a set order.

Example Syntax [1a]: For DML Triggers

Sample Code
  1. CREATE OR REPLACE TRIGGER [TRIGGER NAME]
  2. FOR [DML] ON [TABLE NAME]
  3. COMPOUND TRIGGER
  4. -- Initial section 
  5. -- Declarations 
  6. -- Subprograms
  7. Optional section
  8. BEFORE STATEMENT IS
  9. …;
  10. Optional section
  11. AFTER STATEMENT IS
  12. …;
  13. Optional section
  14. BEFORE EACH ROW IS
  15. …;
  16. Optional section
  17. AFTER EACH ROW IS
  18. …;
  19. END; 
Copyright exforsys.com


In the syntax, note that the compound trigger offers only the declaration section, which are again locally available in the timing blocks.

Example Syntax [1b]: For database views

INSTEAD OF EACH ROW IS 
...; 
END;

Note that none of the timing blocks should be duplicated. Oracle server raises exception PLS-00676 if duplicate timing blocks is found in the compound trigger definition.

5. Compound Triggers: Additional Notes

5.1. USER_TRIGGERS dictionary view structure has been restructured to include the metadata for compound triggers. The new columns specify whether a timing block is available in the compound trigger body.

5.2. The new triggering event has been introduced for compound trigger as ‘COMPOUND’. The figure below shows the TRIGGER_TYPE, TRIGGERING_EVENT and TABLE_NAME of the trigger TRG_COMP_DEMO.

5.3. The columns BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, and AFTER_STATEMENT are set as YES/NO based on the timing blocks available in the compound trigger. For example, the trigger TRG_COMP_DEMO contains all the timing blocks in the trigger body, the columns can be queried as below.

6. Applications of Compound Triggers

A compound trigger is best suited to achieve two objectives.

  • Yield better performance while loading a table simultaneous to a running transaction and using its values.
  • Resolve mutating table error (ORA-04091)

I will  illustrate both the accomplishments in below steps

6.1. Demonstrating performance gains during simultaneous loading

1. Two tables were created

Sample Code
  1. SQL> CREATE TABLE ORDERS (ORD_ID NUMBER, 
  2. ITEM_CODE VARCHAR2(100), 
  3. ORD_QTY NUMBER, 
  4. ORD_DATE DATE); 
  5. TABLE created. 
  6. SQL> CREATE TABLE ORDER_ARCHIVE(ORD_ID NUMBER, 
  7. ORD_CODE VARCHAR2(100));
  8.  
  9. TABLE created. 
Copyright exforsys.com


2. To demonstrate the performance difference between the versions, I create a normal FOR EACH ROW trigger on ORDERS table to insert data into ORDER_ARCHIVE table.

Sample Code
  1. SQL> CREATE OR REPLACE TRIGGER TRG_ORDERS
  2. BEFORE INSERT ON ORDERS
  3. FOR EACH ROW 
  4. BEGIN
  5. DBMS_OUTPUT.PUT_LINE('Insert order‘||:NEW.ORD_ID||’ into ORDER_ARCHIVE');
  6. INSERT INTO ORDER_ARCHIVE VALUES (:NEW.ORD_ID,:NEW.ITEM_CODE);
  7. END;
  8. / 
  9. TRIGGER created. 
Copyright exforsys.com


3. Now, I will insert the test data into the ORDERS table using SELECT statement

Sample Code
  1. SQL> INSERT INTO orders(ord_id, item_code, ord_date)
  2. 2 SELECT (rownum+1)*100, 'ITEM_'||rownum, sysdate-rownum
  3. 3 FROM DUAL
  4. 4* CONNECT BY ROWNUM < 15; 
  5. INSERT ORDER 1 INTO ORER_ARCHIVE
  6. INSERT ORDER 2 INTO ORER_ARCHIVE
  7. INSERT ORDER 3 INTO ORER_ARCHIVE
  8. INSERT ORDER 4 INTO ORER_ARCHIVE
  9. INSERT ORDER 5 INTO ORER_ARCHIVE
  10. INSERT ORDER 6 INTO ORER_ARCHIVE
  11. INSERT ORDER 7 INTO ORER_ARCHIVE
  12. INSERT ORDER 8 INTO ORER_ARCHIVE
  13. INSERT ORDER 9 INTO ORER_ARCHIVE
  14. INSERT ORDER 10 INTO ORER_ARCHIVE
  15. INSERT ORDER 11 INTO ORER_ARCHIVE
  16. INSERT ORDER 12 INTO ORER_ARCHIVE
  17. INSERT ORDER 13 INTO ORER_ARCHIVE
  18. INSERT ORDER 14 INTO ORER_ARCHIVE 
  19. 14 rows created. 
Copyright exforsys.com


Note the output of the above INSERT process. For each of the 14 records inserted into the ORDERS table through the trigger, Oracle server makes simultaneous inserts into the ORDER_ARCHIVE table.

4. Compound triggers in Oracle 11g have the ability to perform simultaneous bulk insert process into the table. I will create the compound trigger TRG_COMP_ORDERS to implement the bulk loading process. Note the use of associative arrays, which hold the data to be inserted. It flushes off once the data is replicated into the ORDER_ARCHIVE table when the index count reaches 20 or the statement execution completes.

Sample Code
  1. SQL> CREATE OR REPLACE TRIGGER TRG_COMP_SAL
  2. FOR INSERT ON ORDERS
  3. COMPOUND TRIGGER
  4. TYPE ORDER_T IS TABLE OF ORDER_ARCHIVE%ROWTYPE 
  5. INDEX BY PLS_INTEGER;
  6. L_ORDERS ORDER_T;
  7. I NUMBER := 0;
  8. AFTER EACH ROW IS
  9. BEGIN
  10. I := I+1;
  11. L_ORDERS(I).ORD_ID := :NEW.ORD_ID;
  12. L_ORDERS(I).ORD_CODE := :NEW.ITEM_CODE;
  13. IF I >= 20 THEN
  14. DBMS_OUTPUT.PUT_LINE('Bulk Load for 20 orders'); 
  15. FOR J IN 1..I
  16. LOOP
  17. INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
  18. END LOOP;
  19. L_ORDERS.DELETE;
  20. I := 0;
  21. END IF;
  22. END AFTER EACH ROW;
  23. AFTER STATEMENT IS
  24. BEGIN
  25. DBMS_OUTPUT.PUT_LINE('Statement level loading');
  26. FORALL J IN 1..L_ORDERS.COUNT
  27. INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
  28. L_ORDERS.DELETE;
  29. I := 0;
  30. END AFTER STATEMENT;
  31. END;
  32. /
  33.  
  34. TRIGGER created. 
Copyright exforsys.com


5. I will insert 64 rows (instead of just 14) into ORDERS table using SELECT query. The simultaneous inserts into ORDER_ARCHIVE table are achieved only in 3 bulk insert process.

Sample Code
  1. SQL> INSERT INTO orders(ord_id, item_code, ord_date)
  2. 2 SELECT (rownum+1)*100, 'ITEM_'||rownum, sysdate-rownum
  3. 3 FROM DUAL
  4. 4 connect BY rownum < 65
  5. 5 / 
  6. Bulk LOAD FOR 20 orders
  7. Bulk LOAD FOR 20 orders
  8. Bulk LOAD FOR 20 orders
  9. Statement level loading 
  10. 64 rows created. 
Copyright exforsys.com


Above results clearly differentiate the compound trigger from the conventional triggers in terms of performance while simultaneous data loading.

6.2. Demonstration of Mutating table solution using Compound triggers

Now, let us learn about the most accomplished achievement of Compound Triggers, i.e. their ability to tackle with Mutating table error (ORA-04091).

Mutating table occurs when a table is referenced when it is in floating state. A table is in flux or floating state when it is the participant of a running transaction. Earlier, it used to be taken as a precaution during coding or its effects were diluted using workaround solutions. Few of the efficient workarounds are as below.

  • Change in logic implementation and code design
  • Using PRAGMA_AUTONOMOUS_TRANSACTION
  • Conversion of row level trigger to statement level
  • Defining package to hold variables at session level; these variables would hold the values and later would be inserted once the statement execution finishes.

How compound trigger fixes the problem?

Compound trigger contains the timing blocks and variables, which are persistent till the statement completes and are visible in all the timing blocks of the trigger. These variables are of collection type which holds the table data, before it enters the flux mode. Table gets locked once it moves to flux mode, but the backup data in the collection variables remains persistent and can be used for reference within the trigger.

Therefore, the logic used is same as earlier, but being a single compiled unit, it is more convenient and easily maintained.

In the example code below, the compound trigger fires on update of ORDERS table. It queries the ORDERS table to fetch and display the old quantity. For a conventional DML trigger, the situation is the best candidate for ORA-04091 Mutating table error.

A user updates the order quantity for order id 600. Now before the UPDATE statement executes, the BEFORE STATEMENT block fetches the old ‘order quantity’ value (90) and stores in a collection type variable. Now when the statement executes, it updates the ‘order quantity’ to 150. AFTER STATEMENT block in the trigger displays the ‘old quantity’ value.

Sample Code
  1. CREATE OR REPLACE TRIGGER TRG_ORDERS
  2. FOR UPDATE ON ORDERS
  3. COMPOUND TRIGGER
  4. TYPE ORD_QTY_T IS TABLE OF ORDERS.ORD_QTY%TYPE;
  5. L_ORD_QTY ORD_QTY_T; 
  6. BEFORE STATEMENT IS
  7. BEGIN 
  8. SELECT ORD_QTY 
  9. BULK COLLECT INTO L_ORD_QTY 
  10. FROM ORDERS WHERE ORD_ID=600; 
  11. END BEFORE STATEMENT; 
  12. AFTER EACH ROW IS
  13. BEGIN
  14. DBMS_OUTPUT.PUT_LINE('Old Quantity Value:'||L_ORD_QTY(L_ORD_QTY.FIRST));
  15. END AFTER EACH ROW; 
  16. END;
  17. / 
  18. SQL> UPDATE ORDERS
  19. 2 SET ORD_QTY = 150
  20. 3 WHERE ORD_ID = 600;
  21. Old Quantity Value:90 
  22. 1 row updated. 
Copyright exforsys.com


7. Conclusion

Compound trigger combines the properties of both statement and row level triggers. The logic which was earlier maintained at row and statement level can now be placed into one single body, with state and persistency in data across the timings.

 

출처 : http://www.exforsys.com/tutorials/oracle-11g/compound-triggers-in-oracle-11g.html

:     

TISTORY에 Login하려면 여기를 누르세요.


ORA-20023: Missing start and end values for time model stat

Oracle 2012. 5. 15. 11:01

"ORA-20023: Missing start and end values for time model stat"

 

이는 Oracle11g License의 정책으로 인해서 생성된 Parameter설정과 연관되어 있는 Error 입니다.

 

하여 CONTROL_MANAGEMENT_PACK_ACCESS Parameter의 값을 확인 해보셔야 합니다.

 

11g에서는 기본값이 DIAGNOSTIC+TUNING 으로 되어 있는데,

 

이를, NONE으로 설정 시, Snapshot에 대한 조회 권한이 없어집니다.

  

해당 Parameter 값을 변경하기 위해서는 아래와 같이 해주시면 됩니다.

 

 

alter system set CONTROL_MANAGEMENT_PACK_ACCESS="DIAGNOSTIC+TUNING" scope=both;


 

Snapshot 정보는 계속 자동으로 생성되더라도, 위와 같이 설정이 되어 있지 않다면 이를 이용하여 분석할 수 없습니다.

 

보다 자세한 정보는 아래 Link에서 확인해보시기 바랍니다.

 


http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams037.htm

 

:     

TISTORY에 Login하려면 여기를 누르세요.


DataPump Export (EXPDP) Client Gets UDE-8 ORA-31626 ORA-39086 [ID 549781.1]

Oracle 2011. 8. 11. 15:19

수정 날짜 29-APR-2010     유형 PROBLEM     상태 PUBLISHED  


Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Symptoms

Datapump export from the client side completes with errors. Example:

expdp system/password full=y dumpfile=full_exp.dmp logfile=full_explog

...
. . exported "CALYPSO"."KICKOFF_CFG" 0 KB 0 rows 
. . exported "CALYPSO"."LE_AGR_CHILD" 0 KB 0 rows 
. . exported "CALYPSO"."LE_ATTR_CODE" 0 KB 0 rows 

UDE-00008: operation generated ORACLE error 31626 
ORA-31626: job does not exist 
ORA-39086: cannot retrieve job information 
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745 
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712 
ORA-06512: at line 1

However, reviewing the logfile shows that the "job successfully completed"

Cause

This issue has been discussed in Bug 5969934 EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK

Solution

The expdp client makes calls to DBMS_DATAPUMP package to start and monitor export job. Once the export job is underway, the client just monitors the job status by issuing DBMS_DATAPUMP.GET_STAUS.
Therefore, if the export logfile says "job successfully completed", the dump file generated by the job should be fine.


You can simply ignore the errors, since the dump file is still valid for an import.

In the 10.2.0.2 release, there were a number of problems that caused the expdp and impdp clients to exit prematurely, interpreting a nonfatal error as a fatal one, giving the appearance that the job had failed when it hadn't. In fact, inspection of the log file, if one was specified for the job, showed that the job ran successfully to completion. Often a trace file written by one of the Data Pump processes would provide more detail on the error that had been misinterpreted as a fatal one. Many of these errors involved the queues used for communication between the Data Pump processes, but there were other issues as well. 
.
With each subsequent release, these problems have been addressed, and the client has become more robust and rarely, if ever, runs into situations like this. However, this is the result of many bug fixes in subsequent releases, some in Data Pump and some in supporting layers. It's impossible to know, at this point, what combination of bug fixes would address this specific failure, and even if that was possible, it wouldn't address other possible failures that look very similar on the client side. 
.
Relying on information in the log file is one way to verify that the job actually completed successfully. Problems like this one became much more intermittent by the 10.2.0.4 release and are rarely, if ever, seen in 11.1 or later.

References

BUG:5969934 - EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK

관련 정보 표시 관련 자료


제품
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
오류
UDE-8; ORA-6512; ORA-31626; ORA-39086; ERROR 31626

출처 : Oracle Support


Data Pump에 생각보다 많은 Bug들이 있다. 하지만, 성능상으로는 이런 Bug들을 감수할만큼 효율적이니 잘 피해서 사용해보자.
:     

TISTORY에 Login하려면 여기를 누르세요.


Oracle 10gR2이상에서 Automatic Tuning of Undo_retention Causes Space Problems

Oracle 2011. 8. 11. 11:39

Subject: Automatic Tuning of Undo_retention Causes Space Problems
  Doc ID: Note:420525.1 Type: PROBLEM
  Last Revision Date: 26-MAY-2008 Status: PUBLISHED

 


Applies to:

Oracle Server - Enterprise Edition - Version: 10.2 to 11.0
This problem can occur on any platform.

 

Symptoms

Look for: 

1.) System Managed Undo 
undo_management=auto in init.ora file 

2.) Fixed size undo tablespace
SQL> select autoextensible from dba_data_files where tablespace_name='<current_undo_tablespace >' 

returns "No" for all the undo tablespace datafiles. 

3.) The undo tablespace is already sized such that it always has more than enough space to store all the undo generated within the undo_retention time, and the in-use undo space never exceeds the undo tablespace warning alert threshold (see below for the query to show the thresholds). 

4.) The tablespace threshold alerts recommend that the DBA add more space to the undo tablespace: 

SQL> select creation_time, metric_value, message_type,reason, suggested_action from dba_outstanding_alerts where object_name='<current_undo_ts>'; 

returns a suggested action of: "Add space to the tablespace" 

Or,

This recommendation has been reported in the past but the condition has now cleared: 
SQL> select creation_time, metric_value, message_type, reason, suggested_action, resolution from dba_alert_history where object_name='<current_undo_ts>'; 

5.) The undo tablespace in-use space exceeded the warning alert threshold at some point in time: 

To see the warning alert percentage threshold: 

SQL> select object_type, object_name, warning_value, critical_value from dba_thresholds where object_type='TABLESPACE'; 

To see the (current) undo tablespace percent of space in-use: 
SQL> select 
((select (nvl(sum(bytes),0)) 
from dba_undo_extents 
where tablespace_name='<current_undo_ts>' 
and status in ('ACTIVE','UNEXPIRED')) *100) / 
(select sum(bytes) 
from dba_data_files 
where tablespace_name='<current_undo_ts>') 
"PCT_INUSE" 
from dual;

Cause

This is due to Bug 5387030 that is fixed in 11.1 version of RDBMS server.

 

Solution

Apply the patch for the Bug 5387030. Check metalink for patch availability for your platform and RDBMS version.

Workaround

There are 3 possible alternate workarounds (any one of these should resolve the problem of the alerts triggering unnecessarily): 

1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo tablespace now has the autoextend attribute but does not autoend: 
SQL> alter database datafile '<datafile_flename>' 
autoextend on maxsize <current_size>; 

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

2.) Set the following hidden parameter in init.ora file: 
_smu_debug_mode=33554432 

or 

SQL> Alter system set "_smu_debug_mode" = 33554432; 

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

3.) Set the following hidden parameter in init.ora: 
_undo_autotune = false 

or 

SQL> Alter system set "_undo_autotune" = false; 

With this setting, v$undostat (and therefore v$undostat.tuned_undoretention) is not maintained and and the undo_retention used is the one specified in init.ora file.

 

References

Bug 5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS




출처 : Oracle Support


alter system set "_IN_MEMORY_UNDO"=false;
Alter system set "_undo_autotune" = false;
:     

TISTORY에 Login하려면 여기를 누르세요.