Oracle 常用SQL命令

博主： Tehran
发布时间：2021 年 07 月 18 日
500 次浏览
暂无评论
14363字数
分类： Oracle

启动和关闭方式

一、Oracle数据库几种启动方式

1、startup nomount

　　非安装启动，这种方式下启动可执行：重建控制文件、重建数据库，读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。

2、startup mount (dbname)

　　安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机、重新定位数据文件、重做日志文件。

　　执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。

3、startup open (dbname)

　　先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，这种方式下可访问数据库中的数据。

4、startup 等于以下三个命令

　　startup nomount

　　alter database mount

　　alter database open

5、startup restrict

　　约束方式启动，这种方式能够启动数据库，但只允许具有一定特权的用户访问，非特权用户访问时，会出现以下提示：

　　ERROR：ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用

6、startup force

　　强制启动方式，当不能关闭数据库时，可以用startup force来完成数据库的关闭，先关闭数据库，再执行正常启动数据库命令

7、startup pfile=参数文件名

　　带初始化参数文件的启动方式，先读取参数文件，再按参数文件中的设置启动数据库

　　例：startup pfile=E:\Oracle\admin\oradb\pfile\init.ora

8、startup EXCLUSIVE

　　独占和共享启动

二、Oracle三种关闭方式

1、shutdown normal
　　正常方式关闭数据库

2、shutdown immediate

　　立即方式关闭数据库，在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），当使用

　　shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。

3、shutdown abort

　　直接关闭数据库，正在访问数据库的会话会被突然终止，如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。

三、详解

　　Oracle数据库提供了几种不同的数据库启动和关闭方式，本文将具体介绍这些启动和关闭方式之间的区别以及它们各自不同的功能。

　　一、启动和关闭Oracle数据库

　　　　对于大多数Oracle DBA来说，启动和关闭Oracle数据库最常用的方式就是在命令行方式下的Server Manager。从Oracle 8i以后，系统将Server Manager的所有功能都

　　集中到了SQLPlus中，也就是说从8i以后对于数据库的启动和关闭可以直接通过SQLPlus来完成，而不再另外需要Server Manager，但系统为了保持向下兼容，依旧保留了

　　Server Manager工具。另外也可通过图形用户工具（GUI）的Oracle Enterprise Manager来完成系统的启动和关闭，图形用户界面Instance Manager非常简单，这里不再

　　详述。要启动和关闭数据库，必须要以具有Oracle 治理员权限的用户登陆，通常也就是以具有SYSDBA权限的用户登陆。一般我们常用INTERNAL用户来启动和关闭数据库

　　（INTERNAL用户实际上是SYS用户以SYSDBA连接的同义词）。Oracle数据库的新版本将逐步淘汰INTERNAL这个内部用户，所以我们最好还是设置DBA用户具有SYSDBA

　　权限。

　　二、数据库的启动（STARTUP）

　　启动一个数据库需要三个步骤：

　　1、创建一个Oracle实例（非安装阶段）

　　2、由实例安装数据库（安装阶段）

　　3、打开数据库（打开阶段）

　　在Startup命令中，可以通过不同的选项来控制数据库的不同启动步骤。

　　1、STARTUP NOMOUNT

　　NONOUNT选项仅仅创建一个Oracle实例。读取init.ora初始化参数文件、启动后台进程、初始化系统全局区（SGA）。Init.ora 文件定义了实例的配置，包括内存结构的大小

　　和启动后台进程的数量和类型等。实例名根据Oracle_SID设置，不一定要与打开的数据库名称相同。当实例打开后，系统将显示一个SGA内存结构和大小的列表，如下所示：

　　SQL> startup nomount
　　　　ORACLE 例程已经启动。
　　　　Total System Global Area 35431692 bytes
　　　　Fixed Size 70924 bytes
　　　　Variable Size 18505728 bytes
　　　　Database Buffers 16777216 bytes
　　　　Redo Buffers 77824 bytes

　　2、STARTUP MOUNT

　　该命令创建实例并且安装数据库，但没有打开数据库。Oracle系统读取控制文件中关于数据文件和重作日志文件的内容，但并不打开该文件。这种打开方式常在数据库维护操

　　作中使用，如对数据文件的更名、改变重作日志以及打开归档方式等。在这种打开方式下，除了可以看到SGA系统列表以外，系统还会给出\"数据库装载完毕\"的提示。

　　3、STARTUP

　　该命令完成创建实例、安装实例和打开数据库的所有三个步骤。此时数据库使数据文件和重作日志文件在线，通常还会请求一个或者是多个回滚段。这时系统除了可以看到前

　　面Startup Mount方式下的所有提示外，还会给出一个\"数据库已经打开\"的提示。此时，数据库系统处于正常工作状态，可以接受用户请求。假如采用STARTUP NOMOUNT

　　或者是STARTUP MOUNT的数据库打开命令方式，必须采用ALTER DATABASE命令来执行打开数据库的操作。例如，假如你以STARTUP NOMOUNT方式打开数据库，也就

　　是说实例已经创建，但是数据库没有安装和打开。这是必须运行下面的两条命令，数据库才能正确启动。

　　　　ALTER DATABASE MOUNT;
　　　　ALTER DATABASE OPEN;

　　而假如以STARTUP MOUNT方式启动数据库，只需要运行下面一条命令即可以打开数据库：

　　ALTER DATABASE OPEN.

　　4、其他打开方式

