Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

A database storage unit that groups related logical structures together.

0
votes
0answers
24 views

TEMP Tablespace Removal

Oracle Version: 12.1.0.2 We have a discrepancy between our PROD and DR TEMP tablespace data files. PROD = 30G + 30G + 30G data files DR = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files I ...
2
votes
0answers
35 views

changing tablespace seems to waste space

I have a large (several TBytes) Postgres database (v10 on windows) containing tables that are partitioned by date-range. Depending on volumes, some of them are partitioned by day, others by month. I ...
0
votes
1answer
21 views

Is it safe to move big table to another tablespace while working with it?

I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat ...
1
vote
0answers
20 views

Oracle Database Recovery [closed]

I recently develop an app using Oracle Application Express on a Windows OS, after uploading lots of data. Unfortunately for me the OS field before I could make any backup but, I salvage the data files ...
1
vote
1answer
34 views

Creating a table space sql developer

I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here. I'...
0
votes
0answers
50 views

Moving undo tablespace data file to new location in 12C

We have moved undo tablespace datafile to new location as bellow: ALTER DATABASE MOVE DATAFILE '/oracle/app/oracle/oradata/ETATD/undotbs01.dbf' TO '/TD-Data/table_space/undo_datafile/undotbs01.dbf'; ...
1
vote
1answer
120 views

DB2 Tablespace in state Restore pending

I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined. When I set off the following ...
0
votes
1answer
64 views

How to import an existing schema and tablespace into a postgres database, from a filesystem-level backup

Follow-up question from: Have postgres' pg_dump export an index Say I have an existing database, and I want to add to it another schema with content. I saved that content previously through a ...
1
vote
4answers
123 views

TABLESPACE creation error

I have an error "ORA-01537" when I create a tablespace. create tablespace "VOLUMT" datafile 'VOLUMT.dbf' size 250M autoextend on; Then, it shows like following. ERROR at line 1: ORA-01537: cannot ...
2
votes
0answers
356 views

Getting “Cannot open datafile for read-only” for an intentionally DROPped InnoDB table

It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB): [ERROR] InnoDB: Cannot open datafile for ...
0
votes
1answer
19 views

How do you list all items from all databases located in a specific tablespace in postgresql?

I'm trying to clean out some old tablespaces in my postgres instance, and I can't seem to figure out how to list everything in a specific tablespace. SELECT * from pg_tables WHERE tablespace ='...
3
votes
0answers
539 views

Why do I get a PostgreSQL permission error when specifying a tablespace in the “create database” command?

When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user): postgres=> create database rich1; ...
2
votes
1answer
2k views

Oracle 12c export and import schema with different data tablespace and index tablespace

Suppose that I exported the schema objects from my Oracle 12c database using the original export utility as below: exp userid=<export_schema_name>/<export_schema_password>@<db_string&...
2
votes
3answers
4k views

Oracle shrinking / reclaiming free tablespace space

I have a Oracle 12c tablespace with the size of 90GB. Almost 40GB are free. TB1 54 % 90880 41383 49497 46 98304 50 % ONLINE PERMANENT The tablespace has 3 datafiles. E:\ORACLE\...
1
vote
2answers
92 views

Rebalance/move or strink oracle undotablespace

I'm running out of space in oradata01 and oradata02. Is there a ways to move or rebalance with oradata03? /opt/oracle/oradata01/x/undotbs01.dbf 4 100 % /opt/oracle/oradata02/x/undotbs02.dbf ...
0
votes
1answer
299 views

DBA_TABLESPACE_USAGE_METRICS is empty

For one of my 11.2g databases, select * from dba_tablespace_usage_metrics returns no rows. Why is that and how can I fix it? Thank you in advance.
0
votes
0answers
55 views

Tablespace auto-extend feature in mySQL - how many times it get extended

