Here's an example of a database index report written with SQL statements
that sets number format, width etc as an example for you.
rem ************************************************************************
rem *
rem * LIST_INDEXES.SQL - 1) Reports indexes used in the database
rem * In Word, set font to size 9, courier new, legal 8.5x14 landscape
rem *
rem ************************************************************************
rem whenever sqlerror exit sql.sqlcode
* Set up global settings
SET DEFINE '!'
clear columns;
clear breaks;
clear comp;
set LINESIZE 170 PAGESIZE 52 NEWPAGE 0 SPACE 2 NUMWIDTH 14 HEADING ON -
TERMOUT OFF FLUSH OFF FEEDBACK OFF VERIFY ON WRAP OFF PAUSE OFF -
EMBEDDED OFF HEADSEP ON RECSEP OFF
SPOOL LIST_INDEXES.DOC
break on TODAY duplicate -
on NOW duplicate -
on owner -
on table_name -
on index_name -
on tablespace_name
rem * Report page top title and bottom titles
ttitle on
btitle on
ttitle center 'Your Company Name' right 'Page : ' FORMAT '99999999'
sql.pno -
skip -
left ' ' center 'Schema Index List' right 'Report Date : ' SDATE -
skip -
left 'Report Id: list_indexes' center 'Index Validation Project'
right 'Report Time : ' TNOW -
skip 1
btitle skip -
'-------------------------------------------------------------------------'
-
skip -
rem * Column commands to format columns
column TODAY noprint new_val SDATE
column NOW noprint new_val TNOW
column owner format A8
column table_name format A30
column index_name format A30
column UNIQ format A4
column PART format A4 heading "PART"
column column_position noprint heading "COL" format 990
column TBSPC format A20 heading "TABLESPACE"
column MON format A3 heading "MON"
column USED format A4 heading "USED"
column BLKSZ format 999990 heading "BLK SZ"
--column old_count heading 'Old DB Count' format A20 WORD_WRAPPED
--column new_count heading 'New DB Count' format A20 WORD_WRAPPED
select to_char(SYSDATE,'DD-MON-YY') TODAY,
to_char(SYSDATE,'HH:MI PM ') NOW,
a.owner OWNER,
a.table_name,
a.index_name,
b.column_position,
substr(b.column_name,1,30) COL_NAME,
substr(decode(a.index_type,'NORMAL',NULL,'FUNCTION-BASED
NORMAL','FUNCTION',a.index_type),1,8) INDX_TYP,
decode(a.uniqueness,'UNIQUE','Y',NULL) UNIQ,
a.tablespace_name TBSPC,
d.block_size BLKSZ,
decode(c.monitoring,'YES','Y','N') MON,
decode(c.used,'YES','Y','N') USED,
decode(a.partitioned,'NO','N','Y') PART
from sys.dba_indexes a, sys.dba_ind_columns b, v$object_usage c,
sys.dba_tablespaces d
where a.owner = b.index_owner
and a.index_name = b.index_name
and a.table_name = b.table_name
and a.index_name = c.index_name (+)
and a.table_owner = '<yourschema>'
and a.tablespace_name = d.tablespace_name
group by to_char(SYSDATE,'DD-MON-YY'),
to_char(SYSDATE,'HH:MI PM '),
a.owner,
a.table_name,
a.index_name,
b.column_position,
substr(b.column_name,1,30),
substr(decode(a.index_type,'NORMAL',NULL,'FUNCTION-BASED
NORMAL','FUNCTION',a.index_type),1,8),
decode(a.uniqueness,'UNIQUE','Y',NULL),
a.tablespace_name,
d.block_size,
decode(c.monitoring,'YES','Y','N'),
decode(c.used,'YES','Y','N'),
decode(a.partitioned,'NO','N','Y')
order by to_char(SYSDATE,'DD-MON-YY'),
to_char(SYSDATE,'HH:MI PM '),
a.owner,
a.table_name,
a.index_name,
b.column_position,
substr(b.column_name,1,30),
substr(decode(a.index_type,'NORMAL',NULL,'FUNCTION-BASED
NORMAL','FUNCTION',a.index_type),1,8),
decode(a.uniqueness,'UNIQUE','Y',NULL),
a.tablespace_name,
d.block_size,
decode(c.monitoring,'YES','Y','N'),
decode(c.used,'YES','Y','N'),
decode(a.partitioned,'NO','N','Y')
/
SPOOL OFF
rem * Standard closing for report program
clear columns;
clear breaks;
clear comp;
ttitle off
btitle off
rem exit sql.sqlcode
-----Original Message-----
From: Amar P. Singh
[mailto:oracledba-ezmlmshield-x56964996.[Email address protected]
Sent: Thursday, June 01, 2006 10:03 AM
To: LazyDBA Discussion
Subject: Inserting Big numbers displays calue in Exponential form
Hi Gurus~
Ok now I have a table with number data type and when I am inserting the
data with length of 16 or above it shows me the value in expontial
format. Can you tell me how to see the full values.
Eg insert into temp1 values(12233445566778899);
Select * from temp1;
1.22334455667789E16
Why this exponential E in record?
With Regards,
Amar P Singh
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
This communication is intended for the use of the recipient to which it is
addressed, and may contain confidential, personal and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take action
relying on it. Any communication received in error, or subsequent reply,
should be deleted or destroyed.
Oracle LazyDBA home page