　　除了前面介绍的三种数据库打开方式选项外，还有另外其他的一些选项。

　　(1) STARTUP RESTRICT

　　这种方式下，数据库将被成功打开，但仅仅答应一些特权用户（具有DBA角色的用户）才可以使用数据库。这种方式常用来对数据库进行维护，如数据的导入/导出操作时不希

　　望有其他用户连接到数据库操作数据。

　　(2) STARTUP FORCE

　　该命令其实是强行关闭数据库（shutdown abort）和启动数据库（startup）两条命令的一个综合。该命令仅在关闭数据库碰到问题不能关闭数据库时采用。

　　(3) ALTER DATABASE OPEN READ ONLY;

　　该命令在创建实例以及安装数据库后，以只读方式打开数据库。对于那些仅仅提供查询功能的产品数据库可以采用这种方式打开。

[Oracle用户]

1、显示当前用户名
　　select user from dual;
　　show user

2、显示当然用户有哪些表
　　select * from tab;

3、显示当所有用户的表
　　select * from user_tables;

4、显示当有用户可以访问表
　　select * from all_tables;

5、显示用户为SCOTT的表
　　select * from dba_tables where owner='SCOTT';

6、显示所有用户名和帐户的状态
　　select username,account_status from dba_users;

7、显示所有用户信息
　　select * from dba_users;

8、将SCOTT帐号解锁(加锁)
　　alter user scott account unlock(lock);

9、当前用户的缺省表空间
　　SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual);

10、显示当前数据库的用户
　　SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;

11、建立一个新用户
　　create user edpthw identified by "123456"
　　default tablespace users
　　temporary tablespace temp
　　quota unlimited on users
　　quota 10m on mytbs2;

21、授权连接给指定用户
　　GRANT "CONNECT" TO "hmtong"; GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;

22、查询Pwfile中存放的用户信息
　　select * from v$pwfile_users;

[Oracle登录]

1、运行SQLPLUS工具

　　C:\Users\wd-pc>sqlplus;

2、直接进入SQLPLUS命令提示符

　　C:\Users\wd-pc>sqlplus /nolog;

3、以OS身份连接 

　　C:\Users\wd-pc>sqlplus / as sysdba;   或

         C:\Users\wd-pc>sqlplus sys as sysdba; 或

　　SQL>connect / as sysdba;

4、普通用户登录

　　C:\Users\wd-pc>sqlplus scott/123456; 　或

　　SQL>connect scott/123456;  或

　　SQL>connect scott/123456@servername;

5、以管理员登录

　　C:\Users\wd-pc>sqlplus sys/123456 as sysdba;　或

　　SQL>connect sys/123456 as sysdba;

6、切换用户

　　SQL>conn hr/123456; 

　　注：conn同connect

 7、退出

　　exit or quit;

 8、启动关闭实例

  ● SQL>startup;启动实例、打开控制文件、打开数据文件

  ● SQL>startup mount;启动实例、打开控制文件  

  ● SQL>startup nomount;启动实例

  ● SQL>shutdown immediate;迫使所有用户执行完当前的 SQL 语句后断开连接，并关闭服务器

  ● SQL>shutdown;等待所有用户断开连接后关闭服务器

9、三个默认用户

　　用户: sys/ change_on_install
　　用户:system/manager

　　用户:scott/tiger

创建表空间

1.创建默认表空间WLP_DAT

CREATE TABLESPACE "MYSPACE" DATAFILE
'D:\oracle\oradata\orcl\data/WLP_DAT.dbf' SIZE 32M REUSE AUTOEXTEND ON NEXT 32M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

2.创建临时表空间WLP_TEMP（用于缓存,可以不建）

ALTER TABLESPACE "TEMP" ADD TEMPFILE
'D:\oracle\oradata\orcl\data/WLP_TEMP.dbf' SIZE 32m REUSE autoextend on next 32m;

创建用户并授权

1 CREATE USER "WLPING" PROFILE "DEFAULT" 
 2 IDENTIFIED BY "123456"
 3 DEFAULT TABLESPACE "MYSPACE"　　//默认指定表空间
 4 TEMPORARY TABLESPACE "TEMP"　　//指定缓存表空间
 5 ACCOUNT UNLOCK; 　　//用户非锁定
 6 
 7 GRANT EXECUTE ON dbms_comparison TO "WLPING";
 8 GRANT UNLIMITED TABLESPACE TO "XIR_MD";
 9 GRANT "CONNECT" TO "XIR_MD";
10 GRANT "RESOURCE" TO "XIR_MD";
11 
12 -- GRANT DBA TO XIR_MD;

PROFILE： Oracle系统中的profile可以用来对用户所能使用的数据库资源进行限制，使用Create Profile命令创建一个Profile，用它来实现对数据库资源的限制使用，如果把该profile分配给用户，则该用户所能使用的数据库资源都在该profile的限制之内。具体管理内容有：CPU的时间、I/O的使用、IDLE TIME（空闲时间）、CONNECT TIME（连接时间）、并发会话数量、口令机制等。

用户管理

oracle内部有两个建好的用户：system和sys。用户可直接登录到system用户以创建其他用户，因为system具有创建其他用户的权限。在安装oracle时，用户或系统管理员首先可以为自己建立一个用户。

※ 使用sys as sysdba 可以获得数据库最高权限

一、创建用户

语法[创建用户]： create user 用户名 identified by 口令[即密码]；

