How many bytes for Oracle datatype timestamp and number.

Posted on 2009-04-06
Medium Priority
Last Modified: 2013-12-18
How many bytes should be counted for Oracle datatype timestamp/date and numbers? What is number(8,2)? For example,
PHONE#    char(10)
is counted 10 bytes, but for
Arrive_time     timestamp
how many bytes to be counted for timestamp?
Question by:jl66
  • 3
  • 2
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 24080657
13 bytes in a timestamp...

select vsize(systimestamp), vsize(sysdate) from dual

a number will use the fewest number of bytes needed to store the value with a minimum of 2
regardless of precision or scale

LVL 74

Assisted Solution

sdstuber earned 2000 total points
ID: 24080722
11 bytes for timestamp
13 bytes for timestamp with time zone

numer(8,2) will vary from 0 (for null) or 2-5 if populated

Author Comment

ID: 24081183
Thanks for the info. How did you get "numer(8,2) will vary from 0 (for null) or 2-5 if populated"?

Author Comment

ID: 24081377
How to get "11 bytes for timestamp"?
LVL 74

Assisted Solution

sdstuber earned 2000 total points
ID: 24081457

select vsize(arrive_time) from your_table

systimestamp is actually a "timestamp with time zone" type, sorry for the confusion.

null is 0 simply by nature of being NULL.  :)

you can check the size of your data by populating your number(8,2) column with data and examining it.

You'll see the smallest it ever gets is 2 bytes, the largest is 5 bytes.

select your_column,vsize(your_column) from your_table


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question