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 pg_tblspace Invalid argument*

test=# checkpoint; 
       CHECKPOINT

test=# select pg_switch_xlog();
 pg_switch_xlog
----------------
 5/DF000238
(1 row)
**
test=# select pg_start_backup(to_char(now(), 'yyyy-mm-dd hh24:mi:ss'));
WARNING:  could not read symbolic link "pg_tblspc/oth_data_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_index": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/tbs_temp_default": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/oth_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_ids": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/tbs_default": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/top_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/rpt_data_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_h_index": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/rpt_indx_tbs": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/str_data_h": Invalid argument
WARNING:  could not read symbolic link "pg_tblspc/top_data_tbs": Invalid argument
 pg_start_backup
-----------------
 5/E0000028
(1 row)

I'm working in Oracle 19c DB. Block size is 8k. I'm trying to insert new data in my table. I have enough free space (according to dba_free_space more than 2 GB). But I've got ORA-01654: unable to extend index by 8192. The question is why I need **8192*8k=64 MB** free space if **NEXT_EXTENT = 1048576 = 1MB**? And how to reuse my free space? I've checked that i need free extents 64 MB or above, byt I have only 1-3 MB extents: select count(*) as extents_qnt from dba_free_space where tablespace_name = 'MY_TABLESPACE' and bytes/1024/1024 >= 64 -- 64 MB EXTENTS_QNT ------- 0 select count(*) as extents_qnt from dba_free_space where tablespace_name = 'MY_TABLESPACE' and bytes/1024/1024 BETWEEN 1 AND 3 EXTENTS_QNT ------- 1642 I've got an error because of the lack of large enough free extents. But I still don't understand why I need 64 MB (not 1 MB).

I am concerned about the SYSTEM tablespace. It's space is 99% full. The whole tablespace has 1510 MB and there is 1507 MB already used. Max size of datafile is 32 GB and the autoincrement is on with increment 10 MB. However, I had outside company's audit and they pointed us it is unacceptable to keep so less free space in the system tablespace (3MB). As far as I see it is autoextensible, I am not sure if I should take any actions to resize the datafile (for example to 2 GB). I am not sure what kind of extents (DBA_EXTENTS) shall be storaged in this space, so at this moment I am not able to check if anything shall be moved from this space to SYSAUX or others. My question is, if it is really required to resize the system tablespace to avoid some problems with database due to its fullness? What would you do with this case? Maybe it would be enough to change incrementation size from 10 MB to more?

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 USERS tablespace to bigfile?

I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space. - Is it too much sorting occur on TEMP? - How do I identify such issue from AWR? - And how usually it can be resolved? My Oracle Database instance is running on version 18c. I am a DBA who mainly manages the operational side of the DB. I don't do much development / code writing. I would like to understand more, so I could provide some advice to the developers.

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;
CREATE DATABASE
postgres=> \l+
                                                                            List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |          Access privileges          |   Size    | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-------------------------------------+-----------+------------+--------------------------------------------
 postgres  | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                                     | 7455 kB   | pg_default | default administrative connection database
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin               | No Access | pg_default |
 rich1     | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                                     | 7233 kB   | pg_default |
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin                         | 7345 kB   | pg_default | unmodifiable empty database
                                                             : rdsadmin=CTc/rdsadmin
 template1 | pgsys    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pgsys                            | 7345 kB   | pg_default | default template for new databases
                                                             : pgsys=CTc/pgsys
(5 rows)

As you can see, the database is put into the pg_default tablespace, but if I specify the default tablespace in the tablespace clause (also still logged in as **pgsys**) I get a permission error:

postgres=> create database rich2 tablespace pg_default;
ERROR:  permission denied for tablespace pg_default

Here's the permissions for that user:

postgres=> \du pgsys
               List of roles
 Role name | Attributes  |    Member of
-----------+-------------+-----------------
 pgsys     | Create role | {rds_superuser}
           : Create DB

This is PostgreSQL error, but I should mention that this an AWS Aurora instance in case that makes a difference.

I am new to Oracle, and I am not quite understanding the size calculations shown in OEM under tablespaces. I know it should be simple and my well be. I need to monitor the table space but not sure what I am looking at. Should I go after the "Available Space Used(%)" or the "Allocated Space Used(%)" to monitor? If I am running a script to alert when a threshold is met, which % should I use?

