oracle数据库基本操作

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_34406086

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量75

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/blogsyuanjian/archive/2012/07/11/2586185.html

1.安装好oracle数据库后，默认有三个账号，如下：

a. sys 用户最高管理权限

登陆方式：必须带上as sysoper 或者as sysdba :

例如：conn sys/123456 as sysoper ;conn sys/123456 as sysdba ;

用show user命令出现的为当前用户为“public”；

b.system 用户 dba

登陆方式：不必带上sysoper或者sysdba;

例如：conn system/123456 ; show user ,显示为：”system“;

conn system/123456 as sysdba ;show user ,显示为：”sys“;

C.scott 用户普通用户，一般安装后，都会有该用户

登陆方式和上面一样，但是有时会发现，该用房呗lock了。需要为它解锁。

例如：ALTER USER scott ACCOUNT UNLOCK

具体参考：http://caihorse.iteye.com/blog/633793 ；

2.sysdba ,sysoper.normal ;

在使用plsql登陆数据库是，登陆界面需要用户选择登陆connect as ;通常就是这三种方式。下面是个人了解情况：

sys and system are "real schemas", there is a user SYS and a user SYSTEM.

In general, unless the documentation tells you, you will NEVER LOG IN as sys or system, they are our internal data dictionary accounts and not for your use. You will be best served by forgetting they exist.

sysdba and sysoper are ROLES - they are not users, not schemas. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.

In real life, you hardly EVER need sysdba - typically only during an upgrade or patch.

sysoper is another role, if you connect as sysoper, you'll be in a schema "public" and will only be able to do things granted to public AND start/stop the database. sysoper is something you should use to startup and shutdown. You'll use sysoper much more often than sysdba.

do not grant sysdba to anyone unless and until you have absolutely verified they have the NEED for sysdba - the same with sysoper.

具体参考：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2659418700346202574

SYSDBA can do more than start/stop the database. It has a lot more functionality that sysoper (Operator mode) which is normally used for basic database functions such as start/stop.

SYSOPER privilege allows operations such as: Instance startup, mount & database open ;Instance shutdown, dismount & database close ;

Alter database BACKUP, ARCHIVE LOG, and RECOVER.This privilege allows the user to perform basic operational tasks without the ability to look at user data.

SYSDBA privilege includes all SYSOPER privileges plus full system privileges (with the ADMIN option), plus 'CREATE DATABASE' etc..This is effectively the same set of privileges available when previously connected INTERNAL.

具体参考:http://www.orafaq.com/maillist/oracle-l/2001/12/07/0435.htmv;

注：上面提到的角色的概念为：

参考：http://liuyf8688.iteye.com/blog/39505

3.启动oracle数据库：打开服务，启动实例和监听器。

4.更改用户密码：password username

5.运行一个sql文件，例如存放在D：/test.sql ,运行为：start D:/test.sql;

6.spool 命令为截屏命令；使用为：

->spool D：/test.sql

->select * from stuentinfo;

->spool off

包select出来的东西报错到test.sql 文件中。

7.linesize
->show linesize
->set linesize
8.pagesize
->show pagesize
->set pagesize