47
votes
Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?
Here's the trick, at least with pgAdmin v4:
Leave the tablespace blank. It will default to "pg_default" when creating the database.
- 571
11
votes
Accepted
Oracle shrinking / reclaiming free tablespace space
I agree with EdStevens' comment, but here are some other ideas:
The AskTom website has a script that will list exactly how much you can shrink the existing data files.
If that doesn't yield much ...
- 1,143
7
votes
Accepted
Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2
The SYSTEM and SYSAUX tablespaces are reuqired by the RDBMS to function correctly.
The SYSAUX tablespace
A list of objects that reside in the SYSAUX tablespace can be retrieved using the V$...
- 18.3k
5
votes
Accepted
ORA-00959: tablespace does not exist but I can see it in select
While creating a common user, any default tablespace, temporary tablespace, or profile specified using the following clauses must exist in all the containers belonging to the CDB:
DEFAULT ...
- 8,650
5
votes
Oracle shrinking / reclaiming free tablespace space
I think there are five methods to deal with this oracle maintenance issue:
Online reorg - Oracle online table reorganization package, (dbms_redefinition) is used to reorganize tables while they ...
- 1,038
5
votes
Accepted
Checking space availability before a VACUUM FULL (postgres)
Firstly, I would suggest using pgstattuple to obtain tuple-level statistics.
pgstattuple returns a relation's physical length, percentage of “dead”
tuples, and other info. This may help users to ...
- 2,002
5
votes
Accepted
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?
It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors ...
- 26.9k
5
votes
mysqldump with --no-tablespaces on InnoDB with index?
"Tablespaces" are for clumping tables into a single .ibd. The mysqldump options relating to that control whether to output the DDL statements such as CREATE TABLESPACE.
mysqldump does not ...
- 79.4k
4
votes
Accepted
Get tablespace of a given table in DB2
On Db2 for LUW you can use this query. You get one row per table data partition (1 row for a non range partitioned table).
SELECT
SD.TBSPACE AS DATA_SPACE
, SL.TBSPACE AS LONG_SPACE
, SI....
- 778
4
votes
Accepted
What permissions are needed to prevent ERROR: permission denied for pg_tablespace_size('pg_global') in RDS?
At this time I've found no way to directly grant the necessary CREATE permission on the table-space to any user. For whatever RDS doesn't allow you to grant that directly.
However the permission has ...
- 161
3
votes
Accepted
Specificness of Tablespace
Despite being answered 10 years ago, I don't think I could improve on Justin Cave's very applicable answer from this similar question on SO: Should an Oracle database have more than one tablespace for ...
- 1,153
3
votes
Accepted
Oracle: Not able to create tablespace from stored procedure
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within ...
- 8,650
3
votes
SYSTEM tablespace getting filled up
First of all, make sure that SYSTEM tablespace is not assigned as default or temporary tablespace for normal database users.
Second of all, one of the space consuming table in SYSTEM tablespace is ...
- 8,650
3
votes
What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?
What is the relation between MySQL pages and .frm & .ibd files? Is .frm files consists of many pages coming together?
.frm used to contain metadata about tables- that means, the structure (...
- 15k
3
votes
Best practice for creating tablespaces in Postgresql
Tablespaces in PostgreSQL exist for some really particular needs (and I doubt a less than 500 GB is in that case) and for SQL compliance.
If you plan to create tablespaces to store eventually ...
- 1,590
3
votes
Continuously Adding SSDs to an existing PostgreSQL Server
Have you turned on TimescaleDB's native compression, available since v1.5? I ask because you mention ZFS/BTRFS above, which suggests you aren't employing its compression already.
Typically see 90-98% ...
- 346
3
votes
Accepted
How do I correct InnoDB Multiple files found for the same tablespace ID?
After reading up on what a tablespace was, it became clear that they were tables in the databases. I noticed that one database recurred in each of the 25 lines, whether it was first in the order or ...
- 141
2
votes
Balancing the size of oracle datafiles for a tablespace
An alternative (and simpler) solution, if you don't mind moving all your data to the new partition:
Drop the new datafile.
Move the old datafile to the new partition.
Optional: Create a second ...
- 821
2
votes
Accepted
Balancing the size of oracle datafiles for a tablespace
If a tablespace has multiple datafiles, Oracle manages what goes into what datafile. You have no control over this. If you have enough diskspace to do so, I suggest the following data-juggling-...
- 821
2
votes
Accepted
How to create tablespace if it does not exist
The point here is that Postgres functions are almost but not quite like true stored procedures. Postgres functions (unlike stored procedures) run in the context of an outer transaction. Therefore, you ...
- 182k
2
votes
In PostgreSQL, can you read from an index during a ALTER INDEX ... SET TABLESPACE operation?
Try to recreate the index concurrently, so the original one is still used while creating the new.
CREATE INDEX CONCURRENTLY idx_name
ON table_name
USING BTREE(col_name)
TABLESPACE new_tblspace
- 121
2
votes
Accepted
ORA-01658 on index tablespace when trying to insert into empty table of new database
Oracle docs note this about ORA-01658:
ORA-01658: unable to create INITIAL extent for segment in tablespace string
Cause: Failed to find sufficient contiguous space to allocate INITIAL extent ...
- 18.3k
2
votes
Accepted
How does PostgreSQL decide what to name its tablespace directories?
I look at the source code of postgres9.6.
/* tablespace.c */
create_tablespace_directories(const char *location, const Oid tablespaceoid)
{
....
location_with_version_dir = psprintf("%s/%s", ...
- 890
2
votes
Accepted
ORA-01536: Out of space error when doing CTAS
The user A must be running out of quota(or you might have forgotten to assign quota) on assigned tablespace.
The error seems to be misleading and I suspect something else is the problem here, ...
- 8,650
2
votes
Moving postgresql data to different drive
Here is a description of how to move an existing PostgreSQL database to a new location (folder, partition), on Arch Linux (this procedure should be similar for other Linux distributions).
pg_dumpall ...
- 181
2
votes
Accepted
DBA_TABLESPACE_USAGE_METRICS is empty
Typically caused by:
Bug 18921384 - DBA_TABLESPACE_USAGE_METRICS VIEW DOES NOT RETURN ROWS WHEN DB_FILES LIMIT IS REACHED
To verify:
show parameter db_files
select count(*) from v$datafile;
Then ...
- 41.1k
2
votes
Oracle writes whole file during database creation is it ok?
If you are able to create this tablespace(logical entity of Oracle database to hold user objects) then you already have created the database.
Yes, this is expected. The statement defines a datafile ...
- 8,650
2
votes
Oracle shrinking / reclaiming free tablespace space
I searched for an answer to this and finally found it:
(optionally) remove unused (dropped) tables from tablespace: PURGE TABLESPACE table_space_name;
clean the tablespace: ALTER TABLESPACE ...
- 215
2
votes
Accepted
Inconsistency between maxbytes & bytes. Oracle
MAXBYTES is 0 for datafiles that are not autoextensible, but BYTES is greater than 0. Use this:
Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (decode(d.maxbytes, 0, d....
- 41.1k
Only top scored, non community-wiki answers of a minimum length are eligible