Questions tagged [tablespace]

A tablespace is a database concept and refers to a storage location where the actual data underlying database objects can be kept.

A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments. (A database segment is a database object which occupies physical space such as table data and indexes.)

Once created, a tablespace can be referred to by name when creating database segments. Tablespaces specify only the database storage locations, not the logical database structure, or database schema. For instance, different objects in the same schema may have different underlying tablespaces. Similarly, a tablespace may service segments for more than one schema. Sometimes it can be used to specify schema as to form a bond between logical and physical data.

cf. wikipedia

319 questions
1
vote
1 answer

Querying larg table without extending tablespace in Oracle DB

I have a DB table (used for a queue) that is holding alot rows. Now I'm looking for the oldest entries to see when thes have been enqueued by running the query: SELECT * FROM DB_SCHEMA.WORKER ORDER BY ENQ_TIME ASC; But what I get after some time is…
PaulEdison
  • 897
  • 1
  • 15
  • 36
1
vote
2 answers

How to use different tablespaces for tables and indexes with liquibase in Java

I'm using liquibase (3.6.3) in Spring Boot (2.1.8) java (1.8) application for managing database (Oracle 12.2) schema changes. My liquibase changesets does not contain 'tablespace' params and all data (including indexes) is creating in default…
antropoff
  • 111
  • 2
  • 3
  • 14
1
vote
1 answer

Sort Area Size (KB) - Invalid value for sort area size. The minimum value is: 96 KB

When I try to reorganize a tablespace with OEM 10g, I get an error in the 3rd step (options) saying : Invalid value for sort area size. The minimum value is 96 KB. My SGA management is set to AUTO. Why this error on sort size area ? Thanks for your…
Imran
  • 167
  • 1
  • 1
  • 11
1
vote
2 answers

"No privilege" when creating table with Organization index

I am running Oracle 11.g and the id I am using has DBA role (full access supposedly). When I tried to create a table under another schema, it works fine. However, when I tried to create a table with organization index, I was prompted ORA-01950: no…
Johnny Wu
  • 1,297
  • 15
  • 31
1
vote
2 answers

Need to clean users01.dbf in Oracle 12c

I have Oracle Database 12c in a docker container. At one point, the space in the tablespace expanded (the users01.dbf file became 32GB in size) and I made a new file for this tablespace - users02.dbf. I analyzed the tables and indexes that occupy…
Spark
  • 11
  • 2
1
vote
1 answer

Altering tablespace for partitioned table in Postgres

In my database i have a partitioned table with name 'record_partitioned' on PostgreSQL 11.2. i want to change its tablespace to a new tablespace 'fast_ssd' so all new dervied tables from this table be in 'fast_ssd' tablespace. when i try to alter…
A.F.N
  • 199
  • 2
  • 15
1
vote
2 answers

Is there any major impact if we don't add tablespace clause while creating the table or indexes in oracle?

While creating tables or indexes, I always get the recommendation to add tablespace clause in the queries. Is there any major impact later on our table if we don't use the tablespace clause while creating them ? This is what I am doing for a very…
Amit
  • 11
  • 1
1
vote
1 answer

Persisting a single, static, large Postgres database beyond removal of the db cluster?

I have an application which, for local development, has multiple Docker containers (organized under Docker Compose). One of those containers is a Postgres 10 instance, based on the official postgres:10 image. That instance has its data directory…
1
vote
1 answer

psql CREATE TABLESPACE shows syntax error

I tried to create a tablespace in a directory to store my databases but it returns syntax error everytime. I entered : CREATE TABLESPACE "general" LOCATION "/media/tahnoon/qwerty/Data/PSQL DATABASES"; And it returned : ERROR: syntax error at or…
tahnoon
  • 133
  • 1
  • 14
1
vote
0 answers

Create tablespace with IMP Oracle10

friends, I have a .dump file of oracle 10G that I have to restore with IMP in oracle 11G. but I don't know how to do it so that you don't put the tables inside the tablespace of the system. The command I'm using is imp system/passw…
Juancho
  • 13
  • 4
1
vote
1 answer

Is it possible to declare a separate tablespace in interval partition?

I have a dedicated tablespace for data and a dedicated tablespace for index. I migrated a table to a interval partitioned table and rebuilt the indexes on a separate tablespace. Then new records entered the table, creating new interval partition. My…
1
vote
1 answer

Drop table and temporary tablespace

I'm using oracle 11g and i have 3 tables, when i performed truncate on the first table no changes happened on the TEMP table space, also when i performed delete on the second table no changes happened on the TEMP table space, . But when i performed…
Ammar IT
  • 11
  • 1
1
vote
1 answer

Unable to understand how ibd file encryption is helping secure the data in MySql

I am using MySql 8.0.11 and using keyring_file plugin, I have encrypted a particular table, say t1, in my database. When I check the contents of t1.ibd file I can see that the contents are encrypted after successful encryption. But, I continue to…
1
vote
1 answer

Can I Restore data from ORACLE datafiles folder?

my server had been attacked by a ransomware .rapid and all my data had been encrypted , luckily for me the oracle home folder is not encrypted - yet - and most of the files including the datafiles folder and tablespaces are still accessible Can any…
Ali3lo
  • 19
  • 1
  • 4
1
vote
1 answer

How to move schema objects to a different tablespace

I am using Oracle database 12c. I understand that a tablespace is a logical storage unit made up of one or more datafiles in which data about schema objects is stored. I also understand how to create tablespaces. My question is: Which schema…
JTruant
  • 387
  • 2
  • 6
  • 19