---
title: "Oracle: How to move a table to another schema?"
description: "Can I move a table to another schema in Oracle?.The quick answer is, \"not possible\". You have to rebuild it via \"create table as select\""
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [Oracle: How to move a table to another schema?](https://www.pythian.com/blog/oracle-how-to-move-a-table-to-another-schema)

 Written by [Christo Kutrovsky](https://www.pythian.com/blog/author/christo-kutrovsky) | Jul 21, 2006 4:00:00 AM

 

## Backstory

A client asked me, “How can I move a table to another schema in Oracle?” The quick answer I gave him is, “not possible”. You have to rebuild it via “create table as select”. You might ask, justifiably, why would you want to do that anyway? His problem was that the application has been split into 2 parts, and he wanted to have separate schemas for each part, to ensure that there is no cross-schema table access.

The way this should work is like this:

```
SQL> rename t1 to kutrovsky.t1;

ORA-01765: specifying table's owner name is not allowed
```

Oops! How could you do that without rebuilding the segments, I was wondering. And here’s what came up.

It’s not exactly `rename t1 to kutrovsky.t1;`, but it gets pretty close.

Let’s assume that `t1` is the table we want to move. For demonstration purposes, allow me to create a simple table `t1`:

```
create table t1 ( column1 ) as select rownum from user_tables where rownum <=10;
```

## First Step

The first step in our process is to create a range partitioned table, in the following example named `t1_temp`, based on the structure of our table. The name is of no importance, it is only temporary. We use any existing number, date or varchar2 column of our table for the partition key. The ranges also do not matter, since we’re not validating them.

```
create table t1_temp partition by range (column1)
(partition dummy values less than (-1),partition t1 values less than (MAXVALUE))
as select * from t1 where rownum <=0;
```

## Second Step

As the second step, we create the new table, which will hold the data. Note that we’re only creating the layout, no data.

```
create table kutrovsky.t1 as select * from t1 where rownum <=0;
```

## Third Step

And now here comes the magical third step:

```
alter table t1_temp exchange partition dummy with table t1 including indexes without validation;
alter table t1_temp exchange partition dummy with table kutrovsky.t1 including indexes without validation;
```

The first command “assigns” the data segment to the `t1_temp` table. The second command “assigns” the data segment to the `t1` table in the new owner.

Magic? I don’t think so. Here’s how and why it works.

## Result

When you create a normal table, Oracle creates two items. The logical object (`object_id`) and the data segment (`data_object_id`). When you create a partitioned table, Oracle creates 1 logical object (the table) and multiple data segments (each partition). In a partitioned table, the logical object (the table) has no physical segment, only it’s partitions have them.

Oracle has a command to re-assign physical objects (data segments) to compatible logical objects, but it only works between a partition of a table and a non-partitioned table. When you use that command in the two step process as shown above, you essentially re-assign between two tables, by using a partitioned table to work around the limitation of the exchange command.

I took three snapshots of our objects of interest.

1. Before executing any exchanges
2. After 1st exchange
3. After 2nd exchange

Notice how the `data_object_id` travels “down” and gets re-assigned.

| OWNER | OBJECT_NAME | SUBOBJ | OBJECT_ID | DATA_OBJECT_ID |
| --- | --- | --- | --- | --- |
| **Before any exchange operations** |  |  |  |  |
| BIG_SCHEMA | T1 |   | 673309 | **673309 <–** |
| BIG_SCHEMA | T1_TEMP | T1 | 673312 | 673312 |
| KUTROVSKY | T1 |   | 673313 | 673313 |
| `alter table t1_temp exchange partition dummy with table t1 including indexes without validation;` |  |  |  |  |
| BIG_SCHEMA | T1 |   | 673309 | 673312 |
| BIG_SCHEMA | T1_TEMP | T1 | 673312 | **673309 <–** |
| KUTROVSKY | T1 |   | 673313 | 673313 |
| `alter table t1_temp exchange partition dummy with table kutrovsky.t1 including indexes without validation;` |  |  |  |  |
| BIG_SCHEMA | T1 |   | 673309 | 673312 |
| BIG_SCHEMA | T1_TEMP | T1 | 673312 | 673313 |
| KUTROVSKY | T1 |   | 673313 | **673309 <–** |

## Context

This approach also works when the table has indexes. The little detail is that you have to create the same indexes on both the temp partitioned table (as local indexes!) and the final destination table.

Those of you who have been following along attentively will know that you can now drop the `t1_temp` table.

Happy moving!

## Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

 

 

[View full post](https://www.pythian.com/blog/oracle-how-to-move-a-table-to-another-schema)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Christo Kutrovsky"
  },
  "dateModified" : "2026-03-24T07:07:19.821Z",
  "datePublished" : "2006-07-21T04:00:00Z",
  "headline" : "Oracle: How to move a table to another schema?",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/oracle-how-to-move-a-table-to-another-schema",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```