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
3
votes
2 answers

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

I am running cron job which have following PL/SQL block: declare begin --insert into DB_LOGS_TEMP table from DB_LOGS table INSERT INTO DB_LOGS_TEMP SELECT * FROM DB_LOGS WHERE DB_LOG_ID NOT IN(SELECT DB_LOG_ID from DB_LOGS_TEMP ); --keep…
Andrew
  • 3,632
  • 24
  • 64
  • 113
3
votes
1 answer

Moving Postgres tablespaces and tables across EC2 instance

I have postgres database running on Amazon EC2 instance. I have few tablespaces created for some monthly tables, such that each table is on individual tablespace. To get the maximum performance, I have created each tablespace on individual amazon…
Sujit
  • 2,403
  • 4
  • 30
  • 36
3
votes
2 answers

Cannot enter data into an Oracle table in production through stored procedure

The row count of the table is 671839. The sequence is in order. Transactions have been made to the table yesterday like it had been made on for the last 7 months, i.e. since the last application deployment. Nothing has been changed in the…
Pratip GD
  • 83
  • 2
  • 7
3
votes
1 answer

Oracle temp tablespace alter statement

What is wrong with my query? I had look here but could not resolve the problem. SQL> CREATE TEMPORARY TABLESPACE TEMPRM; Tablespace created. SQL> ALTER TABLESPACE TEMPRM ADD DATAFILE '+TEMPDATA/rm/datafile/TEMPRM_temp01.dbf'; ALTER TABLESPACE …
Malatesh
  • 1,944
  • 6
  • 26
  • 39
3
votes
1 answer

ORA-30036 happen intermittently

I have a stored proc that do a very large update. Sometimes the job failed with error ORA-30036 Unable to extend segment by 8 in undo tablespace 'undotbs2' But after a few hours, we reran the job and it completed successfully. I checked and found…
timpham
  • 556
  • 2
  • 7
  • 25
3
votes
1 answer

Error committing Oracle tablespace in Docker

I tried to "docker commit" the tablespace I created on a container that is based on alexeiled's oracle xe 11g container. But I got this error: FATA[0027] Error response from daemon: ApplyLayer exit status 1 stdout: stderr: write…
simou
  • 2,467
  • 4
  • 30
  • 39
3
votes
2 answers

Alter Table Move command - ORACLE

Consider i have TABLE1 in Tablespace TS1. I have another Tablespace TS2. What is the difference between the below three ALTER TABLE TABLE1 MOVE NOLOGGING PARALLEL; ALTER TABLE TABLE1 MOVE TABLESPACE TS1 NOLOGGING PARALLEL; ALTER TABLE TABLE1 MOVE…
GKN
  • 107
  • 1
  • 2
  • 8
3
votes
1 answer

How to increase the TEMP TABLE Space value in Oracle?

Currently my Oracle 11g temp TABLESPACE value is 34GB. I need to increase the table space value to a large value (45GB) I tired the following sql command to increase the temp table space. ALTER TABLESPACE temp ADD TEMPFILE…
Ullan
  • 1,311
  • 8
  • 21
  • 34
3
votes
1 answer

How to prevent a user from using space in a tablespace?

I tried the following commands,but i can still insert into the table on appts. Why? MICHAEL@orcl@SQL> alter user michael quota 0M on appts; User altered. MICHAEL@orcl@SQL> select tablespace_name,max_bytes from user_ts_quotas; TABLESPACE_NAME …
3
votes
1 answer

Prevent users to create tables in default tablespace

I've a problem and haven't find any clues so far. I'll try to explain it the best I can, but feel free to ask for more details! Context I'm working with Postgres 9.2.4 on Windows, and I need to implement some kind of quota administration for each…
Chopin
  • 1,442
  • 17
  • 24
3
votes
1 answer

Copying a tablespace from one postgresql instance to another

I'm looking for a way to quickly "clone" a database from 1 postgresql server to another. Assuming... I have a postgresql server running on HostA, serving 2 databases I have 2 devices mounted on HostA, each device stores the data for one of the…
Sebastian
  • 2,678
  • 25
  • 24
3
votes
2 answers

table is in the system tablespace 0 which cannot be discarded

I am trying to run this query: alter table about_member discard tablespace; But when I do, my error logs are outputting this: InnoDB: Error: table `diskise`.`about_member` InnoDB: is in the system tablespace 0 which cannot be discarded What can…
Get Off My Lawn
  • 34,175
  • 38
  • 176
  • 338
3
votes
3 answers

How to import Oracle (C)LOB into another tablespace

I'm importing a database dump from one Oracle 10g installation into another. The source has a layout with several tablespaces. The target has one default tablespace for the user I'm importing the dump into. Everything works fine, for ordinary…
Kriegel
  • 423
  • 5
  • 16
3
votes
1 answer

How to recover sysaux tablespace?

I'm using Oracle 10g and have just found out that the SYSAUX tablespace has RECOVER status. When I check the datafile I can see that it hasn't been updated since May 2012. I understand that the core functionality in the database still is ok but have…
swetom
  • 71
  • 1
  • 5
2
votes
1 answer

Postgres recovery after destruction of temporary tablespace

I'm attempting to speed up the performance of postgresql on ec2. An ec2 node is structured as follows - you have slow, durable network attached storage (EBS), and you also have a fast, volatile storage (ephemeral storage). I.e., in a system crash,…
Chris Stucchio
  • 871
  • 1
  • 8
  • 4