Oracle creates users quickly, imp / exp imports and exports DMP files

Time:2021-3-5

1. First of all, we can use the administrator user to log in to Oracle as SYSDBA

sqlplus username/password as sysdba;

Then I can create users

create user username identified by password;

3. After creating the user, we can modify the user’s password

alter user username identified by password;

4. In general, we don’t use the user’s default table space, so we need to create a table space

1) By default, the table space is automatically expanded, and the size of each expansion is 100m, without limiting the maximum space
create tablespace tablespacename datafile 'f:\ts_zzg\zzg_data.dbf' size 2000M autoextend on next 100m maxsize unlimited; 

2) By default, the table space is automatically expanded, and the size of each expansion is 100m, and the maximum space is 5g. Note: the physical storage path of the table space is after the datafile, and the suffix of the file name can be arbitrary
create tablespace tablespacename datafile 'f:\ts_zzg\zzg_data.dbf' size 2000M autoextend on next 100m maxsize 5G;

5. Create a good table space, but also need to allocate table space to users

alter user zzg default tablespace ts_zzg;

6. Assign DBA rights to users

grant dba to zzg;

7. The table space is assigned to the user, and the user can’t log in (without login permission), so the user needs to be assigned permission

grant create session,create table,create view,create sequence,connect,resource,unlimited tablespace to zzg;

8. After the user is assigned permission, we can log in

conn username/password;

9. After logging in, we can also query the user’s permissions

select * from session_privs;

10. We can also delete users and their related objects, and disconnect the related links before deleting them

drop user username cascade;

11. Imp imports data, and the CMD command line executes the following imp script

imp cqyyt/123456 file=C:db.dmp file=C:db.log full =y;
imp username/123456 fromuser=fromusername touser=tousername file=F:\DEBT_QKJ_0731.dmp log=F:\DEBT_QKJ_0108.log ignore=y;

12. Exp export data, and execute the following command under the command line of CMD

exp gd_base/[email protected]/oanet file=D:\export\gd_base.dmp log=D:\export\gd_base.log owner=gd_base;
exp gd_base/[email protected] file=D:\export\gd_base.dmp log=D:\export\gd_base.log owner=gd_base;

Recommended Today

Rust and python: why rust can replace Python

In this guide, we compare the rust and python programming languages. We will discuss the applicable use cases in each case, review the advantages and disadvantages of using rust and python, and explain why rust might replace python. I will introduce the following: What is rust? What is Python? When to use rust When to […]