Shulou(Shulou.com)06/01 Report--

Oracle has been using it for some time to create users, assign permissions, and import initial data, so take the time today to sort out the SQL statements used in this process.

Step1: log in to sqlplus using the DBA user

C:\ Users\ Administrator > sqlplus dbaname/dbapassword@orcl as sysdba

Step2: query the current users of the database

SQL > select username from dab_users

Step3: if there is a user to be created, delete the user and the corresponding tablespace

Delete a user:

SQL > drop user username cascade

If the deletion is not successful, the user may be currently in use. Close the use or restart the database service and delete the user again.

Restart the database service:

SQL > shutdown abort

SQL > startup

Delete tablespaces:

SQL > drop tablespace tablespacename including contents and datafiles

Step4: creating tablespaces

When creating a tablespace, you can first query the storage address of the current data file, so that you can use it when creating a tablespace.

Query the address of the data file:

SQL > select file_name from dba_data_files

FILE_NAME

-

F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ USERS01.DBF

F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ UNDOTBS01.DB

F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSAUX01.DBF

F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ SYSTEM01.DBF

F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ EXAMPLE01.DB

F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ IPINMEI.DBF

Create a tablespace:

SQL > create tablespace tablespacename datafile'F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ datafilename.DBF' size 1g

Create a self-growing tablespace:

SQL > create tablespace tablespacename

SQL > logging

SQL > datafile'F:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ datafilename.DBF'

SQL > size 50m

SQL > autoextend on

SQL > next 5m maxsize 2048m

SQL > extent management local

Step5: create a user and specify a tablespace

SQL > create user username identified by userpassward default tablespace tablespacename

Step6: assign permissions to users

SQL > grant dba,connect,resource to username

Step7: after exiting sqlplus, import the dmp file

Imp username/password@orcl full=y file=d:\ dir\ * * .dmp log=d:\ dir\ * * .log tablespaces=tablespacename

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.