Server Administration :: Estimated DB Size If BLOB Data Type Is Included

Jul 7, 2010

We will create a new instance in our production server, but this time, part of it's table structure has a BLOB data type (re: <column name> blob(3000)). It's our first time to handle this kind of Oracle data type. What would be my estimate size for it's default tablespace?

View 5 Replies


ADVERTISEMENT

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

View 2 Replies View Related

Server Administration :: Size Of Data Of Table

Oct 20, 2011

ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
19 35 2810 3

Ok, the above shows us:

- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.

Therefore, our table

- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

not too sure this calculation is accurate for getting the size (data)of the table.

View 32 Replies View Related

Cannot Create BLOB Data Type For A Column In Tavle

Jul 6, 2010

user@tank> create table filetable(id number(6), file_content BLOB);

0 rows affected (0.02 seconds)

vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL

I am new to Database field.

- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.

View 1 Replies View Related

Server Administration :: RAW Data-type?

Apr 3, 2013

RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.

View 5 Replies View Related

Server Administration :: What Can Be Maximum Size Of Data File

Nov 24, 2011

I am having I/O issues if i create 20 GB DATAFILES on SMALL TABLE SPACE. guide me with the maximum size limit of data file that I can create in Windows 2003 32 bit server.

View 3 Replies View Related

Server Administration :: XE Limitations - How To Calculate Size Of User Data

Apr 15, 2011

I have read following statement from a link [URL]...

Oracle Database XE can be installed on any size host machine with any number of CPUs (one database per machine), but XE will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine.

calculation of this 4GB size. how can we calculate this size?

by simply going to DBF file and seeing their size?
or
by exporting dump and seeing the size of that dump?

View 3 Replies View Related

Server Administration :: Long Data Type Illegal?

Dec 26, 2012

when I try comnd create table a as select * from b where 1=2; it says illegal datatype long..i m bemused what sin has the long datatype done?

View 4 Replies View Related

Data Guard :: Move BLOB Images In DB To BFILE To Reduce Time And Size Of Backup?

Jan 22, 2012

We are planning to move BLOB images in our DB to BFILE to reduce the time and size of backup.

So when we have a physical dataguard setup, can we configure data guard to copy the OS level BFILE's to the DR site?

View 2 Replies View Related

Server Administration :: How To Reduce Size Of TEMP DBF File Size

Apr 13, 2011

I am using oracle 8.1.5 database and my temp01.dbf file size is increased upto 19.8 GB now i want reduce its size .

View 13 Replies View Related

Server Utilities :: How To Find Estimated Time To Complete Import (imp)

Apr 17, 2012

I am using imp / Schema level. Dump file contains 3 schemas with 120 GB size.Imp is running now. How to find completion time. So that I can inform to application developer about activity completion time.

View 4 Replies View Related

Server Administration :: BLOB Are Missing / Unable To Extend Lobsegment Schema1

Mar 22, 2012

we have received an arror on 7 Mar 2012 which is is follows

ORA-1692 : unable to extend lobsegment schema1.Sys_LOB0000126620000004$$ Partition sys_Lob_p265 by 8192 in tablespace DIF-M12

the above error was reported in alert log at 1150 hrs and then a file was added by the local DBA at 1820 hrs i.e after 6 hours.Now my query is that is there any relation of this error with the BLOB missing from database or if not so what harmful effect there may be on Database as datafile was added after 6 hours

secondly when i see the DF_M12 tablespace usage at EM so its total size is 86 Gb and used is 76Gb but when i see its datafiles so it shows a different story which is opposite to the reported used space i.e only one datafile is showing 25g and rest are not showing any space which means that something is missing so i doubt it is this may be the reason of missing blob or not

View 7 Replies View Related

Calculate Size Of Table With BLOB Column

Feb 5, 2007

when trying to calculate the occupied space for a table, I'm using DBA_SEGMENTS, which works fine as long as the table does not have a BLOB column.