例子： create user test identified by test;

语法[更改用户]: alter user 用户名 identified by 口令[改变的口令];

例子： alter user test identified by 123456;

二、删除用户

语法：drop user 用户名;

例子：drop user test;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。

语法： drop user 用户名 cascade;

例子： drop user test cascade;

三、授权角色

oracle为兼容以前版本，提供三种标准角色（role）:connect/resource和dba.

（1）讲解三种标准角色：

1》. connect role(连接角色)

--临时用户，特指不需要建表的用户，通常只赋予他们connect role.

--connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。

--拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他数据的链（link）

2》. resource role(资源角色)

--更可靠和正式的数据库用户可以授予resource role。

--resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

3》. dba role(数据库管理员角色)

--dba role拥有所有的系统权限

--包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有

（2）授权命令

语法： grant connect, resource to 用户名;

例子： grant connect, resource to test;

1 CREATE TABLE MY_TABLE (
 2 ID NUMBER(10) PRIMARY KEY,
 3 NAME VARCHAR2(50) NOT NULL,
 4 BIRTHDAY DATE
 5 );
 6 // 表名注释
 7 COMMENT ON TABLE my_table IS '个人信息表';
 8 // 字段名注释
 9 COMMENT ON COLUMN MY_TABLE.ID IS 'ID';
10 COMMENT ON COLUMN MY_TABLE.NAME IS '姓名';

组合主键约束：CONSTRAINT PK_MY_TABLE PRIMARY KEY (column1,column2)

二、修改约束

禁用约束 disable constraint 约束名字;

删除约束 drop constraint 约束名字;

新增约束 alter table <表名 > add constraint <主键名>

约束介绍

1.主键约束：
要对一个列加主键约束的话，这列就必须要满足的条件就是分空
因为主键约束：就是对一个列进行了约束，约束为（非空、不重复）
以下是代码要对一个列加主键，列名为id,表名为emp

格式为：
alter table 表格名称 add constraint 约束名称增加的约束类型（列名）

例子：

alter table emp add constraint ppp primary key (id)

————————————————————————————————

2.check约束：
就是给一列的数据进行了限制
比方说，年龄列的数据都要大于20的
表名（emp）列名（age）

格式：
alter table 表名称 add constraint 约束名称增加的约束类型（列名）

例子：

alter table emp add constraint xxx check(age>20)

3.unique约束：
这样的约束就是给列的数据追加的不重复的约束类型

格式：
alter table 表名 add constraint 约束名称约束类型（列名）
比方说可以给ename列加个unique，让ename列的数据不重复
例子：

alter table emp add constraint qwe unique(ename)

————————————————————————————————

4.默认约束：
意思很简单就是让此列的数据默认为一定的数据

格式：
alter table 表名称 add constraint 约束名称约束类型默认值） for 列名

比方说：emp表中的gongzi列默认为10000

alter table emp add constraint jfsd default 10000 for gongzi

————————————————————————————————

5.外键约束：
这个有点难理解了，外键其实就是引用
因为主键实现了实体的完整性，
外键实现了引用的完整性，
应用完整性规定，所引用的数据必须存在！

其实就是个引用，
比方说一个表名称叫dept 里面有2列数据一列是ID一列是ENAME
id：表示产品的编号
ename：表示产品的名称

另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID
id:表示用户号
did：表示购买的产品号

要让emp表中的did列去引用dept表中的id

可以用下面的方法

格式：
alter table 表名 add constraint 约束名称约束类型 (列名) references 被引用的表名称（列名）

例子：

