{"id":4375,"date":"2025-06-27T04:40:01","date_gmt":"2025-06-27T04:40:01","guid":{"rendered":"https:\/\/w3buddy.com\/?p=4375"},"modified":"2026-01-15T12:44:09","modified_gmt":"2026-01-15T07:14:09","slug":"how-to-fix-ora-00959-tablespace-test_tbs-does-not-exist","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-fix-ora-00959-tablespace-test_tbs-does-not-exist\/","title":{"rendered":"How to Fix ORA-00959: Tablespace &#8216;TEST_TBS&#8217; Does Not Exist"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">If you&#8217;re working with Oracle and see this error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>ORA-00959: tablespace 'TEST_TBS' does not exist\n<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">It means you&#8217;re trying to use a tablespace that hasn\u2019t been created in your database yet. This error commonly appears during user creation, table creation, or while running SQL scripts that reference missing tablespaces.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s understand the causes, how to fix it, and how to avoid it in the future.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">When and Why This Error Happens<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Oracle shows this error when the name of the tablespace you are trying to use doesn\u2019t exist. Here are common situations where this occurs:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. <strong>Creating a Table with a Missing Tablespace<\/strong><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TABLE employees (\n  id NUMBER,\n  name VARCHAR2(100)\n) TABLESPACE TEST_TBS;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If <code>TEST_TBS<\/code> hasn\u2019t been created yet, Oracle throws the error.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2. <strong>Creating an Index in a Non-Existent Tablespace<\/strong><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE INDEX emp_id_idx \nON employees (id) \nTABLESPACE TEST_TBS;<\/code><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">3. <strong>Creating a User with a Default or Temporary Tablespace That Doesn\u2019t Exist<\/strong><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE USER test_user IDENTIFIED BY mypassword\nDEFAULT TABLESPACE TEST_TBS\nTEMPORARY TABLESPACE TEMP_TBS;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If <code>TEST_TBS<\/code> or <code>TEMP_TBS<\/code> doesn\u2019t exist, the error will occur.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">4. <strong>Running an Import or SQL Script<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Many SQL or schema dump files include tablespace references:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TABLE orders (\n  order_id NUMBER\n) TABLESPACE TEST_TBS;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If the destination database doesn\u2019t have <code>TEST_TBS<\/code>, it will fail.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 How to Fix ORA-00959<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Check if the Tablespace Exists<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Use the following SQL:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>SELECT tablespace_name \nFROM dba_tablespaces \nWHERE tablespace_name IN ('TEST_TBS', 'TEMP_TBS');<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If no rows are returned, the tablespaces are missing.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Create the Required Tablespace(s)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Create permanent tablespace<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TABLESPACE TEST_TBS \nDATAFILE '\/u01\/app\/oracle\/oradata\/ORCL\/test_tbs01.dbf' \nSIZE 100M \nAUTOEXTEND ON \nNEXT 10M \nMAXSIZE 1G;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Create temporary tablespace<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TEMPORARY TABLESPACE TEMP_TBS \nTEMPFILE '\/u01\/app\/oracle\/oradata\/ORCL\/temp_tbs01.dbf' \nSIZE 50M \nAUTOEXTEND ON;<\/code><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Modify the SQL If You Can\u2019t Create the Tablespace<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If you don\u2019t have permission to create tablespaces or want to keep things simple, update the SQL to use existing tablespaces.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example for table creation<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE TABLE employees (\n  id NUMBER,\n  name VARCHAR2(100)\n) TABLESPACE USERS;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example for user creation<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>CREATE USER test_user IDENTIFIED BY mypassword\nDEFAULT TABLESPACE USERS\nTEMPORARY TABLESPACE TEMP;<\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Or remove the <code>TABLESPACE<\/code> clause to use the default.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Tips to Avoid This Error<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u2705 Always check tablespace names before running scripts<\/li>\n\n\n\n<li>\u2705 Include tablespace creation commands in schema setup<\/li>\n\n\n\n<li>\u2705 Use environment-safe defaults like <code>USERS<\/code> and <code>TEMP<\/code><\/li>\n\n\n\n<li>\u2705 Avoid hardcoding tablespace names unless needed<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">For more tablespace commands and options, see:<br>\ud83d\udd17 <a href=\"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/oracle-tablespace-management\/\" target=\"_blank\" rel=\"noreferrer noopener\">Oracle Tablespace Management<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;re working with Oracle and see this error: It means you&#8217;re trying to use a tablespace that hasn\u2019t been created in your database yet. This error commonly appears during user creation, table creation, or while running SQL scripts that reference missing tablespaces. Let\u2019s understand the causes, how to fix it, and how to avoid [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-4375","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4375","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4375"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4375\/revisions"}],"predecessor-version":[{"id":4377,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4375\/revisions\/4377"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4375"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4375"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4375"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}