As far as I can tell, the size of the BLOB data is stored with the SEGMENT_TYPE = 'LOBSEGMENT', but I cannot find a view that tells me which DBA_SEGMENT row belongs to the BLOB column in the table I'm checking.

To give you an example:

select sum(BYTES)
from DBA_SEGMENTS
where owner = user
and segment_name = 'MY_TABLE'
group by SEGMENT_NAME

returns 262144

running:

SELECT sum(length(blob_column))
FROM my_table

returns 821333

There are entries in DBA_SEGMENTS for my user with the type LOBSEGMENT, but I cannot find a way to map the correct DBA_SEGMENTS row to the table I am checking.

I'm using Oracle 10.2.0.3.0 on Redhat

View 2 Replies View Related

Server Administration :: How To Know DB Size Increase Per Day

Jan 31, 2011

How to know DB size increase per hour or day on the Oracle?

View 3 Replies View Related

Server Administration :: Max Size Of Tablespace

Jan 4, 2010

I have one tablespace PSINDEX with Maxsize of 6 GB. But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

View 5 Replies View Related

Server Administration :: Segment Size?

Dec 28, 2011

I am working to understand the space allocation of table with the value we provided with the data type. For that I have created a table with varchar2 and length 50. Size of table created is of 65536 Bytes. This is when we don't have any insertion in the table. Later when we insert some rows, total size if the segment still remain same that is 65536 bytes.

Now again when I created table with varchar2 and length this time is 500 but still it is created with same size that is 65536. So can you just explain, on what values segment size depends on and how the length effect the size & space allocation.

db_block_size is 8192.

View 2 Replies View Related

Server Administration :: Unable To Increase Size Of Sga

Feb 2, 2012

I am trying to increase the size of sga or you can say that i want to make my sga in automatic memory management...Following is the steps i am trying

SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 96M
SQL>

after that i am trying to increase the size

SQL> alter system set sga_max_size = 200m;
alter system set sga_max_size = 200m
*
ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified

View 1 Replies View Related

Server Administration :: How To Shrink Table Size

Jan 4, 2012

I have a table: desc STG_XML

Name Null Type
------------------------------ -------- ------------------------
ENTITY_ID NOT NULL VARCHAR2(100 CHAR)
ENTITY_TYPE_ID NOT NULL NUMBER
SOURCE_ID NOT NULL VARCHAR2(512 CHAR)
XML_SCHEMA_ID NOT NULL NUMBER
JOB_ID NOT NULL NUMBER
FINGERPRINT NOT NULL VARCHAR2(100 CHAR)
ENTITY_XML_DATA CLOB()
ARCHIVED NUMBER(1)
CREATION_DATE TIMESTAMP(6)
MODIFICATION_DATE TIMESTAMP(6)
ARCHIVING_DATE TIMESTAMP(6)
CREATED_BY VARCHAR2(50 CHAR)
MODIFIED_BY VARCHAR2(50 CHAR)

The problem is that the data of the table are 40GB while on the DB the table holds 400GB! How can I shrink and reuse that space except from drop/recreate and drop/import?

The table has no initial data, so that I can play with the INITIAL parameter. Data are inserted, updated and deleted all the time. I have run DBMS_ADVISOR which recommended to SHRINK table. I have performed the shrink :

alter table STG_XML shrink space COMPACT;

but I haven't gained any space.

View 12 Replies View Related

Server Administration :: How To Undo Tablespace Size

Feb 7, 2011

As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?

As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?

In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.

As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?

SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space

[code]....

View 12 Replies View Related

Server Administration :: Increasing Tablespace Size?

Mar 7, 2010

I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.

View 8 Replies View Related

Server Administration :: Size Of The Oracle Database 11g?

Apr 21, 2010

how can we find the size of the oracle database 11g.

View 2 Replies View Related

Server Administration :: ARC File Size Too Small?