Situation: We have some alert on our mySQL servers instance about high Disk IO overload in a timeframe during the night. We have huge database and they are in auto-extend mode; the tablespaces are ...
0
votes
1answer
1k views

How to rebuild SYSAUX corrupted tablespace?

I have database 10g 32-bit windows without backup. I want to migrate full database to 11g, but the SYSAUX tablespace is corrupted. Can I rebuild the SYSAUX tablespace?
3
votes
6answers
4k views

Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2

SYSTEM and SYSAUX Tablespace size of my DB is in the following status now: SYSTEM - PCT_USED= 99 SYSAUX - PCT_USED= 95 I believe the db is in critical situation now. What can I do to bring back it ...
2
votes
1answer
110 views

Specificness of Tablespace

I'm fairly new to working with databases and am trying to fully understand the purpose of a tablespace. Lets say I have a system where a user will input a schedule of tasks that need to be performed....
0
votes
1answer
36 views

Oracle writes whole file during database creation is it ok?

Consider empty (!) database creation: CREATE TABLESPACE reload DATAFILE 'D:\myDb.dbf' SIZE 3000M ONLINE; This is leads to writring 3000 megabytes to the disk for empty database. Is it normal for ...
1
vote
1answer
3k views

SYSTEM tablespace getting filled up

My SYSTEM tablespace is getting filled up enormously within a few days. I've added another dbf file when it was filled up completely earlier. Now, the second .dbf is also about to fill. The only ...
0
votes
1answer
153 views

Alter Postgres Tablespace [closed]

I can't seem to create a table in a particular tablespace. For instance, when I do: CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space; and then I do: select * from ...
0
votes
1answer
300 views

Why can't I create temporary tablespace?

I'm trying to create dictionary managed tablespace with the TEMPORARY clause and TEMPFILE clause of CREATE TABLESPACE statement. But oracle says I can't use the DEFAULT STORAGE clause doing that... ...
1
vote
1answer
59 views

How much Tablespace we need to create for Oracle schema [closed]

How is the optimum number of tablespace we need to create in an Oracle schema ? One for data and one for index ? Thanks
3
votes
0answers
271 views

Oracle 12c tablespace monitoring tool [closed]

I am looking for a tool to monitor Oracle tablespaces and receive email alerts when they get over a defined percentage. We want to be able to detect the issue before having to react to the problem.
1
vote
1answer
76 views

How does PostgreSQL decide what to name its tablespace directories?

Using the psql CLI tool with a PostgreSQL 9.6.1 server running on localhost, I created a new tablespace with create tablespace my_tablespace location /tmp/data; and found that this created a ...
-1
votes
1answer
308 views

ORA-01536: Out of space error when doing CTAS