I'm new in Oracle, so maybe my question could be stupid. I use Oracle only for data storage. I have made some research but I'm blocked. I use Oracle 12c. I created a PDB with admin user PEEI_SYS like this: create pluggable database PEEI admin user PEEI_SYS identified by PEEI roles = (DBA); -- open PDB PEEI alter pluggable database PEEI open read write;` I have created another user called PEEI which should only do select, update, insert on tables owned by PEEI_SYS. I have created the user PEEI like this: CREATE USER "PEEI" IDENTIFIED BY "PEEI" DEFAULT TABLESPACE PDATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;` Now I would like that the user PEEI could insert rows in the table PEEI_SYS.PEEI_P_TRACKING. This table is created like this: `CREATE TABLE PEEI_SYS.PEEI_P_TRACKING ( "CODE_WORKFLOW" VARCHAR2(30 BYTE), "STATUS" VARCHAR2(15 BYTE), "DATE_UPDATE" DATE, "USER_UPDATE" VARCHAR2(20 BYTE), "DEB_WORKFLOW" DATE, "FIN_WORKFLOW" DATE, "TIME_SECOND" NUMBER ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PDATA" ; GRANT SELECT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_READ; GRANT DELETE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE; GRANT INSERT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE; GRANT UPDATE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE; When I got the error I granted unlimited privileges to PEEI user on PDATA tablespaces like this: ALTER USER PEEI QUOTA UNLIMITED ON PDATA; I have still the error. Could you please help me ? Thank you very much in advance. Kind regards,

I'm trying to import a database dump, however I am encountering this error: ORA-39083: Object type TABLE:"MYSCHEMA"."MINF" failed to create with error: ORA-00959: tablespace '_$deleted$5$0' does not exist Failing sql is: CREATE TABLE "MYSCHEMA"."MINF" ( "P_MID" VARCHAR2(16 BYTE) NOT NULL ENABLE , "XML_ORIG_MSG" "SYS"."XMLTYPE" , "XML_MSG" "SYS"."XMLTYPE" , "P_OFFICE" CHAR(3 BYTE) NOT NULL ENABLE , "P_DEPARTMENT" CHAR(3 BYTE) DEFAULT '...' , "P_MSG_TYPE" VARCHAR2(15 BYTE) DEFAULT '...' , "P_MSG_SUB_TYPE" VARCHAR2(10 BYTE) , "P_CDT_MOP" VARCHAR2(6 BYTE) , "P_PREFERRED_CDT_MOP" VARCHAR2(6 It's seems the tablespace for the MINF table has been dropped before getting the dump. Is there a workaround to still import this table? Update: I have tried impdp statement, with the same result: ./impdp system/****** DIRECTORY=GPPDUMP DUMPFILE=SPOCBC_SCD02.ROCKY.20120919102739.dmp.1 ,SPOCBC_SCD02.ROCKY.20120919102739.dmp.2 ,SPOCBC_SCD02.ROCKY.20120919102739.dmp.3 ,SPOCBC_SCD02.ROCKY.20120919102739.dmp.4 LOGFILE=baru.log REMAP_SCHEMA=SPOCBC_SCD02:OCBCGPP5 REMAP_TABLESPACE=_$deleted$5$0:P_DATA

Some time ago after an update of Ubuntu 20.10, I discovered that MySQL was not running. When I tried to restart it, the error message suggested that it had started, failed and stopped. A check of the error log contained "InnoDB Multiple files found for the same tablespace ID" and listed 25 such instances, for example Tablespace ID: 92 = ['mercury/pickupcity.ibd', 'mycity/wp_mycity_eventlog.ibd'] How do I correct InnoDB Multiple files found for the same tablespace ID?

I deleted a tablespace that I no longer needed, and I accidentally left out the SQL query to delete the files. The tablespace has been deleted but the DFB has been left in the operating system path. I was wondering what the best way to delete it would be, since it is taking up unnecessary space. I have not dared to execute a rm -f file.dbf lsof does not indicate that any process is using it, but I think it would be better to do it from the DB itself if possible. System: RHEL4 Oracle Database: 10.2 Thanks!

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 up and working and forgot it. Fast forward to now, I just did a backup up my whole data-dir using percona's xtrabackup and transferred it to a new server. Copy the backup into my data-dir and attempt to start mysql. Fails with Tablespace 1, name 'sys/sys_config', file './sys/sys_config.ibd' is missing! mysqld: Can't create/write to file './performance_schema/cond_instances_2201.sdi' (OS errno 2 - No such file or directory) mysqld: Can't create file './performance_schema/cond_instances_2201.sdi' (errno: 2 - No such file or directory) I look at the the data-dir (/var/lib/mysql) and while I notice my main database folder, I don't see a sys or performance_schema folder. Curiously I do see a mysql folder but its full of files called slow_log and general_log. My first thought is the backup failed so I checked the old server and it has the exact same thing! However, I do have a mysql.ibd on the old server. So I run show databases and it shows all 3 databases (sys, performance_schema, mysql). I try to go to sys/sysconfig and it complains about the table space but I am able to go into mysql database tables and perofrmance_schema tables. Something is definitely up and I read people suggest doing an upgrade and mysql can self correct missing main tables (mysql_upgrade is deprecated so I must use mysqld and force it). I am scared to run update on my current server so I go back to the new server (the one I attempted to restore from backup) I rename /var/lib/mysql/mysql to /var/lib/mysql/backup and execute sudo -u mysql mysqld --upgrade=FORCE --skip-grant-tables --skip-networking and get in the error log /usr/sbin/mysqld (mysqld 8.0.21-12) starting as process 4000 2020-12-13T23:41:31.040574Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-12-13T23:41:31.279802Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2020-12-13T23:41:31.281181Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci' failed with error code = 1049, error message = 'System schema directory does not exist.'. 2020-12-13T23:41:31.281565Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2020-12-13T23:41:31.281986Z 0 [ERROR] [MY-010119] [Server] Aborting 2020-12-13T23:41:32.579767Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-12) Percona Server (GPL), Release '12', Revision '7ddfdfe'. So I am not sure what to do, I would prefer to get my new server up and running instead of messing with my old server because it's currently live and being used. I have innodb table per file Should I expect to see a mysql folder inside my data-dir that looks to be slow_log and general_log backups? Will the sys,performance_schma,mysql databases have their own folder in the data-dir? I wish there was a way to tell mysql to disregard the sys,mysql,performance_schema tables and re-create them, I thought that's what the upgrade option would do but it fails **Edit 12/23/2020** I think its problem that only shows up with the newest mysql. The backup was done on Percona Mysql 8.0.19-10 and the failed restore was attempted on Percona Mysql 8.0.21-12. Normally I wouldn't think it was the difference in versions... However, I just happen to upgrade the percona mysql to 8.0.22-13 on the the one that was previously 8.0.19-10 and the exact same issue occured. I believe the newer mysql version is more picky and fails to load if sys_config.ibd is missing where as 8.0.19 had no issues **Edit 12/24/2020** I uninstalled percona 8.0.21-12 on the target server and installed 8.0.19-10. Restored the backup with no issues (despite missing sys folder, etc). While the problem of missing a sys folder is still an issue it seems to only prevent startup with 8.0.2x

Our application is inserting and updating records into a DB2 database. It's been running for about 3 years, and we encountered an error SQLcode -960 SQLState=57011 SQLERRMC=IS_SMALL_TS The tablespace details are as follows: Tablespace ID = 5 Name = IS_SMALL_TS Tуре =- Database managed space Contents = All permanent data. Large table space. State = 0x0000 Detailed explanation: Normal Total pages = 894976 Number of useable pages = 894972 Number of used pages = 893844 Number of free pages =1128 High water mark (pages) =893844 Page size (bytes) =16384 Extent size (pages) =4 Prefetch size (pages) =4 Number of containers =1 The Db2level info is as follows: D: \IBM\SQLLIB\BIN>db2level DB21085I This instance or install (instance name, where applicable: "DB2") uses "64" bits and DB2 code release "SQL11014" with level identifier 0205010F. Informational tokens are "DB2 v11.1.4050.859", "51911120160", DYN1911120100WIN64 , and Fix Pack "5". Product is installed at "D:\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1". We read through IBM Manual and tried a few thing including Alter tablespace IS_SMALL_IS increase 30 percent but don't see any changes. What can we do?

I'm trying to upgrade a Postgres cluster from PG 12 to PG 15 using this command :

sudo pg_upgradecluster 12 main --method=link

My cluster uses some tablespaces:

postgres=# SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace;
    oid     |      spcname       |      pg_tablespace_location      
------------+--------------------+----------------------------------
       1664 | pg_global          | 
       1663 | default_old        | 
 1905123481 | ts1                | /mnt/postgres/ts1
 1905522771 | ts2                | /mnt/postgres/ts2
 1905595436 | ts3                | /mnt/postgres/ts3

When I run the pg_upgradecluster script, it fails immediately with this error :

tablespace directory "" does not exist

All the tablespaces are located on different disks I've already upgraded another cluster which doesn't have tablespaces from PG 12 to PG 15 without any errors. I don't understand what to do now :( does anyone faced this issue before and knows how to fix it ?? Thanks in advance

I'm looking for ways to improve my database performance for heavy analytical queries and found that my temp tablespace has 1M extents (default for uniform tablespace). With my usual temp tablespace usage far above 1GB per query (top queries with heavy joins/sorts/groups can consume up to 100 GB of temp tablespace) 1M extents looks too low for me. Is it worth increasing extent size for performance?

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? (Does it perform better when it can be used?) Or, is it just a legacy appendage from some prehistoric time when 4K was just the page size? Background: I have always just created a 4K, 8K, 16K, & 32K tablespaces and associated bufferpools when I create DB2 db's. My manager is challenging me on this. (Good for him - I should know this!) He thinks we should just create a 32K tablespace and be done with it. I'm not able to find anything that tells me, for instance, that when the row size allows it, we should use 4K rather than 32K because XYZ. It tells me I *CAN* do this, but not that/when I should.

I was cleaning up tablespaces and wanted to delete one, and after moving all the segments from it, I got the following:

SCOTT.OE> drop tablespace fact_index including contents and datafiles;
drop tablespace fact_index including contents and datafiles
*
ERROR at line 1:
ORA-14405: partitioned index contains partitions in a different tablespace

I tried looking for where all the schemas had data in all tablespaces. No luck.

I have the following case: - the master database has an additional tablespace - I'm trying to replicate the master with the pg_basebackup tool - the directory / partition layout on the slave is the same as on the master What parameters should I use to set up replication safely? Do I need to map tablespace with **-T** parameter or is it only required if I am doing pg_basebackup on the master? pg_basebackup is generaly safe? Data not be deleted from the master? My command is: db_slave# pg_basebackup -h db_master -p 5433 -U replication -D /var/lib/postgresql/12/main -P -Xs -Fp -R But I get: pg_basebackup: error: directory "/home/db" exists but is not empty pg_basebackup: removing data directory "/var/lib/postgresql/12/main" Tablespace is store in */home/db*. Thanks a lot for all answers.

I am unable to create new tablespaces on a fresh Postgres 15.3 installation on Ubuntu 22.04. I tried the following so far: - re-install Postgres - fully loosen file system permission - disabled SELinux But still the postgres user reports it cannot set permissions on the tablespace folder. The log is below.

$ mkdir -p /media/user/Work/Databases/geo

$ chown -R postgres:postgres /media/user/Work/Databases

$ sudo chmod -R 777 /media/user/Work/Databases

$ setenforce 0
setenforce: SELinux is disabled

$ sestatus
SELinux status:                 disabled

$ sudo su postgres
postgres@luis-workstation:/home/user$ psql
could not change directory to "/home/user": Permission denied
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# CREATE TABLESPACE geo
  OWNER postgres
  LOCATION '/media/user/Work/Databases/geo';
ERROR:  could not set permissions on directory "/media/user/Work/Databases/geo": Permission denied

I need to create a table for caching in the postgresql database. Transactions should be as fast as possible, so i thought of creating unlogged table and have a RAM disk as it's tablespace. There is a warning in the official postgresql documentation : > Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster. I know that unlogged tables are not replicate to the standby and it is okay for me to store unpersistent data in that table. So in case of crash i'm fine with losing it. Here is a questions: 1. Is it safe after all to place a tablespace on the RAM disk in this configuration? (unlogged + RAM disk tablespace + unpersistent data) 2. If it is a safe option should I create ram disk on a replica too, since creation of tablespace will be stored somewhere in wal? I'm using EDB 13.9.13 with streaming replication. If this will workout I will do the same for Postgresql v14.7