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

Postgres - Is it necessary to create tablespace in my case?

I have a mobile/web project, using pg9.3 as database, and linux as server. The data won't be huge, but as time goes on, the data increase. For long term considering, I want to know about: Questions: 1. Is it necessary for me to create tablespace…
Eric
  • 22,183
  • 20
  • 145
  • 196
0
votes
0 answers

How to free table space used by CLOB

I have a stored procedure with an input parameter of type CLOB. This parameter is used as follows in the procedure to insert data into the table. PROCEDURE sp_Proc1(ret_Num1 OUT INTEGER, tmpData IN CLOB) AS BEGIN INSERT INTO MyTable WITH…
Saurabh
  • 26
  • 4
0
votes
1 answer

Is it safe to add a datafile to a tablespace which is being used by a index which is currently in the process of creation?

The situation is that I ran a create index query on a table (index type ctxsys.context) about 36 hours ago. The tablespace it refers to has 128gb allocated (4x32gb datafiles). As per latest situation the index is still in progress of being built…
0
votes
1 answer

how do we prevent dml operations for a specific schema for a specified period of time?

Is there any possible way to prevent dml operations for a specific user schema is busy traffic hours without impacting other user schema's dml operations
Watson Ferror
  • 21
  • 1
  • 5
0
votes
1 answer

Oracle SQL Group by Clause

I would like to write a sql to get top 5 table space storage metric. Below query gives the metric about all tbspaces. Appreciate if someone fine tune this to have only top N SELECT ts.tablespace_name AS…
Prakash
  • 17
  • 1
  • 7
0
votes
1 answer

How to export oracle 11g user to 10g and on a different default tablespace

I have an 11g database and I want to export a dump to import it later on a 10g database. I can use expdp with option VERSION=10.2 but my problem is that I have to import this dump on a different tablespace. thank you
ramziy
  • 3
  • 1
0
votes
2 answers

How can I delete the users datafile in Oracle using SQL?

I have tried dropping the datafile by itself but I get this error. SQL> alter tablespace USERS drop datafile '/u01/app/oracle/oradata/orcl/users01.dbf'; alter tablespace USERS drop datafile '/u01/app/oracle/oradata/orcl/users01.dbf' * ERROR at line…
swanhella
  • 441
  • 6
  • 14
0
votes
0 answers

unable to create INITIAL extent for segment in tablespace SYSAUX

My program creates a database schema. The queries related to tables and/or view creations work just fine but the first insert I do fails with unable to create INITIAL extent for segment in tablespace SYSAUX error. It is a straightforward insert in…
ptpdlc
  • 2,373
  • 6
  • 22
  • 33
0
votes
1 answer

How to fix : unable to extend segment by 8 in undo tablespace?

I'm trying to add 10m data to my database table and at about 3,5m it stopped and I got the error unable to extend segment by 8 in undo tablespace. I've never encountered that before and I have no idea how to fix it. I'm running my scripts through…
aiden87
  • 929
  • 8
  • 25
  • 52
0
votes
0 answers

alter tablespace db2 when the Table space type is "AUTOMATIC STORAGE"

Good day, I would like to alter a tablespace to bigger size. The following is my query: ALTER TABLESPACE USERSPACE1 resize (ALL 8192) Then I hit error as follow: [Error Code: -20318, SQL State: 42858] The ALTER TABLESPACE statement failed…
Panadol Chong
  • 1,793
  • 13
  • 54
  • 119
0
votes
0 answers

Auto creating new datafile in oracle

I have some problem with Oracle database. Before it works like that, if INDX[Counter].dbf file reach its maximum size (8GB) Oracle automatically created new datafile INDX[Counter+1].dbf. But on server wasn't enough size to create new datafile. I…
Marcin1199
  • 83
  • 1
  • 11
0
votes
1 answer

Release unused space of USERS tablespace in oracle

I have lots of table with lots of records in oracle 11g. (more than 2 billions) After applying some queries and creating some indexes I am so close to insufficient disk space. Right now for executing each query ORA-01652 error for USERS tablespace…
Ali
  • 1,759
  • 2
  • 32
  • 69
0
votes
2 answers

How to know how much space on tablespace will the creation of an index take

I have to create an index on a specific tablespace, on oracle database. I would like to know if there is a way to tell how much space of the tablespace will it take the creation of the index, so I can assure that my tablespace is capable of handling…
Cristian
  • 199
  • 2
  • 13
0
votes
1 answer

Why am I getting ORA-02180, if my CREATE TABLESPACE command syntax is correct?

I am trying to create a new temporary tablespace for testing purposes (the code to be tested is actually designed to work with an external Oracle database, all the important tables sharing the same tablespace named `sbrdwh'). To test the code, I…
distort86
  • 43
  • 4
0
votes
1 answer

How to work with schemas(Oracle) in symfony?

I have those entities, on each entity is defined "name" and "schema" on parameters in @ORM\Table annotation, but doctrine don't dump the schema on sql. UPDATE: if i change the @ORM\Table parameters to (name="RRHH.usuario") the dump don't show the…
Jorge
  • 63
  • 7