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
0 answers

Is it possible for an Oracle user without access to a tablespace to insert values into a table placed in this tablespace?

I revoked a user's access to a tablespace. I thought that this user wouldn't acomplish to insert data in tables from this tablespace. But this user still can. Is this the normal behaviour or am I missing something? Thank you
frankdart
  • 59
  • 2
  • 10
0
votes
1 answer

Initial Extent for a table gets allocated only after data insertion in Locally managed user tablespace

In case of Locally managed SYSTEM tablespace, when objects are created in a user tablespace (which is also locally managed) initial extents are getting allocated only after data insertion. Whereas in the same scenario where SYSTEM tablespace is…
0
votes
1 answer

PostgreSQL deleting a tablespace without running service

During a reset procedure, we need to reset the PostgreSQL database (on Gentoo) as well, this means clearing a given database completely. During this time we have access to the filesystem only. As stated in the docs, and proven by reality - just…
MemLeak
  • 4,456
  • 4
  • 45
  • 84
0
votes
1 answer

Create LOB Tablespace in Db2

I am running below mentioned query for creating LOB tablespace but getting error. Can anyone check why this error is coming. Query :- CREATE LOB TABLESPACE RESUMETS IN BLUDB LOG NO CLOSE NO; Error :- SQL> CREATE LOB TABLESPACE RESUMETS IN BLUDB…
user3568717
  • 67
  • 1
  • 2
0
votes
1 answer

Tablespace Segments and Schema Objects

I am learning about tablespaces, but I am not sure what a segment of a tablespace constitutes. Is it safe to say that the following query displays all schema objects that can be moved to different tablespaces: select distinct segment_type from…
JTruant
  • 387
  • 2
  • 6
  • 19
0
votes
1 answer

Oracle segment space

I have a Tablespace with some tables and I can insert data without problem. Now I want to create a new table and I get this: 00000 - "unable to create INITIAL extent for segment in tablespace %s" *Cause: Failed to find sufficient contiguous…
0
votes
1 answer

Oracle Undo tablespace after commit and record stored after undo_retention is crossed?

When I commit a transaction in oracle, it looks like my undo segment is released. Where does my old values go after commit? What happens to undo tablespace after we commit a transaction? And when we flashback the table, it looks like we can still…
Saman
  • 333
  • 3
  • 9
0
votes
0 answers

Encounter ORA-00600 when moving tablespace in oracle trigger

I need to change the tablespace when client create table with specified tablespace, so I write the following trigger: CREATE OR REPLACE TRIGGER TRIG_MON_DDL AFTER CREATE OR ALTER ON DATABASE DECLARE SQL_TABLESPACE_NAME VARCHAR2(60); INDEX_TABSP_NAME…
kira
  • 31
  • 4
0
votes
0 answers

Restore MYSQL table data with ibd or frm files

I want to restore my table data with available file type .frm and .idb, based on this link. It was going smoothly until step 3, which gives Error Code 1030, Got error -1 from storage engine. It's a query to discard tablespace and remove idb file…
Rayan Suryadikara
  • 237
  • 1
  • 3
  • 17
0
votes
0 answers

Revoke any right for a user to another schema

I'm trying to forbid a user from one schema A (tablespace A) to alter, delete, drop, insert on any table of schema B (tablespace B) but allowing it to perform those tasks inside "his" tablespace (A). By revoking those grants, the user is not able to…
Antoine
  • 55
  • 1
  • 7
0
votes
0 answers

Oracle - synchronise tablespace schema changes to another tablespace from the same database

I am using two tablespaces from the same Oracle database (11g), one is tablespace A, the other is B. I'd like to set a daily planified task which would alter the tablespace B in order to integrate the changes made to the tablespace A during the day…
Antoine
  • 55
  • 1
  • 7
0
votes
0 answers

Oracle Database Error: ORA-01119 signaled during creating tablespace

I'm running an Oracle Database 12c Release 1 (12.1.0.2.0) on a Linux machine. To give some context to the situation, I've performed a partition for the database and am now trying to create a tablespace. It doesn't go through and when I check my logs…
M. Sun
  • 1
  • 1
  • 2
0
votes
1 answer

How to know how much temp table space required for expdp job?

expdp $DBNAME directory=ar_exp content=data_only dumpfile=${tablename}.dmp logfile=${tablename}.log tables=$tablename query=$SCHNAME.$tablename:'"where substr('$fieldname',1,5) in('$cpidlst')"' Here this operation takes temp tablespace, but how…
Sparsh
  • 39
  • 6
0
votes
1 answer

DB2 tablespace state last changetime

Is there any db2 command or SQL query will show me last change time of table space to current state?
Rovshan Musayev
  • 144
  • 3
  • 15
0
votes
0 answers

oracle temporary tablespace not taking all the place he has

I got a problem with my temporary tablespace. I'm on Oracle. I need a temporary tablespace of about 8G for an import. Problem is I don't have that much place in just one place of my server. So I did multiple tempfiles : CREATE TEMPORARY TABLESPACE…
Valentin Rochet
  • 137
  • 1
  • 11