Oct 2, 2012

I noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.

Is there anything else I can look into to see how to make the .arc files larger?

SQL> select group#, thread#, bytes from v$log;
GROUP# THREAD# BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800

select blocks, block_size, blocks*block_size from v$archived_log where sequence# between 63876 and 72851 and thread# = 1

BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
28 512 14336
28 512 14336
28 512 14336
55 512 28160

[code]...

View 18 Replies View Related

Server Administration :: Reducing Tablespace Size?

Jan 24, 2012

we have a tablespace of size 900 GB where 90% of space is occupied by two tables having BLOB data and now i need to drop these two tables and then to recover the space, i need to resize the tablespace (datafiles).

View 3 Replies View Related

Server Administration :: Non Standard Block Size

Aug 15, 2011

I am using oracle 10g with sga_max_size =4GB and db block size 16k. Now i am creating a tablespace with block size 32 kb , whats value i select for the parameter db_32k_cache_size.

Is there any standard way to calculate the value of this parameter.

View 4 Replies View Related

Server Administration :: How To Reduce Size Of A Table

Oct 2, 2012

One of our solaris machines is running Oracle 8.0.3

A table reached the 2 Gb size and oracle failed due to the operating system file size limitation.

The information in the table is not relevant and can be deleted, but the table contains a lot of indexes.

I would like to know the best procedure to delete the information and reduce the size of the file.

View 3 Replies View Related

Server Administration :: Listener.trc Size Increasing?

Dec 5, 2012

My listener.trc file has grown to a size of 56G and is contineously growing with the following message:

9:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:669] naeshow: [05-DEC-2012 19:56:49:670] naeshow: [05-DEC-2012

[code]...

View 1 Replies View Related

Server Administration :: Increase Size Of Database To 50 GB

Jan 31, 2013

I have Oracle 11gR2 running on windows xp machine. Windows xp has total size of 150 GB and free space of 95 GB.

I checked the size of the database that I created. It showed the total size of the database as 2 GB and used space as 2 GB. If I want to increase the total size of the database to 50 GB, what should i do? Now which is the disk space size? Windows or Oracle?

View 4 Replies View Related

Server Administration :: What Is The Size Of Each Granule For Oracle 10g

Mar 27, 2011

I want to know what is the size of each granule for oracle 10g. I read it from the following link

[URL].........

There it is described that

Quote:
The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size. Generally speaking, on most platforms, if the total SGA size is equal to or less than 1 GB, then granule size is 4 MB. For SGAs larger than 1 GB, granule size is 16 MB. Some platform dependencies may arise. For example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 1 GB. Consult your operating system specific documentation for more details.

Now My query about full list of granule size for different platform like windows 64 bit, unix etc.

View 12 Replies View Related

Server Administration :: How To Get Size Of All 80 Users From Database

Mar 19, 2012

select username,account_status,default_tablespace from dba_users;

The ran the above query and it return 80 users but when i ran below query it shows just 14 rows.

select owner,sum(bytes)/(1024*1024) "GB" from dba_segments group by owner;how to get the size of all 80 users from database ?

View 2 Replies View Related

Server Administration :: Sudden Increase In Archive Log Size

Mar 26, 2010

We had a database (DB A)that is having version 9.2.0.6.0.This DB is having multiple tables and volume of 6 million in individual tables.Another database is also 9.2.0.6.0 (DB B), this DB has Mviews pointing to DB A. Mviews are refreshed in every 15 mins, with fast refresh option in 90% cases and remaining having complete refresh.

Last weekend we have migrated DB 2 to version 10.2.0.4.0 - 64bi and on another server.After version upgrade and DB migration complete refresh was done once for all mviews.

Now DB A is generating huge amount of archive log and also it's UNDO space is getting fully consumed causing performance issue and DB hang.what has gone wrong and what we can do to improve response of DB A and also to reduce size of Archive log ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved