This article is suitable for UF NC, UAP series products. Other cases are for reference only.
Cut the crap and start getting to the point:
Steps:
--Create TABLE space
--data and index must be paired.
CREATE tablespace nnc_data01 datafile ' E:\ORACLE\...\nnc_data01.dbf ' SIZE 500M autoextend on NEXT 50M EXTENT MANAGEMENT LO CAL UNIFORM SIZE 256K;
CREATE tablespace nnc_data02 datafile ' E:\ORACLE\...\nnc_data02.dbf ' SIZE 300M autoextend on NEXT 50M EXTENT MANAGEMENT LO CAL UNIFORM SIZE 256K;
CREATE tablespace nnc_data03 datafile ' E:\ORACLE\...\nnc_data03.dbf ' SIZE 500M autoextend on NEXT 100M EXTENT MANAGEMENT L ocal UNIFORM SIZE 512K;
CREATE tablespace nnc_index01 datafile ' E:\ORACLE\...\nnc_index01.dbf ' SIZE 500M autoextend on NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE tablespace nnc_index02 datafile ' E:\ORACLE\...\nnc_index02.dbf ' SIZE 300M autoextend on NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE tablespace nnc_index03 datafile ' E:\ORACLE\...\nnc_index03.dbf ' SIZE 500M autoextend on NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
User:
--Create User
CREATE USER new identified by new DEFAULT tablespace Newtbs;
--User Authorized DBA
GRANT Connect,dba to new;
File storage directory:
--Build a physical location (manually create a new folder locally):
CREATE DIRECTORY dmp_dir as ' F:\dir ';--dmp_dir is the alias of the actual path
--To see if the directory was created successfully
SELECT * from Dba_directories;
--Grant users Read and Write permissions
Grant Read,write on directory Dmp_dir to new;
Export Users:
--EXPDP user/Password @ database directory= alias dumpfile= data file logfile= log;
--Example: EXPDP new/[email protected] Directory=dmp_dir dumpfile=new.dmp logfile=dmp_dir:new.log;
--Remote: EXPDP new/[email protected]:1521/orcl schemas=new directory=dmp_dir dumpfile =new.dmp logfile=new.log; (dmp_ Dir must be on a remote machine instead of local)
To import Users:
--IMPDP user/Password @ database directory= alias dumpfile= data file remap_schema= old User: New user remap_tablespace= old tablespace: New table Space full=y;
Example: IMPDP new/[email protected] Directory=dmp_dir dumpfile=new.dmp remap_schema=old:new Remap_tablespace=oldtbs:newtbs Full=y;
Then put some supplementary query code:
------------------------------------------
--Delete Directory
Drop directory Directory_name;
--View all users in the current database
Select username from dba_users
--Delete User
Drop userxxcascade;--deleted the user, just deleted the user under the schema objects, will not delete the corresponding tablespace.
--View Table space
SELECT * from Dba_tablespaces;
--View the relationship between the user and the default table space
Select Username,default_tablespace from Dba_users;
--View Tablespace file path
SELECT * from Dba_data_files order by file_id;
--Delete Table space
DROP tablespace tablespace_name including CONTENTS and datafiles;
--Change the default table space
Alter user hwpj_study default Tablespace nnc_data02;
Simple example of Oracle data pump Import and Export