Questions tagged [tablespaces]
A database storage unit that groups related logical structures together.
170
questions
0
votes
1answer
23 views
How to quota a CDB only tablespace to a CDB user?
I can create a CDB tablespace like this: (all using: sqlplus / as sysdba)
-- create unique table space for admin
CREATE TABLESPACE admints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON;
I ...
1
vote
1answer
28 views
Creating table space steps [closed]
In order for me to create space in the database is this all I need to do?
alter tablespace USER_DATA_TBS add datafile '/oradata/bosv/user_data04.dbf' size 10m;
Or are there other steps I need to ...
0
votes
2answers
51 views
MySQL one file per table not working
I've followed all of the instructions for enabling the one file per table in MySQL (v8, on Ubuntu 20):
Added innodb_file_per_table=ON
Restarted MySQL
Dumped all databases, one by one.
However, when ...
2
votes
1answer
101 views
Missing mysql, sys, performance_schema
I have Percona Mysql 8.0.19-10 on debian 10.
A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database ...
0
votes
1answer
28 views
Get tablespace name from dump file
I have an Oracle dump file which I need to import but do not know what is its tablespace name. How can I get it from the dump file? I know that it is possible to get many information using impdp ...
0
votes
1answer
34 views
could not read symbolic link error while executing pg_start_backup function in Postgresql DB
In order to take data folder backup in postgres 9.6 database executed the order of functions for backup,
while executing pg_start_backup function produced warning : could not read symbolic link for ...
0
votes
0answers
23 views
How to get dependencies between tablespaces?
For example, we have tablespace1 and tablespace2.
The tablespace1 has the table called table1 owned by soe user.
The tablespace2 has a table called table2 owned by soe user, which has a foreign key ...
1
vote
2answers
37 views
How do I move tables and indexes owned by user to new tablespaces in Oracle 19C?
I have a user named SDE and the tablespace for this users tables are in SDE_TBS, I would like to move all of these tables to tablespace SDEBUS_DT. For indexes I would like to move to SDEBUS_IX. How ...
-1
votes
1answer
34 views
How to locate the tablespace objects that have grown suddenly?
In my Oracle 11g database, I have one of my tablespace that has grown suddenly during the night.
I would like to find the tablespace objects (e.g. tables or index) that have grown suddenly as occupied ...
0
votes
0answers
82 views
yet another Tablespace is missing
work0.psg.com:/var/db/mysql/wordpress# mysqldump -p --databases wordpress > dump.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all ...
0
votes
0answers
13 views
How to shutdown and startup a database to delete datafiles in between
OS: Windows 10 Professional / DBMS: Oracle 18c XE
How would one approach the issue of losing a connection while shutting down the database?
I would like to have one script which deletes datafilepaths ...
0
votes
1answer
74 views
Warning message: invalid value for parameter “default_tablespace” in postgres
I create a new tablespace using the following script:
CREATE SCHEMA semiarido;
CREATE ROLE semiarido PASSWORD 'senha';
GRANT CREATE ON SCHEMA semiarido TO semiarido;
GRANT USAGE ON SCHEMA semiarido ...
0
votes
1answer
99 views
Adding multiple files to a tablespace
In Oracle, we have huge amount of data imported daily, which requires us to create data files off and on in a tablespace.
Wonder if we create two data files simultaneously, will it use both when one ...
0
votes
1answer
60 views
Changing default permenant USERS tablespace to bigfile
I'm trying to build an oracle database server but it's setting up the users tablespace as a smallfile. How can I either install brand new database with users being bigfile or changing the existing ...
0
votes
1answer
111 views
Postgres use multiple tablespaces in a single database
Is it possible to allocate tables directly to tablespaces within a single database/schema in postgres? Based on the docs, tablespace is a create database argument, but are there any workarounds?
1
vote
1answer
104 views
Get tablespace of a given table in DB2
What is the command to get the name of tablespace of a given table in DB2 database ?
0
votes
2answers
69 views
ORACLE TABLESPACE USERS GETS 100% USED
I am facing a problem with oracles users tablespace. It automatically grows really fast. What kind of data is stored there? And what happens when it hits 100% of space used? Is there a solution to ...
0
votes
2answers
358 views
Postgres tablespace size management
I have a Postgres 10 database which is the model for an MVC app.
On it many (200,000) LOBS get saved, eventually processed, and stored or deleted depending on some conditions.
I have two problems.
...
4
votes
1answer
140 views
Continuously Adding SSDs to an existing PostgreSQL Server
An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to ...
3
votes
1answer
781 views
Can shrinking temporary TEMP tablespace in Oracle cause any harm?
I have a massive TEMP tablespace that is 1TB large. It consists of 30datafiles, each one is like 31GB in size.
Entire TEMP tablespace has a size of 1TB, while used size is only ~600MB.
I want to ...
0
votes
1answer
955 views
What is the default quota on a tablespace for a given user in oracle if you don't set one?
My environment
I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Explanation of the situation
I was creating a user with the following command
create user ...
0
votes
1answer
135 views
PostgreSQL: Table and index on different tablespace
In PostgreSQL i have the freedom of choosing to put index (of a table or materialized view) on tablespace other than the one i chose for the table (or materialized view) itself.
Is there any ...
-1
votes
1answer
107 views
ORACLE DMU (database migration assistant for unicode) Tool
Reading the documentation for Oracle DMU tool, when I try the step create a step for DMU Repository tablespace the document says to use the result of the query for initial size. However; I could not ...
0
votes
1answer
236 views
Oracle Tablespace not using 100% of available space
About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally. I check on the space available with this ...
1
vote
0answers
634 views
mysql - importing large Tablespace: Lost connection during query
iam trying to recover innodb table which has 1.5M rows from ibd file ( 5.5 GB )
this is the exact steps i do:
getting create table query using mysqlfrm command
create the table
Alter Table discard ...
0
votes
1answer
38 views
“Server not open” / “Drop tablespace” catch-22 situation
I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress".
Fair enough. The fix for this is as ...
0
votes
0answers
695 views
can delete manually a .ibd file and .frm file?
I have the following trouble in a mysql server database:
190919 17:35:30 [ERROR] Cannot find or open table abril2017/mexico from
the internal data dictionary of InnoDB though the .frm file for ...
1
vote
2answers
973 views
Free space of tablespace deleting table content (rows)
I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data.
ORA-01691: unable to extend lob segment RS2....
1
vote
1answer
454 views
ORA-01652: Extend a tablespace in Oracle 12c, no DBA_DATA_FILES table
I know that there are other questions about this topic but reading and trying them they cannot solve my problem. Mainly because select * from DBA_DATA_FILES; shows me an error when executing, because ...
3
votes
3answers
573 views
In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it?
We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers.
Question:
Is there a time where it would be correct to use 4K rather than 32K? If so, why? (...
0
votes
0answers
35 views
How to use tablespace
I am trying to implement Transperent Data Encryption. I am done with setting up the wallet and creating the tablespace.
My doubt is how to specify tablespace so that every object(tables/functions/...
0
votes
1answer
931 views
Checking space availability before a VACUUM FULL (postgres)
Is it possible to check how much re-usable space is available in a tablespace before running a VACUUM FULL on a large table?
I have a large postgres table (about 20G) that gets an occasional VACUUM ...
0
votes
1answer
25 views
During table migration of tables from one tablespace to another, does it needs free space to do the alter table move?
During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move?
exemple:. I have 30gb of free space on ...
0
votes
2answers
75 views
Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)
I've been trying to search for this info on oracle documents but they just don't talk about it.
I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (...
1
vote
2answers
5k views
Best practice for creating tablespaces in Postgresql
PostgreSQL version : 11.2
OS : RHEL or Oracle Linux 7.6 (Yet to be decided)
I am at the design stage of setting up a production database. In production, the DB will be around 300GB to 400GB in size.
...
0
votes
1answer
176 views
Oracle12c: Objects attempting to import to original tablespace (despite wanting to remap them)
I've exported a schema from a DB server (Oracle 12.2.0.1.0) using exp with the following command.
exp USERID=FOO/FOO2019 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.dmp" LOG=FOO.log
I now want to ...
0
votes
2answers
646 views
What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?
I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL?
Database pages are smallest read write unit ...
-1
votes
2answers
591 views
Creating Dictionary Management Tablespace Oracle [closed]
i am stuck at creating a dictionary management tablespace in 11g Release 2 Oracle 32 bit , I can't create a normal tablespace with dictionary management because my System tablespace is locally ...
1
vote
1answer
74 views
postgres pg_xlog explodes during set tablespace
I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and ...
0
votes
1answer
258 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 ...
4
votes
1answer
377 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 ...
1
vote
1answer
368 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
1answer
4k 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'...
2
votes
1answer
2k 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
476 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
5answers
4k 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 ...
4
votes
0answers
2k 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
265 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 ='...
13
votes
1answer
9k 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
7k 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&...
