Using transportable tablespaces for hosting data of multiple users

• US 6,549,901 B1
• Filed: 09/29/2000
• Issued: 04/15/2003
• Est. Priority Date: 05/30/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Loaiza, Juan R., Tsukerman, Alex, Lee, J. William, Putzolu, Gianfranco, Bridge, William H. Jr., Klein, Jonathan D., Rizvi, Hasan
• Primary Examiner(s): Kindred, Alford W.
• Application Number:
  US09/675,195
• Time in Patent Office: 928 Days
• Field of Search: 707/1-3, 707/7, 707/10, 707/200-206, 707/509, 707/513, 707/530, 707/100, 711/1.3, 711/202, 711/206, 711/209
• US Class Current: 1/1
• CPC Class Codes: G06F 16/9017 using directory or table look-up use of a directory or look-up table in file systems G06F16/13
• CPC Class Codes: Y10S 707/99937 Sorting

Abstract

Provided are mechanisms that may be used to support efficient exportation of user data stored in a database system. According to an aspect of the present invention, a database system is configured to store data for users in separate repositories referred to as tablespaces. To store data in this manner, a database system may be configured in variety of ways.

Claims

1. A method of managing data stored in a database system for a plurality of users, the

method comprising the steps of:

generating metadata that defines;

one or more tables spaces for said database system, and

an association between said one or more tablespaces and said plurality of users;

selecting which tablespaces to use to store data for each of user of said plurality of users based on said association between said one or more tablespaces and said plurality of users; and

exporting to another database system said data associated with a particular user, wherein the step of exporting includes generating a binary copy of a subset of one or more tablespaces of said one or more tablespaces, wherein said subset of one or more tablespaces were selected to store data for said particular user.

2. The method of claim 1, wherein said subset of one or more tablespaces is self-contained.

3. The method of claim 1, the method further including the step of causing said subset of one or more tablespaces to be self-contained after performing the step of storing data for said particular user in said subset of said one or more tablespaces.

4. The method of claim 1, wherein:

the step of generating metadata includes generating metadata that defines;

a table,

a plurality of partitions for storing records of said table, wherein each tablespace of said one or more tablespaces contains a partition from said plurality of partitions,

one or more partition keys associated with said table and said plurality of partitions, and

one or more criteria for selecting which partition of said plurality of partitions holds a subset of records in said table, wherein said one or more criteria are based on said one or more partition keys and one or more values associated with said plurality of users; and

the step of selecting which tablespaces to use includes the step of selecting which of said plurality of partitions should hold a particular record for said particular user based on said one or more criteria and at least one value associated with said particular user.

5. The method of claim 1, wherein:

the step of generating metadata includes generating metadata that defines;

a plurality of schemas and an association between each schema of said plurality of schemas and a user from said plurality of users, wherein each schema of said plurality of schemas includes a set of database schema objects,

a first schema belonging to said plurality of schemas that includes a first table and a first tablespace that holds data for said first table, and

a second schema belonging to said plurality of schemas that includes a second table and a second tablespace to hold data for said second table but not said first table; and

the step of selecting which tablespaces to use includes the step of selecting a schema for said particular user to access based on information identifying the user.

6. The method of claim 1, wherein said one or more tablespaces is a set of data files.

7. The method of claim 1, wherein the step of exporting includes

generating data from which said other database system may reconstruct metadata to incorporate said one or more tablespaces in said other database system.

8. A computer-readable medium carrying one or more sequences of instructions for managing data stored in a database system for a plurality of users, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

generating metadata that defines;

one or more tables spaces for said database system, and

an association between said one or more tablespaces and said plurality of users;

selecting which tablespaces to use to store data for each of user of said plurality of users based on said association between said one or more tablespaces and said plurality of users; and

exporting to another database system said data associated with a particular user, wherein the step of exporting includes generating a binary copy of a subset of one or more tablespaces of said one or more tablespaces, wherein said subset of one or more tablespaces were selected to store data for said particular user.

9. The computer-readable media of claim 8, wherein said subset of one or more tablespaces is self-contained.

10. The computer-readable media of claim 8, further including instructions for performing the step of causing said subset of one or more tablespaces to be self-contained after performing the step of storing data for said particular user in said subset of said one or more tablespaces.

11. The computer-readable media of claim 8, wherein:

the step of generating metadata includes generating metadata that defines;

a table,

a plurality of partitions for storing records of said table, wherein each tablespace of said one or more tablespaces contains a partition from said plurality of partitions,

one or more partition keys associated with said table and said plurality of partitions, and