alter table emp add constraint jfkdsj foreign key (did) references dept (id）

三、操作表

rename 表名 to 表名　　//修改表名
 
 truncate table 表名　　//删除表中的所有数据，速度比delete快很多,截断表
 
 drop table 表名　　//删除表

四、修改字段

alter table 表名 modify (字段 字段类型);  -- 修改字段数据类型
 
 alter table 表名 rename column 列名 to 列名 --修改字段名
 
 alter table 表名 add 列名 字段类型; --增加字段
 
 alter table 表名 drop column 字段名;  -- 删除字段

五、修改数据

insert into 表名(值1,值2) values(值1,值2); 　　// 插入数据
 
 update 表名 set 字段=值 [修改条件]　　　//修改数据
 
 delete from table  where 条件　　//删除数据

六、查询

/* 获取表：*/
 
 select table_name from user_tables; //当前用户的表
 
 select table_name from all_tables; //所有用户的表
 
 select table_name from dba_tables; //所有表，包括系统表（需要dba权限）
 
 select table_name from dba_tables where owner='WLPING';  //某个用户的表，包括所有的系统表（需要dba权限）

/查询系统时间/

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

导入导出

总结： EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。
EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。
IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。

EXP不能导出分区表，而EXPDP可以

一、导入dmp文件

imp

1）imp 用户名/密码@服务器IP:端口/服务名 file=dmp文件路径;

imp username/password@127.0.0.1:1521/orcl file=D:\oracle\admin\orcl\dpdump\a.dmp;

2）将exp_export.dmp 中的表table1,table2导入

imp system/manager@hostname:1521/ora11g file=exp_export.dmp tables=table1,table2

impdp

1）导到指定用户下

impdp scott/tiger DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp SCHEMAS=scott;

2）改变表的owner

impdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3）导入表空间

impdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp TABLESPACES=example;

3）导入整个库文件

impdb system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp full=y;

二、导出dmp文件

※ 导入导出指定日志输出文件 log=exp_export.log

※ 在导出命令后面加上 compress=y 可以对导出文件进行压缩

1.导出整个数据库实例下的所有数据
2.导出指定用户的所有表
3.导出指定表

exp

1）将数据库ORACLE完全导出

exp sysuser/password@127.0.0.1:1521/ORCL file=D:d:\daochu.dmp full=y;

2）将数据库中WLP用户与,WLPING用户的表导出

exp username/password@127.0.0.1:1521/ORCL file=d:\daochu.dmp   owner=(WLP,WLPING);

3）将数据库中的表table1、table2导出

exp sysuser/password@127.0.0.1:1521/ORCL file= d:\data\newsmgnt.dmp tables=(table1,table2);

4）将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp system/manager@loaclhost:1521/orcl file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\" log=exp_export.log;

expdp

创建逻辑目录（默认为oracle用户空间下的dpdump目录）

create directory DUMP_DIR as '/oracle/DUMP_DIR';

在服务器上创建该目录，因为Oracle并不会自动创建，如果目录不存在导出会报错

mkdir -p /oracle/DUMP_DIR

给用户授予在该目睹读取的权限

grant read,write on directory DUMP_DIR to scott;

1）导整个数据库

expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp FULL=y;

2）按用户导

expdp scott/tiger@localhost:1521/ora11g schemas=scott dumpfile=expdp_export.dmp DIRECTORY=DUMP_DIR;

3）并行进程parallel

expdp scott/tiger@localhost:1521/ora11g directory=DUMP_DIR dumpfile=expdp_export.dmp parallel=40 job_name=expdp40;

4）按表名导

expdp scott/tiger@localhost:1521/ora11g TABLES=emp,dept dumpfile=expdp_export.dmp DIRECTORY=DUMP_DIR;

5）按查询条件导

expdp scott/tiger@localhost:1521/ora11g directory=DUMP_DIR dumpfile=expdp_export.dmp tables=emp query='WHERE deptno=20';

6）按表空间导

expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp TABLESPACES=temp,example;

序列

新建序列

　　CREATE SEQUENCE 序列名

　　[INCREMENT BY n]　　　　//步长默认1 非零

　　[START WITH n]　　　　　　//开始值

　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}] //最大值/最小值

　　[{CYCLE|NOCYCLE}]　　　　//到达最大值是否循环，不循环时直接报错

　　[{CACHE n| NOCACHE}];　　//默认20，当有大量请求时使用缓存，预先生成多个序列提供使用，断开连接后会丢失未使用的序列值，导致序号不连续。

实例：

1 create sequence seq_my_define
2 minvalue 1
3 nomaxvalue
4 start with 1
5 increment by 1
6 nocycle
7 nocache;

查询序列当前值

select  seq_my_define.currval from dual;

查询下一增长值（提醒，这里虽然是查询，但序列值会改变，相当于++i）：

select  seq_my_define.nextval from dual;

触发器

主键自增：oracle没有主键自增选项，需要自己使用触发器实现

1 CREATE OR REPLACE TRIGGER tr_my_define
2 BEFORE INSERT ON MY_WMPS_DEFINE FOR EACH ROW
3 begin
4     select seq_my_define.nextval into:new.id from dual;
5 end;

变量使用：

例子：将序列S_BOND_POOL_TREE的当前值与数据库ID最大值统一

变量可以在定义时赋值，也可以后面再赋值。

通过select语句将查询结果赋值给变量：select 字段名 into 变量名 from 表

execute immediate 'str'; //可以使用变量拼接字符串得到语句来执行

1 DECLARE
 2 　　n NUMBER :=0;
 3 BEGIN
 4 　　select (num - S_BOND_POOL_TREE.nextval) into n
 5 　　from(
 6 　　　　select max(to_number( node_id)) as num from  XIR_TRD_J.TREE_NODE_INFO
 7 　　);
 8 　　IF n = 0 then return;
 9 　　ELSE
10 　　　　execute immediate 'alter sequence S_BOND_POOL_TREE increment by '|| n;
11 　　　　select S_BOND_POOL_TREE.nextval into n from dual; 
12 　　　　execute immediate 'alter sequence S_BOND_POOL_TREE increment by 1';
13 　　END IF;
14 END;

最后修改：2022 年 01 月 29 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Oracle 常用SQL命令

Tehran • 2021 年 07 月 18 日

<div class="tip share">请注意，本文编写于 1010 天前，最后修改于 815 天前，其中某些信息可能已经过时。</div>

<h2>启动和关闭方式</h2>一、Oracle数据库几种启动方式<pre><code>1、startup nomount

非安装启动，这种方式下启动可执行：重建控制文件、重建数据库，读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。

2、startup mount (dbname)

安装启动，这种方式启动下可执行：数据库日志归档、数据库介质恢复、使数据文件联机或脱机、重新定位数据文件、重做日志文件。

执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查。

3、startup open (dbname)

先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，这种方式下可访问数据库中的数据。

4、startup 等于以下三个命令

startup nomount

alter database mount

alter database open

5、startup restrict

约束方式启动，这种方式能够启动数据库，但只允许具有一定特权的用户访问，非特权用户访问时，会出现以下提示：

ERROR：ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用

6、startup force

强制启动方式，当不能关闭数据库时，可以用startup force来完成数据库的关闭，先关闭数据库，再执行正常启动数据库命令

