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
2
votes
1 answer

unable to extend temp segment by 128 in tablespace TEMP

I am trying to execute below query in Oracle: SELECT DISTINCT t4.s_studentreference "Student ID", t3.p_surname "Surname", t3.p_forenames "Forenames", t1.m_reference "Course", t2.e_name "Enrolment Name" FROM student t4, person t3, …
Aruna Raghunam
  • 903
  • 7
  • 22
  • 43
2
votes
1 answer

How do I get a PostgreSQL tablespace on a second, installed hard drive?

I have PostgreSQL 9.5 installed on Ubuntu 16.04. Since it is on an SSD, and I don't want to write to it too frequently, I would prefer to keep most of my data on a hard disk (connected via SATA) that I recently installed and partitioned to an Ext4…
Max Candocia
  • 4,294
  • 35
  • 58
2
votes
1 answer

How to create database and tables in custom Tablespace in PostgreSQL?

I'm trying to create a database and tables in a custom tablespace, but instead 'pg_default' tablespace is used for tables. Here's example: mkdir /data chown postgres /data CREATE TABLESPACE mytspace OWNER postgres LOCATION '/data'; CREATE…
dcpt
  • 81
  • 1
  • 7
2
votes
0 answers

MySQL tablespace exists error, no access to ssh or drop database privilege

When I'm trying to create a table I get this error: Tablespace for table '`database`.`table`' exists. Please DISCARD the tablespace before IMPORT I found many solutions to this but every single one requires SSH access to server or drop database…
sajushko
  • 418
  • 4
  • 13
2
votes
1 answer

Migrating table with composite partition (Hash/Range) to different tablespace

Trying to move table tab from tablespace oldTs to newTs. There is a composite partition (Range/Hash) on tab. Hence, a direct "Alter-Table-Move-Tablespace" query won't work, need to migrate partition by partition. Below is the SQL of tab: CREATE…
Lastwish
  • 317
  • 10
  • 21
2
votes
2 answers

Oracle XE data limit reached - how to reduce tablespace size?

I have an Oracle XE database with several tablespaces. One of these is used to store image data, and this tablespace has grown to a huge size. We decided that we didn't need to the images any more so deleted them all. This has freed up a lot of…
user1578653
  • 4,888
  • 16
  • 46
  • 74
2
votes
2 answers

Oracle SCN clarification

I would like to know why I am getting different SCN number for the below quires SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL - I USE this for POINT IN TIME RECOVER FOR TABLESPACE . SELECT CURRENT_SCN FROM V$DATABASE. - I use this for Database…
user961532
  • 87
  • 9
2
votes
2 answers

SQL altering tablespace

Okay as I was creating my tables, it gave me the following error: ORA-01536: space quota exceeded for tablespace 'USERS' So I type this command to change the tablespace: ALTER USER bju5108 quota unlimited on USERS; And then it gave me this message:…
2
votes
1 answer

Oracle users tablespace has high allocated memory but not used, possible cause?

On my development environment I have Oracle 10G XE installation. I often run scripts which contains a lot of Stored Procedure definitions. It may happen that I submit CREATE OR REPLACE PROCEDURE xxxxxxxxxxxx for hundreds of Stored Procedures many…
hijack
  • 311
  • 2
  • 6
2
votes
2 answers

Create foreign key and use reference over different tablespaces?

I am practicing database creation and tablespaces and table implementation. I have a problem which I have searched the internet for some ideas, but none of them are precise to what I have now. My question is if it is possible to create a table which…
ditmark12
  • 111
  • 1
  • 11
2
votes
1 answer

Create tablespace in postgresql and set is to it is the default for all newly created databases

I have created a tablespace named hdd_tablespace and I wan't all new databases to be automatically created there. Basically when I execute: CREATE DATABASE FOO; I want this database to be created in hdd_tablespace. I have updated postgresql.conf…
jb.
  • 23,300
  • 18
  • 98
  • 136
2
votes
1 answer

Oracle 11g for a MySQLer, concept of database

I come from a strong experience in MySQL, and I am now starting with Oracle. But I find really difficult to understand what a DATABASE is in Oracle, given that they use similar concepts which I am struggling to differentiate. In mysql, there is a…
Whimusical
  • 6,401
  • 11
  • 62
  • 105
2
votes
1 answer

Oracle create index not in temp tablespace

I have a great table with over 2.000.000.000 rows. Therefor i want to create an index on column1: CREATE INDEX "SCHEMA"."INDEX_TABLE1" ON "SCHEMA"."TABLE1" ("STR1") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING STORAGE(…
bladepit
  • 853
  • 5
  • 14
  • 29
2
votes
0 answers

Import Dump File into Oracle Express 11g ORA-01950

I'm trying to import a some dmp files from a client into a locally installed Oracle Express 11.2g and have been running into some problems. The import command I am using is: imp system/******…
user2387390
  • 21
  • 1
  • 3
2
votes
1 answer

Usage of Database, schemas and tablespace

we are working on data migration of sql server 2000 to Oracle 11g. Sql server has 4 databases which has to be migrated. These 4 databases are used for 6 different standalone applications. Oracle is installed in Unix server. Can we create a single…
ppusapati
  • 53
  • 2
  • 10