At the beginning of Oracle, a lot of unfamiliar, built the library, no users, and did not create table space, directly system users to build a table to add data, a few months past, the table built nearly hundred, the data added tens of thousands of, just more and more feel this way shortcomings too many:
- In PL/SQL, the system tables and user tables are displayed together, not a general disorder;
- User permissions are too large, but also bad control;
- There is no independent table space, late maintenance trouble.
Determined to change, the initial idea is to create a new database B, creating user and table space, the data in the original database A is passed, it is possible. Export no problem, import encountered a lot of problems. With Exp/imp, EXPDP/IMPDP have tried, but can not change owner and tablespace.
By expert guidance, only the final solution, everything in the original database operation can be.
1. CREATE TABLE space:
CREATE'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01. DBF'onNEXT 32M MAXSIZE 2048MEXTENT MANAGEMENT LOCAL;
2. Create user
CREATE USER Account name by password DEFAULT
3. Build a table
The basic statements for the build table are as follows:
1 Create Table as Select * from
But there are too many tables in the database, close to 100, one operation too cumbersome, the use of simple point method.
- Select the table you want to manipulate in PL/SQL, and right-click on "Copy comma seperated" to get a comma-separated list of table names
- The table name string is replaced with a regular tool, Regbuddy, in an instant, nearly hundreds of build table statements.
- Copy the replacement results to the PL/SQL command window to run, complete.
Although it is a small problem, but because I am not very familiar with Oracle, on the internet did not find the best solution, after the QQ group of netizens pointing to achieve the desired goal, but also made me understand Oracle deepened a little.
Changing the owner and tablespace of tables in Oracle