Oracle 常用命令

启动部分：

手动启动oracle:

现在命令的模式下启动监听

lsnrctl start  

然后使用sqlplus来启动oracle

sqlplus / as sysdba

startup

shutdown immediate;

 

 

 

 

 

 

1、普通用户登录命令

$sqlplus chenchen/123456

 管理员用户登录

 

sqlplus "/ as sysdba"

 

2、切换用户

SQL>conn hr/123456 

3、查看oracle版本

SQL>select version from product_component_version where substr(product,1,6) = 'Oracle';

4、建表空间

SQL>create tablespace test datafile'/home/oracle/oraclefile/test.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local;

5、用dba账户新建用户，并同时指定用户空间

SQL>create user chenchen1 identified by 123456 default tablespace test;

 

grant dba to chenchen2;

grant connect,resource to chenchen2;

grant create session to chenchen2;

grant unlimited tablespace to chenchen2;

grant create table to chenchen2;

grant drop any table to chenchen2;

select * from user_sys_privs;//查看当前用户所有权限

　　select * from user_tab_privs;//查看所用用户对表的权限

 

 

 

 

6、导入数据的命令：

imp chenchen1/123456@127.0.0.1:1521/orcl file=/home/oracle/sd_det_0523_2010.dmp full=y

7、显示当前表空间中的所有表名：

select t.table_name,t.num_rows from user_tables t;

 

8、创建目录

CREATE
DIRECTORY dump_dir AS '/home/oracle/diretory/';

9、授权用户访问目录

SQL> grant read,write on directory dump_dir to chenchen1;

10、 查看创建的目录

select * from all_directories

 

 

11、查看日志组

select group#,thread#,members,archived,status from v$log;

 

12、

grant create any directory to chenchen1;

grant read any file group to chenchen1;

grant manage file group to chenchen1;

grant manage any file group to chenchen1;

 

drop user chenchen1 cascade;

 

 

 

13、示范create语句

create table usptotest
(
pn varchar(10) not null,
isd varchar(20) default '' ,
title varchar(150) default '' ,
abst varchar(2000) default '' ,
appno varchar(20) default '',
appdate varchar(20) default '' ,
inventor varchar(200) default '' ,
assignee_name varchar(50) default '',
assignee_country varchar(20) default '' ,
assignee_city varchar(20) default '' ,
assignee_state varchar(10) default '' ,
primary key (pn)
)

 

 

 

grant dba to chenchen;

select * from dba_directories

grant drop any table to chenchen;

 

 

 

14、打开关闭监听，查看监听状态

lsnrctl start 
会看到启动成功的界面;

lsnrctl stop 
停止监听器命令.

lsnrctl status 
查看监听器命令.

 

 

15、删表，表名要加引号

SQL> drop table taixing_ZHZS_0701_GSJGQBGXX;

drop table taixing_ZHZS_0701_GSJGQBGXX

           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

SQL> drop table "taixing_ZHZS_0701_GSJGQBGXX";   

 

Table dropped.

 

SQL> drop table "ningxiaSB_CWBB_XQYKJZZ_ZCFZB";

 

Table dropped.

 

SQL> 

 

 

3.1 设置utl_file_dir参数

SQL> alter system set utl_file_dir='/u01/app/oracle' scope=spfile;

System altered.

SQL> startup force;                  

SQL> show parameter utl_file

NAME                                 TYPE       VALUE

-------------------------------- ----------- ------------------------------

utl_file_dir                         string      /u01/app/oracle