I have two schemas A and B. I'm trying to copy over table B.users to A. I've followed these steps: Run in schema B: GRANT ALL ON users TO A; Run in schema A: CREATE TABLE users AS (SELECT * FROM ...
1
vote
2answers
98 views

How can I insert into a Destination Table from multiple tables concurrently..?

I have a monthly single threaded process that restores about 2,000 databases and pumps data into a table then drops the DB. It takes about 18 hours for the insert to complete (Its via an SSIS Package)....
1
vote
1answer
243 views

oracle schema grants and tablespaces

I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that ...
1
vote
1answer
18k views

ORA-00959: tablespace does not exist but I can see it in select

So, I am connected as SYSDBA to Oracle and trying to run this command: create user C##demo identified by demopassword; That ends with User created. Then I run: alter user C##demo default ...
2
votes
1answer
857 views

Oracle: Not able to create tablespace from stored procedure

I am trying to create tablespace from stored procedure. create or replace procedure testprc is query_string VARCHAR2(4000); tbs_name VARCHAR2(20):='test77'; begin dbms_output.put_line('test..'); ...
0
votes
0answers
533 views

Create Tablespace in a specific location with PostgreSQL

I want to create a database in a specific location. I know that every database belong to a tablespace, so I tried to create a tablespace with this command: CREATE TABLESPACE myTablespace LOCATION 'C:...
1
vote
1answer
3k views

How to reduce sysaux tablespace size with Audit Trail Unified enabled

I have Oracle 12c Standard instance with dev database with enabled Audit Trail Unified. And now SYSAUX tablespace has size 32Gb, but all data is only 9Gb. When I tried to reduce size of SYSAUX I ...
2
votes
2answers
606 views

Balancing the size of oracle datafiles for a tablespace

I have a tablespace where I had to add datafiles, because a partion run full. The new datafiles are not filled yet. Can I move data from one datafile to the other?
2
votes
0answers
560 views

How to get records from corrupted ibd file

Ok, in the last two days I thoroughly searched through the internet, and found multiple propositions on how to handle this, but nothing worked so far. Here is what the problem is and what I tried so ...
1
vote
0answers
513 views

Calculating the optimal size for the oracle undo tablepsace

To size the undo tablespace I query v$UNDOSTAT SELECT BEGIN_TIME, END_TIME, UNDOBLKS, MAXQUERYLEN from v$UNDOSTAT; BEGIN_TIME END_TIME UNDOBLKS MAXQUERYLEN ------------------- -...
1
vote
1answer
46 views

PostgreSQL server integrity after losing a tablespace

I'm running a PostgreSQL server with 2 tablespaces: 'ssd' and 'hdd'. The first contains databases that need to be queried quickly, and is located on a single SSD that is not redundant in any way (no ...
1
vote
0answers
154 views

No Tablespace found during dump import

Original Question: Oracle dump imported with zero rows I have exported the oracle dump from source by using exp username/password file=filepath/filname.dump I tried to import the exported dump ...
1
vote
2answers
169 views

Does export in schema mode also includes tablespaces?

I am executing an expdp command in schema mode. I'm just curious if it also exports tablespaces. I'm total newbie in oracle databases. What if I want to export schemas and tablespaces? What mode do I ...
0
votes
1answer
2k views

ORA-00959: Tablespace '$name' does not exist when using impdp

I'm new at Oracle databases. I'm executing an impdp command and encountered this error: ORA-00959: tablespace '$name' does not exist. Is the only thing I can do is to create the $name tablespace?
1
vote
1answer
2k views

How to create tablespace if it does not exist

When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does ...
2
votes
1answer
12k views

ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND on

I am getting an error: ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ But this is very strange to me because the tablespace has AUTOEXTEND on. What ...
0
votes
1answer
164 views

DB2 9.7 - how to release unused space back to the OS?

I am looking for a clear set of instructions on how to release unused space back to the operating system from a DB2 9.7 instance running on a Windows box. IBM's docs might be great if you already ...
1
vote
0answers
737 views

How not use temp tablespace

How to not use the temp tablespace in oracle? I have something like this: set timing on; create table export_table tablespace tbspace1 noparallel nomonitoring as select /*+ full(cs) full(soi_aux) ...
-1
votes
1answer
7k views

ORA-01555: Snapshot too old rollback segment with name “” too small error

I have created a trigger which works on two fields, first field(blob) and second field(varchar2). This trigger updates the blob when there is some changes in second field, from a blob value of another ...
0
votes
1answer
3k views

How to find what tablespace a table/index is in on PostgreSQL?

How do I find out what tablespace a table (or index) is in with PostgreSQL (9.3)?
1
vote
1answer
3k views

MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine

I have tried this statement to place a tables in different disks. Operating system is Linux Oracle 7, I've changed permissions in the directory with chmod -vR 777, to have total access to the disk ...
1
vote
0answers
127 views

How to recover LogMiner after its tablespace had been deleted?

Here is my story: I created separate table space for Log Miner, create tablespace logminer_ts datafile '/path/to/logminer.dbf' size 50M AUTOEXTEND ON And moved Log Miner to the created table space:...
3
votes
1answer
351 views

In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?

I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...