通过sql*plus工具操作ORACLE 常用命令



一、利用sql*plus工具登录ORACLE

sqlplus username/password  如：普通用户登录  sqlplus scott/tiger

sqlplus username/password@net_service_name 如: sqlplus scott/tiger@orcl

sqlplus  username/password as sysdba 如：sqlplus sys/admin as sysdba

sqlplus username/password@//host:port/sid 

sqlplus /nolog 进入sqlplus工具
conn / as sysdba  为超级系统管理员身份进行登录
connect EST_SZJC_IMF_AODB/password@127.0.0.1:1521/DEVELOP;

(注意：sys和system需要以sysdba登录)

在进入sql*plus之后，可以使用conn连接到其他用户，如：conn sys/admin as sysdba

二、创建表空间

CREATE TABLESPACE 表空间名称 LOGGING DATAFILE 'c:\表空间数据文件.ora' SIZE 500M autoextend on next 200m maxsize 2048m;

create tablespace SZJC_IMF_AODB logging datafile 'D:\Oracle\database\x64\11g\server\oradata\DEVELOP\SZJC_IMF_AODB.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
create temporary tablespace SZJC_IMF_AODB_TEMP tempfile 'D:\Oracle\database\x64\11g\server\oradata\DEVELOP\SZJC_IMF_AODB_TEMP.dbf' size 50m  autoextend on next 50m maxsize 20480m  extent management local;

DROP TABLESPACE SZJC_AODB INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SZJC_AODB_TEMP INCLUDING CONTENTS AND DATAFILES;

三、创建用户并授权表空间

CREATE USER TEST_SZJC_IMF_AODB PROFILE default IDENTIFIED BY password DEFAULT TABLESPACE SZJC_IMF_AODB TEMPORARY TABLESPACE SZJC_IMF_AODB_TEMP ACCOUNT UNLOCK;
grant connect,resource,dba to TEST_SZJC_IMF_AODB;
alter user TEST_SZJC_AODB identified by "12345678";
flush privileges;

drop user TEST_SZJC_AODB cascade;

四、导入DMP文件

imp TEST_SZJC_IMF_AODB/password@127.0.0.1:1521/DEVELOP fromuser=PFO touser=TEST_SZJC_IMF_AODB full=y file=D:\sz-imf\aodb_20160810.dmp ignore=y;

imp TEST_SZJC_IMF_AODB/password@127.0.0.1:1521/DEVELOP full=y file=D:\sz-imf\aodb_20160810.dmp ignore=y;