I found the solution to this. There is a temporary tablespace called TEMP which is used internally by database for operations like distinct, joins,etc. Since my query(which has 4 joins) fetches almost 50 million records the TEMP tablespace does not have that much space to occupy all data. Hence the query fails even though my … Read more
A data file that can hold data for one or more InnoDB tables and associated indexes. There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These are, a. System tablespace b. File per tablespace c. General tablespace System tablespace contains, InnoDB data dictionary. DoubleWrite Buffer. Change buffer Undo … Read more
I use this query column “Tablespace” format a13 column “Used MB” format 99,999,999 column “Free MB” format 99,999,999 column “Total MB” format 99,999,999 select fs.tablespace_name “Tablespace”, (df.totalspace – fs.freespace) “Used MB”, fs.freespace “Free MB”, df.totalspace “Total MB”, round(100 * (fs.freespace / df.totalspace)) “Pct. Free” from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by … Read more
Just add a new datafile for the existing tablespace ALTER TABLESPACE LEGAL_DATA ADD DATAFILE ‘/u01/oradata/userdata03.dbf’ SIZE 200M; To find out the location and size of your data files: SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = ‘LEGAL_DATA’;
You cannot insert data because you have a quota of 0 on the tablespace. To fix this, run ALTER USER <user> quota unlimited on <tablespace name>; or ALTER USER <user> quota 100M on <tablespace name>; as a DBA user (depending on how much space you need / want to grant).
A little late here but generally I’ve seen this problem occur when you get a ‘tablespace full’ error when running in a ‘innodb_file_per_table’ mode. Without going into too much detail (more here), the database server’s tablespace is defined by the innodb_data_file_path setting and by default is rather small. Even made larger, the ‘tablespace full’ can … Read more