oracle数据库操作命令_oracle进入数据库命令-优快云博客

本文链接：https://blog.youkuaiyun.com/u010810431/article/details/52389367

本文提供了一系列Oracle数据库常用操作命令，包括登录数据库、创建表空间、用户管理、数据导入导出等，并介绍了如何查询当前连接数及数据库最大连接数，同时涉及了SQL锁表查询、表数据回滚和字符集修改等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录常用的oracle数据库操作命令：

1.登陆数据库：sqlplus sys/sys as sysdba;

2.创建表空间：create tablespace oa datafile 'D:\oracle\oradata\orcl\oa.dbf' size 200M autoextend on next 50m maxsize 20480m extent management local;

3.创建用户：create user oa identified by oa default tablespace oa;

4.用户授权：grant connect,dba to oa;

5.导入数据库：imp oa/oa@orcl file=e:\oa.dmp full=y;

6.导出数据库：exp oa/oa@orcl owner=oa file=e:\oa.dmp(导出orcl数据库oa用户下的所有数据和oa用户)

7.删除用户oa及数据库：drop user oa cascade;

8.删除表空间oa：DROP TABLESPACE oa INCLUDING CONTENTS AND DATAFILES;

9.当前的连接数：select count(*) from v$process

10.数据库允许的最大连接数：select value from v$parameter where name = 'processes'
11.修改最大连接数（需重启数据库）:alter system set processes = 500 scope = spfile;
12.查看当前有哪些用户正在使用数据：SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine from v$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc;

13.锁表查询SQL：SELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id　= o.object_id AND l.session_id = s.sid;
14.释放SESSION SQL:--alter system kill session 'sid, serial#'; ALTER system kill session '620, 719';
15.表数据回滚：select * from a_test as of timestamp to_timestamp('2018-04-02 13:34:50','yyyy-mm-dd hh24:mi:ss');
alter table a_test enable row movement;
flashback table a_test to timestamp to_timestamp('2018-04-02 11:00:00','yyyy-mm-dd HH24:MI:SS');

alter table a_test disable row movement;

16.修改oracle 字符集：https://blog.youkuaiyun.com/zhangxiaokang1/article/details/50730805