7、startup pfile=参数文件名

带初始化参数文件的启动方式，先读取参数文件，再按参数文件中的设置启动数据库

例：startup pfile=E:\Oracle\admin\oradb\pfile\init.ora

8、startup EXCLUSIVE

独占和共享启动</code></pre>二、Oracle三种关闭方式<pre><code>1、shutdown normal
　　正常方式关闭数据库

2、shutdown immediate

立即方式关闭数据库，在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），当使用

shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。

3、shutdown abort

直接关闭数据库，正在访问数据库的会话会被突然终止，如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间。</code></pre>三、详解　　Oracle数据库提供了几种不同的数据库启动和关闭方式，本文将具体介绍这些启动和关闭方式之间的区别以及它们各自不同的功能。　　一、启动和关闭Oracle数据库　　　　对于大多数Oracle DBA来说，启动和关闭Oracle数据库最常用的方式就是在命令行方式下的Server Manager。从Oracle 8i以后，系统将Server Manager的所有功能都　　集中到了SQLPlus中，也就是说从8i以后对于数据库的启动和关闭可以直接通过SQLPlus来完成，而不再另外需要Server Manager，但系统为了保持向下兼容，依旧保留了　　Server Manager工具。另外也可通过图形用户工具（GUI）的Oracle Enterprise Manager来完成系统的启动和关闭，图形用户界面Instance Manager非常简单，这里不再　　详述。要启动和关闭数据库，必须要以具有Oracle 治理员权限的用户登陆，通常也就是以具有SYSDBA权限的用户登陆。一般我们常用INTERNAL用户来启动和关闭数据库　　（INTERNAL用户实际上是SYS用户以SYSDBA连接的同义词）。Oracle数据库的新版本将逐步淘汰INTERNAL这个内部用户，所以我们最好还是设置DBA用户具有SYSDBA　　权限。　　二、数据库的启动（STARTUP）　　启动一个数据库需要三个步骤：　　1、 创建一个Oracle实例（非安装阶段）　　2、 由实例安装数据库（安装阶段）　　3、 打开数据库（打开阶段）　　在Startup命令中，可以通过不同的选项来控制数据库的不同启动步骤。　　1、STARTUP NOMOUNT　　NONOUNT选项仅仅创建一个Oracle实例。读取init.ora初始化参数文件、启动后台进程、初始化系统全局区（SGA）。Init.ora 文件定义了实例的配置，包括内存结构的大小　　和启动后台进程的数量和类型等。实例名根据Oracle_SID设置，不一定要与打开的数据库名称相同。当实例打开后，系统将显示一个SGA内存结构和大小的列表，如下所示：　　SQL&gt; startup nomount 　　　　ORACLE 例程已经启动。 　　　　Total System Global Area 35431692 bytes 　　　　Fixed Size 70924 bytes 　　　　Variable Size 18505728 bytes 　　　　Database Buffers 16777216 bytes 　　　　Redo Buffers 77824 bytes　　2、STARTUP MOUNT　　该命令创建实例并且安装数据库，但没有打开数据库。Oracle系统读取控制文件中关于数据文件和重作日志文件的内容，但并不打开该文件。这种打开方式常在数据库维护操　　作中使用，如对数据文件的更名、改变重作日志以及打开归档方式等。在这种打开方式下，除了可以看到SGA系统列表以外，系统还会给出\&quot;数据库装载完毕\&quot;的提示。　　3、STARTUP　　该命令完成创建实例、安装实例和打开数据库的所有三个步骤。此时数据库使数据文件和重作日志文件在线，通常还会请求一个或者是多个回滚段。这时系统除了可以看到前　　面Startup Mount方式下的所有提示外，还会给出一个\&quot;数据库已经打开\&quot;的提示。此时，数据库系统处于正常工作状态，可以接受用户请求。假如采用STARTUP NOMOUNT　　或者是STARTUP MOUNT的数据库打开命令方式，必须采用ALTER DATABASE命令来执行打开数据库的操作。例如，假如你以STARTUP NOMOUNT方式打开数据库，也就　　是说实例已经创建，但是数据库没有安装和打开。这是必须运行下面的两条命令，数据库才能正确启动。　　　　ALTER DATABASE MOUNT; 　　　　ALTER DATABASE OPEN;　　而假如以STARTUP MOUNT方式启动数据库，只需要运行下面一条命令即可以打开数据库：　　ALTER DATABASE OPEN.　　4、其他打开方式　　除了前面介绍的三种数据库打开方式选项外，还有另外其他的一些选项。　　(1) STARTUP RESTRICT　　这种方式下，数据库将被成功打开，但仅仅答应一些特权用户（具有DBA角色的用户）才可以使用数据库。这种方式常用来对数据库进行维护，如数据的导入/导出操作时不希　　望有其他用户连接到数据库操作数据。　　(2) STARTUP FORCE　　该命令其实是强行关闭数据库（shutdown abort）和启动数据库（startup）两条命令的一个综合。该命令仅在关闭数据库碰到问题不能关闭数据库时采用。　　(3) ALTER DATABASE OPEN READ ONLY;　　该命令在创建实例以及安装数据库后，以只读方式打开数据库。对于那些仅仅提供查询功能的产品数据库可以采用这种方式打开。<h2>[Oracle用户]</h2><pre><code>1、显示当前用户名
　　select user from dual;
　　show user

2、显示当然用户有哪些表
　　select * from tab;

