Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Binary Large OBject, datatype

0
votes
0answers
10 views

Is it possible to distribute the pg_largeobject with citus?

Is it possible to distribute the pg_largeobject with citus? There are only Examples for normal Tabeles, but we have a huge amount of blobs in our database and want to shard them.
0
votes
0answers
113 views

Unable to access LOB stream after importing a data-only dump from some tables

We've been working on an application for a long time and after all the tests and adding necessary features, we decided to change the phase from testbed to production. There were tables we needed the ...
1
vote
1answer
36 views

Storing files with extension in database as BLOB

Recently during development process I've faced file storage issue. I'm not sure which solution is the best one that's why I decided to write this question. I don't have a lot experience of database ...
3
votes
1answer
60 views

How large is SQLites minimum data storage overhead and what generates them?

Motivation: In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better ...
0
votes
0answers
126 views

Prevention of ORA-01555: snapshot too old: rollback segment number with name “” too small

We have a cashier system relying on Oracle databases for data storage. Any time connection to the database abruptly goes down (for example due to the store being out of power and UPS not working ...
1
vote
1answer
27 views

What data is duplicated when MySQL/MariaDB BLOB columns are copied?

Let table_1 be created as follows: CREATE TABLE table_1 ( id INT AUTO_INCREMENT PRIMARY KEY, some_blob BLOB ); Let table_2 be created as follows: CREATE TABLE table_2 ( id INT ...
4
votes
2answers
274 views

Please enlighten me about FILESTREAM and BLOB files

This is a really confusing topic for me. I can understand what a BLOB is, I can easily use it. I can understand what FILESTREAM is. I can easily implement in on a database, I can backup the database ...
2
votes
2answers
236 views

In what cases are BLOB and TEXT stored in-line on InnoDB?

I've googled this and can't find it, you can see Rick James mention it here. ... BLOB and TEXT are not always stored separately The answer on What is the difference between MySQL VARCHAR and TEXT ...
1
vote
1answer
68 views

SQL Anywhere Long Varchar/Binary Column Length Limited to 32767

Attempting to set up a database table (Sybase SQL Anywhere 11.0.1) to store user uploaded documents as either BLOBs or base64 encoded strings, but running into an issue where the length of my LONG ...
0
votes
2answers
34 views

Retrieve date column default values from BINARYDEFVAL column in sys.ecol$

I am trying to retrieve BLOB values in BINARYDEFVAL column from sys.col$ table. The BINARYDEFVAL of sys.ecol$ table stores default column values in BLOB format. For VARCHAR2 and NUMBER I am able to ...
1
vote
1answer
44 views

LOB in frequently queried table or separate table

I have a table named FinTrans in an Microsoft SQL Server 2012 server with approx 10K-30K records at any given time and about 2K records are inserted and deleted each day. The table has 3 nvarchar(...
3
votes
3answers
226 views

How to retrieve a large text from inside a table?

for my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
1
vote
1answer
324 views

Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

We want to implement the Always Encrypted feature for our application database. There is a table with a varbinary(max) column which is storing binary data of images, pdf documents, etc. Does the ...
0
votes
1answer
196 views

Oracle reorganize lob segement

On an Oracle 12c database I want to reorganize a LOB segment. I used to following sql to generate the alter move command: select 'ALTER TABLE '||s.owner||'.'||l.table_name||' move to('||l....
0
votes
0answers
23 views

How can I save a file into a longblob column on a database located on another server?

I have a database table that contains large files in one of the columns, stored as longblob data. The blobs can currently be up to around 300MB in size. When reading the blobs from the database I'm ...
0
votes
0answers
108 views

How to import CVS with HEX data to Oracle BLOB

I have a CVS file with this content: "d1","...
0
votes
2answers
352 views

Why to put unstructured data in NoSQL DBs?

The statement that NoSQL databases are better for unstructured data because of their schemaless nature can be found everywhere. Frankly, I don't understand that. A human voice recording is often be ...
1
vote
1answer
165 views

Chunking data into a PostgreSQL bytea column?

I have column of type bytea in PostgreSQL database. If I update a row, is it possible to chunk the data into the bytea column in several small steps? The size of one bytea entry will be mostly below ...
0
votes
1answer
81 views

Export a very big amount of files from a table

I'm using Oracle Database 11g Release 11.1.0.6.0 and have a table recording a very big amount of files (GIF) with IDs (ID). Name Type ------------------------...
0
votes
2answers
263 views

Dump single rows (with blobs) to single files

I am creating MySQLDumps for the sake of versioning of a database. The problem currently is a table with blobs. My first approach would be to dump single tables to single files. But next, I want on ...
0
votes
1answer
324 views

Calculating the FileStream Data Columns Length matching used FS Drive used space

Kindly advice me on below : I am process of calculating the total size of LOB objects with below data types (includes Views and user schema tables) . select * from information_schema.columns where ...
1
vote
1answer
127 views

What is the 131072 in the call to lo_open()

ByteA to BLOB conversions typically make a call to lo_open(), CREATE OR REPLACE FUNCTION make_lo(bytea) RETURNS oid AS $$ DECLARE loid oid; fd integer; bytes integer; BEGIN loid := lo_creat(-...
5
votes
0answers
656 views

VARCHAR(MAX) taking more space than NVARCHAR(MAX) [duplicate]

I have a table in SQL Server 2012 Enterprise Edition that has ~100GB of data. Most of this data is attributed to a single NVARCHAR(MAX) column which has an average length of 4500 characters. It stores ...
2
votes
2answers
1k views

Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file

I am stuck at this for a while and had been Googling for answer but can't seem to find any.... I have an Oracle 9i table with BLOB field that I need to extract the BLOB field into a raw file on the ...
1
vote
0answers
30 views

Wasted space for compressed SecureFile LOBs?

When using Oracle BasicFile and SecureFile one property of the implementation is that each LOB occupies multiples of the chunk size (and in case of SecureFile the smallest chunk size is the block size)...
8
votes
1answer
376 views

Best way to shrink a DB after nulling data from varbinary(max)?

We have a database with a large amount of data stored in a field of type varbinary(max). At some point we can purge that data for most rows, but not all. Our plan is to make that field nullable, and ...
4
votes
1answer
4k views

How to copy blob (binary data) from one DB to DB using a script?

We are trying to copy a blob (binary data) from one Database to another Database using a simple script. This is the steps we do. Select the value using MSSQL Studio Client. Copy it into the clipboard....
2
votes
1answer
112 views

Does AWS ever `vacuumlo` PostgreSQL RDS instances?

Link to the long story happened before (and reason to have) this question. Does AWS ever vacuumlo PostgreSQL RDS instances?
20
votes
3answers
5k views

Why is it recommended to store BLOBs in separate SQL Server tables?

This highly-upvoted SO answer recommends to put images in separate tables, even if there is only a 1:1 relationship with another table: If you decide to put your pictures into a SQL Server table, I ...
3
votes
1answer
944 views

How to `vacuumlo` an RDS PostgreSQL database?

As you may have noticed, as a fully managed data base as a service product, AWS Relational Data base Service (RDS) restricts the execution of userland commands. I was staging a production database, ...
2
votes
2answers
5k views

How to show Large Objects (LOB) contents? [duplicate]

How could I get to have a look into LOB contents? \d ticketgrantingticket Table "public.ticketgrantingticket" Column | Type | Modifiers ----------...
1
vote
0answers
135 views

Clean up space used by internal LOB in Oracle

I have a table with a CLOB column which is stored as internal LOB. The data in this column becomes obsolete quickly (within matter of days), and is never accessed afterwards. I wonder whether ...
1
vote
1answer
928 views

Storing large files (~1GB) in MySQL

We are considering a solution that will store large files in MySQL with InnoDB. The size of the files can range from 250MB - 1GB (might grow up to 50GB in future). Here is what the plan is. Create ...
2
votes
2answers
4k views

How to insert big blob data in Oracle?

I'm trying the following: INSERT INTO (blob_column) VALUES (HEXTORAW('very big hex here')) But I'm getting the following error: ORA-01704: string literal too long How can I workaround that? Is ...
4
votes
1answer
472 views

Database for store images

Actualy I'm using MongoDB with embedded images next to other data in same documents. Images count actualy are more than 14000 and each image size is less than 1MB. Images are readed frequently and ...
0
votes
1answer
255 views

Does dbms_lob.getlength return the compressed or uncompressed length?

If you have tablespace compression turned on, does dbms_lob.getlength return the compressed or uncompressed length of lobs stored on that tablespace? What about LOB compression?
1
vote
1answer
128 views

What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data?

I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename. CREATE TABLE Images ( RecordID BIGINT NOT NULL, ImageFilename VARCHAR(50)...
2
votes
1answer
914 views

Very slow query on table with Blob column [closed]

I have a table with approximately 3500 rows. This table contains a longblob column with an average blob size of 750kb. My query performance really deteriorates when trying to filter on the primary ...
0
votes
1answer
56 views

Re-page Oracle tables after removing CLOB fields

I have a table that has a large amount of CLOB data and a large number of records. Even with indexes, some queries are extremely slow. My assumption is that the reason for this is two fold: There's a ...
1
vote
0answers
71 views

Most Efficient way to compare large 'text' type values in MySQL using InnoDB

I have a staging table like this : CREATE TABLE `staging` ( `created_here_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `desc_text` TEXT NOT NULL ); And destination table as : CREATE ...
1
vote
1answer
453 views

Do large longtext necessarily slow down a database

I have a MySQL database that is over a million rows, and has a longtext type data column. Most of these are fairly short, but a few dozen of these rows have more than a MB in the longtext column. No ...
1
vote
0answers
169 views

will vacuumlo find orphaned objects

A Table (mytable) is having 2 columns id_ | loid_ (integer) | (oid) Values are id_ | loid_ 1 | 15001 2 | 15002 Value in loid_ column refers the oid from pg_largeobject. And if I ...
0
votes
1answer
1k views

oid vs bytea in postgres

I have a table called tblA and its having two columns id_ (numeric 50,0), obj_ oid For example, tblA having 1 entries id_ | obj_ 1 | 1001 In pg_largeobject table, there are three entries ...
-1
votes
1answer
7k views

ORA-01555: Snapshot too old rollback segment with name “” too small error

I have created a trigger which works on two fields, first field(blob) and second field(varchar2). This trigger updates the blob when there is some changes in second field, from a blob value of another ...
0
votes
2answers
376 views

Command for setting Inline Length has no effect

I dropped a CLOB(2G) NOT LOGGED column in a table and recreated it with the same size and the LOGGED attribute. If I look at the syscat.columns I see that the INLINE_LENGTH is 0. The old column had ...
3
votes
1answer
1k views

postgres backup for specific table with blob oid

I have a table which having an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table is having only 10 entries (which is approximately ...
0
votes
1answer
424 views

Query on Shrinking Table - Oracle

I'm trying to shrink a LOB whose owner is TEST and the tablespace it resides is on USERS. EDIT: Currently, the default tablespace for TEST schema is tablespace called TESTTBL. I have enabled row ...
1
vote
1answer
1k views

How to search in blob binary wise, and how to export data binary wise

I've a table with a blob field for each row. The blob contains binary data, which can be represented as bytes or text ( using mysql workbench - open value in viewer) Does anybody know how can I ...
0
votes
1answer
3k views

MySQL2::Error: BLOB/TEXT column can't have a default value

I am trying to run a rails mysql migration on Amazon Elastic Beanstalk. It fails with this error message: Mysql2::Error: BLOB/TEXT column 'description' can't have a default value: ALTER TABLE ...
1
vote
1answer
289 views

MySQL – BLObs Hitting Performance

One of my tables contains seven BLObs. Six of these refer to raw data and are typically 1.0 – 1.5Mb each. The seventh is the result of processing this data and can be nearly equal in size to the sum ...