2 Replies Latest reply on Jun 24, 2008 8:43 AM by user629987

    Partitions in different database schema's?


      Is it possible to partition a table and put each of the partitions in different database schema's? If yes, could I get an example showing how to do this?
        • 1. Re: Partitions in different database schema's?
          Jack Raitto-Oracle
          I am not sure why you would want to do this. Partition migration across storage tiers is designed to work transparently. If you move a partition to a different schema, it will not be transparent -- the partition will appear to be missing in its original schema and it will appear in the schema where you place it. Queries that look for it in the original schema will not find it. It is not transparent.

          Conversely, if you migrate partitions within the same schema but to different tablespaces that reside in different datafiles that reside on different storage tiers, the effect is transparent: queries see no logical difference. But you can optimize your storage budget by placing the most used data on the fastest, more expense storage tier and the least used data on the slower, less expensive storage tier. And since it is transparent, you need not make any application changes whatsoever.

          There may be advantages to your proposal that I am not seeing. In any event, the ILM Assistant does not support migration of partitions across schemas.

          Thanks, Jack
          • 2. Re: Partitions in different database schema's?
            Thanks for the reply.

            Can you give me some links with information on partitioning across different schema's, when & why this would be done and the advantages/disadvantages of doing this? Thanks.