3、显示当所有用户的表
　　select * from user_tables;

4、显示当有用户可以访问表
　　select * from all_tables;

5、显示用户为SCOTT的表
　　select * from dba_tables where owner='SCOTT';

6、显示所有用户名和帐户的状态
　　select username,account_status from dba_users;

7、显示所有用户信息
　　select * from dba_users;

8、将SCOTT帐号解锁(加锁)
　　alter user scott account unlock(lock);

9、当前用户的缺省表空间
　　SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual);

10、显示当前数据库的用户
　　SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;

11、建立一个新用户
　　create user edpthw identified by &quot;123456&quot;
　　default tablespace users
　　temporary tablespace temp
　　quota unlimited on users
　　quota 10m on mytbs2;

21、授权连接给指定用户
　　GRANT &quot;CONNECT&quot; TO &quot;hmtong&quot;; GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;

22、查询Pwfile中存放的用户信息
　　select * from v$pwfile_users;</code></pre><h2>[Oracle登录]</h2><pre><code>1、运行SQLPLUS工具

C:\Users\wd-pc&gt;sqlplus;

2、直接进入SQLPLUS命令提示符

C:\Users\wd-pc&gt;sqlplus /nolog;

3、以OS身份连接

C:\Users\wd-pc&gt;sqlplus / as sysdba;   或

C:\Users\wd-pc&gt;sqlplus sys as sysdba; 或

SQL&gt;connect / as sysdba;

4、普通用户登录

C:\Users\wd-pc&gt;sqlplus scott/123456; 　或

SQL&gt;connect scott/123456;  或

SQL&gt;connect scott/123456@servername;

5、以管理员登录

C:\Users\wd-pc&gt;sqlplus sys/123456 as sysdba;　或

SQL&gt;connect sys/123456 as sysdba;

6、切换用户

SQL&gt;conn hr/123456;

注：conn同connect

7、退出

exit or quit;

8、启动关闭实例

● SQL&gt;startup;启动实例、打开控制文件、打开数据文件

● SQL&gt;startup mount;启动实例、打开控制文件

● SQL&gt;startup nomount;启动实例

● SQL&gt;shutdown immediate;迫使所有用户执行完当前的 SQL 语句后断开连接，并关闭服务器

● SQL&gt;shutdown;等待所有用户断开连接后关闭服务器

9、三个默认用户

用户: sys/ change_on_install
　　用户:system/manager

用户:scott/tiger</code></pre><h2>创建表空间</h2>1.创建默认表空间WLP_DAT<pre><code>CREATE TABLESPACE &quot;MYSPACE&quot; DATAFILE
'D:\oracle\oradata\orcl\data/WLP_DAT.dbf' SIZE 32M REUSE AUTOEXTEND ON NEXT 32M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;</code></pre>2.创建临时表空间WLP_TEMP（用于缓存,可以不建）<pre><code>ALTER TABLESPACE &quot;TEMP&quot; ADD TEMPFILE
'D:\oracle\oradata\orcl\data/WLP_TEMP.dbf' SIZE 32m REUSE autoextend on next 32m;</code></pre><h2>创建用户并授权</h2><pre><code>1 CREATE USER &quot;WLPING&quot; PROFILE &quot;DEFAULT&quot; 
 2 IDENTIFIED BY &quot;123456&quot;
 3 DEFAULT TABLESPACE &quot;MYSPACE&quot;　　//默认指定表空间
 4 TEMPORARY TABLESPACE &quot;TEMP&quot;　　//指定缓存表空间
 5 ACCOUNT UNLOCK; 　　//用户非锁定
 6 
 7 GRANT EXECUTE ON dbms_comparison TO &quot;WLPING&quot;;
 8 GRANT UNLIMITED TABLESPACE TO &quot;XIR_MD&quot;;
 9 GRANT &quot;CONNECT&quot; TO &quot;XIR_MD&quot;;
10 GRANT &quot;RESOURCE&quot; TO &quot;XIR_MD&quot;;
11 
12 -- GRANT DBA TO XIR_MD;</code></pre>PROFILE： Oracle系统中的profile可以用来对用户所能使用的数据库资源进行限制，使用Create Profile命令创建一个Profile，用它来实现对数据库资源的限制使用，如果把该profile分配给用户，则该用户所能使用的数据库资源都在该profile的限制之内。具体管理内容有：CPU的时间、I/O的使用、IDLE TIME（空闲时间）、CONNECT TIME（连接时间）、并发会话数量、口令机制等。<h2>用户管理</h2>oracle内部有两个建好的用户：system和sys。用户可直接登录到system用户以创建其他用户，因为system具有创建其他用户的权限。 在安装oracle时，用户或系统管理员首先可以为自己建立一个用户。※ 使用sys as sysdba 可以获得数据库最高权限一、创建用户语法[创建用户]： create user 用户名 identified by 口令[即密码]；例子： create user test identified by test;语法[更改用户]: alter user 用户名 identified by 口令[改变的口令];例子： alter user test identified by 123456;二、删除用户语法：drop user 用户名;例子：drop user test;若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。语法： drop user 用户名 cascade;例子： drop user test cascade;三、授权角色oracle为兼容以前版本，提供三种标准角色（role）:connect/resource和dba.（1）讲解三种标准角色：1》. connect role(连接角色)--临时用户，特指不需要建表的用户，通常只赋予他们connect role.--connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。--拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link）2》. resource role(资源角色)--更可靠和正式的数据库用户可以授予resource role。--resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。3》. dba role(数据库管理员角色)--dba role拥有所有的系统权限--包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有（2）授权命令语法： grant connect, resource to 用户名;例子： grant connect, resource to test;<pre><code>1 CREATE TABLE MY_TABLE (
 2 ID NUMBER(10) PRIMARY KEY,
 3 NAME VARCHAR2(50) NOT NULL,
 4 BIRTHDAY DATE
 5 );
 6 // 表名注释
 7 COMMENT ON TABLE my_table IS '个人信息表';
 8 // 字段名注释
 9 COMMENT ON COLUMN MY_TABLE.ID IS 'ID';
