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
166
votes
27 answers

Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT

I am running a local server of MySQL 5.6.10 on MacOS 10.8.3 and manage my database via Navicat essentials for MySQL. The error I get is that after running and managing my database just fine for a couple of days/weeks something triggers to (it…
MattMirabilis
  • 1,669
  • 2
  • 11
  • 3
129
votes
1 answer

ORA-01950: no privileges on tablespace 'USERS'

I'm getting this error: ORA-01950: no privileges on tablespace 'USERS' I have a default tablespace and I'm able to create tables, however, they do not show at the objects tab in Toad. I cannot insert anything into tables I create.
sam
  • 2,493
  • 6
  • 38
  • 73
60
votes
3 answers

ORA-01653: unable to extend table by in tablespace ORA-06512

I tried to generate some test data by running the following sql. BEGIN FOR i IN 1..8180 LOOP insert into SPEEDTEST select 'column1', 'column2', 'column3', 'column4', 'column5', 'column6', 'column7', 'column8', 'column9', 'column10',…
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
51
votes
12 answers

Find out free space on tablespace

Our application has failed a few times because an 'ORA-01536: space quota exceeded for tablespace', and we would like to be able to prevent this by checking regularly the free space on the tablespace and raising an alert when it drops below certain…
Avs
  • 633
  • 1
  • 6
  • 6
47
votes
5 answers

What is a tablespace and why is it used?

While investigating an issue, I came across this error: 30503 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysql/innodb_index_stats uses space ID: 2 at filepath: ./mysql/innodb_index_stats.ibd. Cannot open…
Bhupesh Pant
  • 4,053
  • 5
  • 45
  • 70
30
votes
4 answers

ORA-01652 Unable to extend temp segment by in tablespace

I am creating a table like create table tablename as select * for table2 I am getting the error ORA-01652 Unable to extend temp segment by in tablespace When I googled I usually found ORA-01652 error showing some value like Unable to extend temp…
Niranjan Sonachalam
  • 1,545
  • 1
  • 20
  • 29
26
votes
7 answers

How can I tell what is in a Postgresql tablespace?

I've created a new tablespace called indexes, and I'm trying to remove the old tablespace indexes_old, which used to contain some tables and indexes. When I try to drop the tablespace, I get: => drop tablespace indexes_old; ERROR: tablespace…
Andy Lester
  • 91,102
  • 13
  • 100
  • 152
25
votes
3 answers

ORA-01658: unable to create INITIAL extent for segment in tablespace TS_DATA

When i tried to create a table in my User_DB schema i am getting an error as ORA-01658: unable to create INITIAL extent for segment in tablespace TS_DATA. I run the following query to get all the TABLESPACE_NAME: SELECT * FROM DBA_DATA_FILES; But i…
Andrew
  • 3,632
  • 24
  • 64
  • 113
23
votes
1 answer

How to find table and column in DB2 with tbspaceid tableid specified in error message

I get following error message when trying to insert an object in the database: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=19, COLNO=0,…
Mathias G.
  • 4,875
  • 3
  • 39
  • 60
21
votes
3 answers

Creating a tablespace in postgresql

I'm trying to create a tablespace in postgres, but I'm getting ownership problems. The command I'm using is: CREATE TABLESPACE magdat OWNER maggie LOCATION '/home/john/BSTablespace' I get the error: ERROR: could not set permissions on directory…
The_Denominater
  • 985
  • 4
  • 9
  • 20
17
votes
5 answers

Should an Oracle database have more than one tablespace for data storage?

My team maintains an Oracle database that is approx. 200GB in size. All of the data (tables, indexes, etc) lives inside a single 'USERS' tablespace. Is this a bad idea? What benefits are there to having multiple tablespaces, and under what…
Kevin Babcock
  • 10,187
  • 19
  • 69
  • 89
16
votes
3 answers

Is there any logical reason of having different tablespace for indexes?

Hi Can some let me know why we created different table space for Index and data.
P Sharma
  • 2,638
  • 11
  • 31
  • 35
16
votes
4 answers

how to determine size of tablespace oracle 11g

I have a database with three tables. I need to move historic partitioned data to other schema Now that i´m planning to creatre the new "historic" tables. I don´t know how to measure the size of partition and subpartitions. Can u help me? please…
user2115594
  • 183
  • 1
  • 1
  • 8
13
votes
3 answers

Where in the DB is the Location of a postgres tablespace stored

I am using postgres 9.2 on redhat 6 this should be simple but I can't find it anywhere. I am looking for the database table and column which stores the Location for a postgres tablespace, I thought it would be in PG_TABLESPACE, but select * from…
davegreen100
  • 2,055
  • 3
  • 13
  • 24
13
votes
2 answers

could not write to hash-join temporary file: No space left on device

I am executing PostgreSQL functions to update a table which has a huge amount of data and the update happens for about 100000 records everyday. During the update I get an error saying: "could not write to hash-join temporary file: No space left on…
Yousuf Sultan
  • 3,055
  • 8
  • 35
  • 63
1
2 3
21 22