one or more criteria for selecting which partition of said plurality of partitions holds a subset of records in said table, wherein said one or more criteria are based on said one or more partition keys and one or more values associated with said plurality of users; and

the step of selecting which tablespaces to use includes the step of selecting which of said plurality of partitions should hold a particular record for said particular user based on said one or more criteria and at least one value associated with said particular user.

12. The computer-readable media of claim 8, wherein:

the step of generating metadata includes generating metadata that defines;

a plurality of schemas and an association between each schema of said plurality of schemas and a user from said plurality of users, wherein each schema of said plurality of schemas includes a set of database schema objects,

a first schema belonging to said plurality of schemas that includes a first table and a first tablespace that holds data for said first table, and

a second schema belonging to said plurality of schemas that includes a second table and a second tablespace to hold data for said second table but not said first table; and

the step of selecting which tablespaces to use includes the step of selecting a schema for said particular user to access based on information identifying the user.

13. The computer-readable media of claim 9, wherein said one or more tablespaces is a set of data files.

14. The computer-readable media of claim 9, wherein the step of exporting includes generating data from which said other database system may reconstruct metadata to incorporate said one or more tablespaces in said other database system.

15. A computer system, comprising:

a database system that stores data for a plurality of users in one or more tablespaces;

said database system including metadata that defines an association between said one or more tablespaces and said plurality of users; and

said database system configured to select which tablespaces to use to store data for each user of said plurality of users based on said association between said one or more tablespaces and said plurality of users; and

said database system configured to export to another database system said data associated with a particular user by performing steps that include generating a binary copy of a subset of one or more tablespaces of said one or more tablespaces, wherein said subset of one or more tablespaces were selected to store data for said particular user.

16. The computer system of claim 15, wherein said subset of one or more tablespaces is self-contained.

17. The computer system of claim 15, wherein said database system is configured to cause said subset of one or more tablespaces to be self-contained after storing data for said particular user in said subset of said one or more tablespaces.

18. The computer system of claim 15, wherein:

said metadata that defines;

a table,

a plurality of partitions for storing records of said table, wherein each tablespace of said one or more tablespaces contains a partition from said plurality of partitions,

one or more partition keys associated with said table and said plurality of partitions, and

one or more criteria for selecting which partition of said plurality of partitions holds a subset of records in said table, wherein said one or more criteria are based on said one or more partition keys and one or more values associated with said plurality of users; and

said database system is configured to select which tablespaces to use by performing one or more steps that include selecting which of said plurality of partitions should hold a particular record for said particular user based on said one or more criteria and at least one value associated with said particular user.

19. The computer system of claim 15, wherein:

said metadata defines;

a plurality of schemas and an association between each schema of said plurality of schemas and a user from said plurality of users, wherein each schema of said plurality of schemas includes a set of database schema objects,

a first schema belonging to said plurality of schemas that includes a first table and a first tablespace that holds data for said first table, and

a second schema belonging to said plurality of schemas that includes a second table and a second tablespace to hold data for said second table but not said first table; and

said database system is configured to select which tablespaces to use by performing one or more steps that include selecting a schema for said particular user to access based on information identifying the user.

20. The computer system of claim 16, wherein said one or more tablespaces is a set of data files.

21. The computer system of claim 16, wherein said database system is configured to export to another database system said data associated with a particular user by performing steps that include generating data from which said other database system may reconstruct metadata to incorporate said one or more tablespaces in said other database system.

22. A method of plugging in a pluggable set of tablespaces having a plurality of disk pointers into a target database, comprising the computer-implemented steps of:

incorporating metadata into said target database, said metadata describing said tablespace and data therein;

copying each tablespace of said pluggable set of tablespaces into said target database; and

locating a data item associated with any one of said plurality of disk pointers without patching any disk pointer of said plurality of disk pointers.

23. The method of claim 22, wherein the step of incorporating metadata into said target database includes the step of executing commands in a file that specify instructions for generating said metadata, wherein said commands are generated by a source database for said pluggable set of tablespaces.

24. The method of claim 22, wherein the step of incorporating metadata includes:

examining said pluggable set of tablespaces to determine one or more attributes of said pluggable set of tablespaces to be defined by metadata; and

generating metadata based on said examination of said pluggable set of tablespaces.

25. The method of claim 22, wherein the step of incorporating metadata includes receiving user input that specifies instructions for generating said metadata.

26. The method of claim 22, wherein the step of incorporating metadata includes incorporating metadata that defines one or more columns for said pluggable set of tablespaces.