10 COMMENT ON COLUMN MY_TABLE.NAME IS '姓名';</code></pre>组合主键约束：CONSTRAINT PK_MY_TABLE PRIMARY KEY (column1,column2)二、修改约束禁用约束 disable constraint 约束名字;删除约束 drop constraint 约束名字;新增约束 alter table &lt;表名 &gt; add constraint &lt;主键名&gt;约束介绍1.主键约束： 要对一个列加主键约束的话，这列就必须要满足的条件就是分空 因为主键约束：就是对一个列进行了约束，约束为（非空、不重复） 以下是代码 要对一个列加主键，列名为id,表名为emp格式为： alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）例子：<pre><code>alter table emp add constraint ppp primary key (id)</code></pre>————————————————————————————————2.check约束： 就是给一列的数据进行了限制 比方说，年龄列的数据都要大于20的 表名（emp） 列名（age）格式： alter table 表名称 add constraint 约束名称 增加的约束类型 （列名）例子：<pre><code>alter table emp add constraint xxx check(age&gt;20)</code></pre><hr>3.unique约束： 这样的约束就是给列的数据追加的不重复的约束类型格式： alter table 表名 add constraint 约束名称 约束类型（列名） 比方说可以给ename列加个unique，让ename列的数据不重复 例子：<pre><code>alter table emp add constraint qwe unique(ename)</code></pre>————————————————————————————————4.默认约束： 意思很简单就是让此列的数据默认为一定的数据格式： alter table 表名称 add constraint 约束名称 约束类型 默认值） for 列名比方说：emp表中的gongzi列默认为10000<pre><code>alter table emp add constraint jfsd default 10000 for gongzi</code></pre>————————————————————————————————5.外键约束： 这个有点难理解了，外键其实就是引用 因为主键实现了实体的完整性， 外键实现了引用的完整性， 应用完整性规定，所引用的数据必须存在！其实就是个引用， 比方说一个表名称叫dept 里面有2列数据 一列是ID一列是ENAME id：表示产品的编号 ename：表示产品的名称另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID id:表示用户号 did：表示购买的产品号要让emp表中的did列去引用dept表中的id可以用下面的方法格式： alter table 表名 add constraint 约束名称 约束类型 (列名) references 被引用的表名称（列名）例子：<pre><code>alter table emp add constraint jfkdsj foreign key (did) references dept (id）</code></pre>三、操作表<pre><code>rename 表名 to 表名　　//修改表名
 
 truncate table 表名　　//删除表中的所有数据，速度比delete快很多,截断表
 
 drop table 表名　　//删除表</code></pre>四、修改字段<pre><code>alter table 表名 modify (字段 字段类型); -- 修改字段数据类型
 
 alter table 表名 rename column 列名 to 列名 --修改字段名
 
 alter table 表名 add 列名 字段类型; --增加字段
 
 alter table 表名 drop column 字段名; -- 删除字段</code></pre>五、修改数据<pre><code>insert into 表名(值1,值2) values(值1,值2); 　　// 插入数据
 
 update 表名 set 字段=值 [修改条件]　　　//修改数据
 
 delete from table where 条件　　//删除数据</code></pre>六、查询<pre><code>/* 获取表：*/
 
 select table_name from user_tables; //当前用户的表
 
 select table_name from all_tables; //所有用户的表
 
 select table_name from dba_tables; //所有表，包括系统表（需要dba权限）
 
 select table_name from dba_tables where owner='WLPING'; //某个用户的表，包括所有的系统表（需要dba权限）</code></pre>/查询系统时间/select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;<h2>导入导出</h2>总结： EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。 EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。 IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。EXP不能导出分区表，而EXPDP可以一、导入dmp文件<h3>imp</h3>1）imp 用户名/密码@服务器IP:端口/服务名 file=dmp文件路径;<pre><code>imp username/password@127.0.0.1:1521/orcl file=D:\oracle\admin\orcl\dpdump\a.dmp;</code></pre>2）将exp_export.dmp 中的表table1,table2导入<pre><code>imp system/manager@hostname:1521/ora11g file=exp_export.dmp tables=table1,table2</code></pre><h3>impdp</h3>1）导到指定用户下<pre><code>impdp scott/tiger DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp SCHEMAS=scott;</code></pre>2）改变表的owner<pre><code>impdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;</code></pre>3）导入表空间<pre><code>impdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp TABLESPACES=example;</code></pre>3）导入整个库文件<pre><code>impdb system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp full=y;</code></pre>二、导出dmp文件※ 导入导出指定日志输出文件 log=exp_export.log※ 在导出命令后面加上 compress=y 可以对导出文件进行压缩1.导出整个数据库实例下的所有数据 2.导出指定用户的所有表 3.导出指定表<h3>exp</h3>1）将数据库ORACLE完全导出<pre><code>exp sysuser/password@127.0.0.1:1521/ORCL file=D:d:\daochu.dmp full=y;</code></pre>2）将数据库中WLP用户与,WLPING用户的表导出<pre><code>exp username/password@127.0.0.1:1521/ORCL file=d:\daochu.dmp owner=(WLP,WLPING);</code></pre>3）将数据库中的表table1、table2导出<pre><code>exp sysuser/password@127.0.0.1:1521/ORCL file= d:\data\newsmgnt.dmp tables=(table1,table2);</code></pre>4）将数据库中的表table1中的字段filed1以"00"打头的数据导出<pre><code>exp system/manager@loaclhost:1521/orcl file=d:\daochu.dmp tables=(table1) query=\&quot; where filed1 like '00%'\&quot; log=exp_export.log;</code></pre><h3>expdp</h3>创建逻辑目录（默认为oracle用户空间下的dpdump目录）<pre><code>create directory DUMP_DIR as '/oracle/DUMP_DIR';</code></pre>在服务器上创建该目录，因为Oracle并不会自动创建，如果目录不存在导出会报错<ul><li>mkdir -p /oracle/DUMP_DIR</li></ul>给用户授予在该目睹读取的权限<ul><li>grant read,write on directory DUMP_DIR to scott;</li></ul>1）导整个数据库<pre><code>expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp FULL=y;</code></pre>2）按用户导<pre><code>expdp scott/tiger@localhost:1521/ora11g schemas=scott dumpfile=expdp_export.dmp DIRECTORY=DUMP_DIR;</code></pre>3）并行进程parallel<pre><code>expdp scott/tiger@localhost:1521/ora11g directory=DUMP_DIR dumpfile=expdp_export.dmp parallel=40 job_name=expdp40;</code></pre>4）按表名导<pre><code>expdp scott/tiger@localhost:1521/ora11g TABLES=emp,dept dumpfile=expdp_export.dmp DIRECTORY=DUMP_DIR;</code></pre>5）按查询条件导<pre><code>expdp scott/tiger@localhost:1521/ora11g directory=DUMP_DIR dumpfile=expdp_export.dmp tables=emp query='WHERE deptno=20';</code></pre>6）按表空间导<pre><code>expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=expdp_export.dmp TABLESPACES=temp,example;</code></pre><h2>序列</h2>新建序列　　CREATE SEQUENCE 序列名　　[INCREMENT BY n]　　　　//步长 默认1 非零　　[START WITH n]　　　　　　//开始值　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}] //最大值/最小值　　[{CYCLE|NOCYCLE}]　　　　//到达最大值是否循环，不循环时直接报错　　[{CACHE n| NOCACHE}];　　//默认20，当有大量请求时使用缓存，预先生成多个序列提供使用，断开连接后会丢失未使用的序列值，导致序号不连续。实例：<pre><code>1 create sequence seq_my_define
2 minvalue 1
3 nomaxvalue
4 start with 1
5 increment by 1
6 nocycle
7 nocache;</code></pre>查询序列当前值<pre><code>select seq_my_define.currval from dual;</code></pre>查询下一增长值（提醒，这里虽然是查询，但序列值会改变，相当于++i）：<pre><code>select seq_my_define.nextval from dual;</code></pre><h3>触发器</h3>主键自增：oracle没有主键自增选项，需要自己使用触发器实现<pre><code>1 CREATE OR REPLACE TRIGGER tr_my_define
2 BEFORE INSERT ON MY_WMPS_DEFINE FOR EACH ROW
3 begin
4 select seq_my_define.nextval into:new.id from dual;
5 end;</code></pre>变量使用：例子：将序列S_BOND_POOL_TREE的当前值与数据库ID最大值统一变量可以在定义时赋值，也可以后面再赋值。通过select语句将查询结果赋值给变量：select 字段名 into 变量名 from 表execute immediate 'str'; //可以使用变量拼接字符串得到语句来执行<pre><code>1 DECLARE
 2 　　n NUMBER :=0;
 3 BEGIN
 4 　　select (num - S_BOND_POOL_TREE.nextval) into n
 5 　　from(
 6 　　　　select max(to_number( node_id)) as num from XIR_TRD_J.TREE_NODE_INFO
 7 　　);
 8 　　IF n = 0 then return;
 9 　　ELSE
10 　　　　execute immediate 'alter sequence S_BOND_POOL_TREE increment by '|| n;
11 　　　　select S_BOND_POOL_TREE.nextval into n from dual; 
12 　　　　execute immediate 'alter sequence S_BOND_POOL_TREE increment by 1';
13 　　END IF;
14 END;</code></pre>

Oracle 常用SQL命令

启动和关闭方式

[Oracle用户]

[Oracle登录]

创建表空间

创建用户并授权

用户管理

导入导出

imp

impdp

exp

expdp

序列

触发器

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

影视资源站接口

Oracle开机自启配置

Oracle精读85_存储过程的基本语法

Oracle 面试宝典-RAC篇

Linux命令经典面试题：统计文件中出现次数最多的前10个单词

Oracle精读81_表字段常用数据类型

Oracle 常用SQL命令

启动和关闭方式

[Oracle用户]

[Oracle登录]

创建表空间

创建用户并授权

用户管理

导入导出

imp

impdp

exp

expdp

序列

触发器

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

Oracle 常用SQL命令

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复