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

How to know the usage of temporary tablespace in Oracle

I am trying to compare two tables which are very large in my system(Oracle 10g). The way I used to compare is the "MINUS" operation. Because of the large size of tables, I want to know the usage of the temporary tablespace on the real time. I…
tousinn
  • 73
  • 2
  • 3
  • 11
2
votes
1 answer

Oracle SQL -- can't insert into row

I have a newly created table. When I try to insert into it, I get.. SQL Error: ORA-01950: no privileges on tablespace 'ADMINISTRATOR' 01950. 00000 - "no privileges on tablespace '%s'" *Cause: User does not have privileges to allocate an extent…
Jeremy
  • 5,365
  • 14
  • 51
  • 80
2
votes
1 answer

unable to extend temp segment on CTAS

Before i start if someone know a better way to do this please Share as i having massive problems with data pump as it hangs on tablespace and when i check the tablespaces repot i see nothing being filled. I am trying to CTAS few tables ( create…
Imran
  • 167
  • 1
  • 1
  • 11
1
vote
1 answer

Create temporary tablespace using a query to determine size in Oracle

I'm trying to create a temporary tablespace that would be half the size the 'TEMP' tablespace. So something like : create temporary tablespace Temptest TempFile 'somepath' size ?M; where ? = select bytes/2/1024/1024 from dba_temp_files where…
Pat
  • 23
  • 4
1
vote
1 answer

Pl/sql script to compute the free space in the users tablespace in oracle

SELECT tablespace_name, sum(bytes)/1024/1024 "MB Free" FROM dba_free_space WHERE tablespace_name = 'USERS' GROUP BY tablespace_name; Hi everyone, the above query is what i use for showing the free space in user tablespace but how do i write a…
peedee
  • 1,941
  • 2
  • 14
  • 14
1
vote
1 answer

ORA-12609: tns: receive timeout occurred- Error Occurs When Running Query to Create Tablespace

Could any one help? I generate a script to create a tablespace from Oracle 12c (ASM), then run it into 19c (ASM as well). However, there is an error occurs ORA-12609: tns: receive timeout occurred. CREATE TABLESPACE ARCH DATAFILE '+DATA' SIZE…
Hung Tran
  • 21
  • 4
1
vote
1 answer

EXPLAIN (ANALYZE, SETTINGS) display wrong tablespace parameters

PostgreSQL 12.8 I have a tablespace called mattermost and a database mattermost-dev in it. postgres=# \l+ mattermost-dev List of databases Name | Owner | Encoding | Collate | …
Anton P
  • 27
  • 4
1
vote
1 answer

Join 2 sql outputs with no common column and both having sum()

So I'm trying to join 2 table outputs which has no common column but same column name. SQL> SELECT sum(s.bytes/1024/1024/1024) AS "Total Space" FROM dba_data_files s UNION all SELECT sum(d.bytes/1024/1024/1024) AS "Used space" FROM dba_segments d 2…
mrgvr
  • 13
  • 2
1
vote
1 answer

How do I remove CHECK PENDING state from a DB2 Tablespace on z/OS?

maybe one of you can help me with this DB2 z/OS thingy. I edited foreign key on a table that was already populated. Due to integrity reasons (I guess) the tablespace was placed in CHECK PENDING and I cannot perform operations on it any longer. This…
unR
  • 1,298
  • 14
  • 21
1
vote
1 answer

How to set tablespace for a specific CREATE TABLE [Table] AS

How to set tablespace for a specific table creation AS clause?, like: CREATE TABLE [New Table] AS TABLE [Old Table] --WITH NO DATA ; In which tablespace new table with/without data is created for this specific 'AS' clause? In official…
Gen Eva
  • 39
  • 6
1
vote
1 answer

How to import an oracle table from dump file

I have a dump file with a table but when I execute the import it gives me the error that the tablespace 'TB_SYS_DAT' does not exist and that the user 'ADMIN' does not exist either. How can I make the import perform the creation of the table with the…
1
vote
1 answer

How to check history of tablespace autoextend status in oracle

I am new to oracle. I work on oracle database 12c version. I have to find out if the tablespace autoextend status was disabled for previous week.But i can able to view current autoextend status for a tablespace. Is there any direct approach to find…
Kishan
  • 334
  • 2
  • 16
1
vote
1 answer

How to estimate oracle tablespace I would require before loading the data?

I have huge files ranging from 450 to 750 million rows, that I need to load into our Oracle environment, and DBA asked me to give an estimate on how much tablespace I would need. How to estimate the required data before loading the data into a…
Abhinav Dhiman
  • 745
  • 3
  • 17
1
vote
1 answer

Are data files required when creating tablespaces in Oracle?

I want to create a tablespace, do I have to specify the DATAFILE? Currently, it is written as follows. CREATE TABLESPACE OTS1 DATAFILE 'OTS1.dbf'; If the DATAFILE was not specified, it was expected to be specified as the Oracle default value?, but…
user13746660
1
vote
1 answer

How to change the AUTOEXTENSIBLE column to YES in table DBA_TEMP_FILES for TEMP TABLESPACE in Amazon ORACLE AWS RDS?

I have two Oracle DBs, one locally and another in AWS RDS. In Local, I can give the option AUTOEXTEND ON for tablespaces but RDS does not have this option as far as I know. Also, when I check the DBA_TEMP_FILES in both the DBs, in the local one the…