DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01543 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01543: tablespace already exists — Causes, Fixes & Prevention

What Is ORA-01543?

ORA-01543 is thrown by Oracle Database when you attempt to create a tablespace using a name that already exists within the database. Because Oracle enforces unique tablespace names at the database level, any duplicate CREATE TABLESPACE statement will immediately fail with this error. This most commonly occurs when initialization scripts are run more than once or when multiple DBAs work on the same environment without proper coordination.

Top 3 Causes

1. Running Setup Scripts More Than Once

The most frequent cause is re-executing a database setup or deployment script that lacks idempotency checks. If your script simply runs CREATE TABLESPACE without first verifying existence, the second run will always fail.

-- This will fail on the second run if tablespace already exists
CREATE TABLESPACE MY_APP_TBS
DATAFILE '/oradata/ORCL/my_app_tbs01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G;
-- ORA-01543: tablespace 'MY_APP_TBS' already exists
Enter fullscreen mode Exit fullscreen mode

2. Naming Conflicts Due to Missing Naming Conventions

Using generic names like DATA, INDEX, or USERS without checking what already exists in the database is a common pitfall. In shared or multi-team environments, another DBA may have already created a tablespace with the same name.

-- Always check before creating
SELECT tablespace_name, status, contents
FROM dba_tablespaces
WHERE tablespace_name = 'MY_APP_TBS';
Enter fullscreen mode Exit fullscreen mode

3. Failed DROP Followed by Immediate CREATE

When a DROP TABLESPACE command fails silently or is rolled back inside a script, the subsequent CREATE TABLESPACE will hit ORA-01543 because the tablespace still exists.

-- Incorrect approach: no validation between DROP and CREATE
DROP TABLESPACE MY_APP_TBS INCLUDING CONTENTS AND DATAFILES;
-- (If DROP fails silently, the next line will error)
CREATE TABLESPACE MY_APP_TBS
DATAFILE '/oradata/ORCL/my_app_tbs01.dbf' SIZE 200M;
-- ORA-01543: tablespace 'MY_APP_TBS' already exists
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 — Wrap CREATE in an existence check using PL/SQL:

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM dba_tablespaces
  WHERE tablespace_name = 'MY_APP_TBS';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE '
      CREATE TABLESPACE MY_APP_TBS
      DATAFILE ''/oradata/ORCL/my_app_tbs01.dbf'' SIZE 200M
      AUTOEXTEND ON NEXT 50M MAXSIZE 5G
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO';
    DBMS_OUTPUT.PUT_LINE('Tablespace created successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Tablespace already exists. Skipping creation.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Fix 2 — If you genuinely need to recreate it, validate the DROP first:

-- Drop completely (WARNING: all data will be permanently lost!)
DROP TABLESPACE MY_APP_TBS
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

-- Verify it is gone before recreating
SELECT COUNT(*) FROM dba_tablespaces
WHERE tablespace_name = 'MY_APP_TBS';
-- Must return 0

-- Now safely recreate
CREATE TABLESPACE MY_APP_TBS
DATAFILE '/oradata/ORCL/my_app_tbs01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Enter fullscreen mode Exit fullscreen mode

Fix 3 — Add a datafile instead of recreating if capacity is the concern:

-- Check current usage first
SELECT file_name, bytes/1024/1024 AS size_mb, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'MY_APP_TBS';

-- Extend by adding a new datafile
ALTER TABLESPACE MY_APP_TBS
ADD DATAFILE '/oradata/ORCL/my_app_tbs02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Write idempotent scripts. Always wrap CREATE TABLESPACE statements inside a PL/SQL existence check or use database migration tools like Liquibase or Flyway, which track and skip already-applied changes automatically.

  • Enforce a naming convention and change management process. Adopt a structured naming pattern such as [PROJECT]_[PURPOSE]_TBS (e.g., CRM_DATA_TBS, CRM_IDX_TBS) and require DBA approval before any new tablespace is created. Regularly audit DBA_TABLESPACES to keep the environment clean and documented.

Related Oracle Errors

  • ORA-00959 — Referenced tablespace does not exist; often seen as the counterpart when working with tablespace lifecycle management.
  • ORA-01119 — Error in creating database file; can accompany ORA-01543 when the datafile path already exists on disk even after a tablespace drop.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)