oracle 基本操作

数据库和实例

=============================================
Jdbc：oracle：thin：@127.0.0.1:1521：orcl
监听：
启动监听：lsnrctl start
停止监听：lsnrctl stop orcl
查看监听状态：lsnrctl status
NET Start OracleServiceName
NET Stop OracleServiceName

查看数据库实例
show parameter service_name;
select name from V$database;
select instance_name from V$instance;
prompt 注释输出

spool xx.log
--要截取的语句
spool end

查看表的备注
select comments from user_col_comments where table_name=‘Y_JGQ_YGXXB‘;

实例是数据库一组后台进程/线程以及一个共享内存区
一般情况下是单实例数据库，一个数据库上只有一个实例对其进行操作。
但是真正应用集群（Real Application Clusters，RAC）
可以有多台实例同时装载并打开一个数据库（位于一组共享物理磁盘上）。

表的管理
=============================================
数据类型
char（10） 定长，查询快，浪费空间 最大2000字符
varchar2（20） 变长，查询慢，节省空间 最大4000
number
number（5,2）五位数，2位有效小数
number（5） 五位整数
date 时间类型(日-月-年)(01-5月-05)
timestamp 时间类型比date精确
blob 二进制类型 图片声音

建表
create table student（
xh number(4),xm varchar(2),sex char(2),bitthday date,sal number(7,2)
);

删除表
drop table 表名;

修改表名字
rename 表名 to 新表名

添加一个字段
alter table 表名 add(列名);

删除一个字段
alter table 表名 drop column 列名;

修改字段长度
alter table 表名 modify(列名);

修改字段类型或者名字(不能有数据)
alter table 表名 modify(列名);

添加数据
insert into 表名(列名...) values(列名...);
insert into 表名 values(列名...);

删除数据，可以通过保存点中的日志文件找回
delete from 表名;（可以通过回滚恢复，删除之前设置回滚点 save point 回滚点；）

回滚 rollback；

快速删除数据，不写日志，不能找回；
truncate table 表名;

删除表的结构和数据
drop table 表名;

查表：
查看表的结构
desc 表名

查表
select * from 表名;
select 列名... from 表名;
select 列名 ‘要显示的列名‘ from 表名;

取消重复行
select distinct 列名 from 表名;

单表查询
select * from 表名 where (列名>500 or 列名=‘xxx‘) and 列名=‘xxx‘;
select * from 表名 order by 列名;(asc默认从低到高)
select * from 表名 order by 列名1 asc 列名2 desc;(从高到低)
select max(表名), min(表名) from 表名;
select 列名1,列名2 from 表名 where 列名1=(select max(列名1) from 表名);
select 列名1,列名2 from 表名 grop by 列名2;

多表联合查询(笛卡尔积)(查询条件不能小于表的个数-1)
表关联查询

更新
update Y_JGQ_YGXXB set RLZYH=1001 where yhh=‘P03890‘
select Y_JGQ_YGXXB.*, Z_JSQ_JGB.FHMC , Y_JGQ_YGXXB.SSJG , Z_JSQ_JGB.XNJG from Y_JGQ_YGXXB ,Z_JSQ_JGB where Y_JGQ_YGXXB.SSJG = Z_JSQ_JGB.XNJG;
select mar1.列名1,mark2.列名2 from 表名1 mar1,表名2 mark2 where mar1.列名1=mark2.列名2;
子查询=内嵌视图
给子查询表指定别名别加as

分页 一共三种
1.rownum(像一个隐藏的字段。记录的是行数)
select * from (select a1.* rownum rn from (select * from mylist) a1 where rownum<=10) where rn>6;

用查询结果创建表
create table 表名1() as select * from 表名2;
合并查询 union(并集去重) union all(并集不去重) intersect(交集) minus(差集)
select * from 表名1 union select * from 表名2;

(导入导出表要到oracle下的bin下进行)
导出表
exp userid= 用户名/密码@实例 tables=(表名) file=path\xx.dmp;

导出表结构
exp userid= 用户名/密码@实例 tables=(表名) file=path\xx.dmp rows=n;

导出数据库(增量备份)
exp userid= system/密码@实例 full=y inctype=complete file=path\xx.dmp;

导入表
imp userid= 用户名/密码@实例 tables=(表名) file=path\xx.dmp;

复制一张表
create table 新建表 as select * from 原表;

导出数据库
复制一张表中的数据到另外一张表
insert into 目标表 from * 原表;

用户
=============================================
用户：类似于其他数据库的数据库，这里一个项目对应一个用户
用户创建时，创建对应的方案 方案里面有数据对象(表，触发器，视图...)
sys 系统管理员权限(sysdba) 数据字典的基表和动态视图在sys里面
system 数据库管理员权限(dba) 次一级的内部数据 管理用数据

创建用户并给权限
create user 用户名 identified by 密码
grant connect to 用户名
grant select on emp to 用户名 with grant option(这个权限可以继续先下传递)

删除用户(删除用户名会删除此用户建的表，必须级联删除)
drop user 用户名 cascade

切换用户
conn 用户名/密码

断开连接
disc 用户名

修改密码
passw 回车
旧密码
新密码

截取界面内容到文件中：
spool path；
sql语句；
spool off；

查看目前登陆角色
show user；

运行特定目录下的脚本
start path\xx.sql;

限制登录时间
create profile 限制名 limit failed_login_attempts 3 password_lock_time 2;
alter user 用户名 profile 限制名;

解锁
alter user 用户名 account unlock;

定期修改密码
create profile 限制名 limit password_life_time 10 password_grace_time 2;
drop profile password.history cascade

用os用户登录，不用密码
sqlplus / as sysdba

权限和角色
=============================================
权限分为：系统权限 和 对象权限(访问其他对象的权限)
角色分为：自定义角色 和 预定义角色
角色是权限的一个集合

查看角色
select * from user_role_privs;

自己建的表的操作权限赋给别的用户
grant select on 表名 to 别的用户名
select * from 别的用户名.表名

收回权限
revoke select on 表名 from 别的用户名

dba 数据库管理员
sysdba 系统管理员
sysoper 系统操作员
connect 登陆角色

Sysdba用户: 可以改变字符集、创建删除数据库、登录之后用户是SYS（shutdown、startup）
Sysoper:用户不可改变字符集、不能创、删数据库、登陆之后用户是PUBLIC （shutdown、startup）
DBA用户：只有在启动数据库后才能执行各种管理工作。
Sysdba> Sysoper>普通的DBA

数据字典 静态的数据库系统信息(属于sys 是只基表和视图的集合)
user_xxx
all_xxx
dba_xxx
user_tables 当前用户拥有的所有表
all_table 当前用户可以访问到的所有表
dba_tables 所有方案所拥有的数据库表
dba_users 所有用户
dba_sys_privs 用户具有的系统权限
dba_tab_privs 用户具有的对象权限
dba_col_privs 显示用户的列权限
dba_role_privs 用户具有的角色

查询所有系统权限(一共140种)
select * from system_privilege_map order by name;

查询所有角色
select * from dba_roles;

查询所有对象权限(一共16种)
select distinct privilege from dba_tab_privs;

查询数据库所有表空间
select tablespace_name from dba_tablespaces;

查询一个角色拥有的系统权限
select * from dba_sys_privs where grantee=‘角色名‘;
select * from role_sys_privs where role=‘角色名‘;

查询一个角色拥有的对象权限
select * from dba_tab_privs where grantee=‘角色名‘;
select * from role_tab_privs where role=‘角色名‘;

用户具有的角色
select * from dba_role_privs where grantee=‘用户名‘;

显示当前数据库全称
select * from global_name;

显示当前用户可以访问所有数据字典视图
select * from dict where comments like ‘%grant%‘;

动态性能视图 记录当前例程的活动信息
v_$开头 其同义词以v$开头 v_$datafile

表空间
=============================================
表空间：管理表的逻辑文件夹
表空间 -》段 -》区 -》块
不同的表空间可以建立相同的表，但是需要建表的用户不同，区别表示用用户区别，而不是表空间，
表空间是表的逻辑结构。表命名约束跟用户有关,跟表空间无关
(索引单放表空间，触发器单放表空间，提高效率，减少io)

表空间的作用
1.提高磁盘使用率
2.减少io读取同时利于备份和恢复

创建表空间(单文件不能超过500m)
create tablespace 表空间名 datafile ‘path\xxx.dbf‘
size 500m uniform size 2m;

使用表空间
create table 表名() tablespace 表空间名;

使表空间脱机
alter tablespace 表空间名 offline;

使表空间联机
alter tablespace 表空间名 online;

只读表空间
alter tablespace 表空间名 read only;

读写表空间
alter tablespace 表空间名 read write;

显示表空间的所有表
select * from all_tables where tablespace_name=‘表空间名‘

知道表，查属于那个表空间
select tablespace_name，table_name from user_tables where table_name=‘表名‘;

删除表空间
drop tablespace 表空间 including contents and datafiles；

扩展表空间
1.增加数据文件
alter tablespace 表空间 add datafile ‘path\xx.dbf‘ size 20m;
2.增加文件的大小
alter tablespace 表空间 ‘path\xx.dbf‘ rsize 20m;
3.设置文件自动增长
alter tablespace 表空间 ‘path\xx.dbf‘
autoextend on next 10m maxsize 500m;

迁移表空间
1确定数据文件所在表空间
select tablespace_name from dba_data_files where file_name=‘path\xx.dbf‘;

2.使表空间脱机
alter tablespace 表空间 offline;

3.移动表空间到指定位置
host move 原路径\xxx.dbf 新路径\xxx.dbf;
4.执行alter tablespace命令
alter tablespace 表空间 rename datafile ‘原路径\xxx.dbf’ to ‘新路径\xxx.dbf‘;

5.使表空间联机
alter tablespace 表空间 online;

显示表空间信息
select tablespace_name from dba_tablespaces;

显示表空间所含数据文件
select file_name,bytes from from dba_data_files where tablespace_name=‘表空间‘;
default tablespace 授权用户访问的表空间名 temporary tablespace tem account unlock;

事务
=============================================
事务提交(会删除保存点，释放锁)
commit;

设置保存点
savepoint 保存点名;

取消部分事务
rollback to 保存点名;

取消全部事务
rollback;

设置只读事务
set transaction read only

sql函数
=============================================
lower(char) 将字符串转化为小写格式

upper(char) 将字符串转化为大写格式

length(char) 返回长度

substr(char m,n) 截取字串

replace（列名 ，‘a‘ ‘b‘）替换字符串

language 语言

db_name 当前数据库名称

host ：数据库所在主机名

session_user 当前操作的用户

select sys_context(‘userenv’，‘db_那么’) from dual;

数据完整性
=============================================
数据遵从逻辑和商业规则可以由约束，触发器，应用程序(过程，函数)实现

1.约束
not null，unique(可以为null),primary key,foreign key,key,check,
sex char(2) default ‘man‘ check(sex(‘man‘,‘woman‘))
references(要关联的表名(字段));

增加约束
alter table 表名 modify 列名 not null;
alter table 表名 add constraint 约束名 unique(列名);
alter table 表名 add constraint 约束名 check(addr in(‘‘);

删除约束
alter table 表名 drop constraint 约束名;（删除主键要带上cascade）

显示用户约束信息
select constraint_name.constraint_type,status,validated
from user_constraints where table_name=‘表名‘;
select column_name,position from user_cons_columns
where constraint_name=‘约束名‘;

列级定义约束
表级定义约束 create table xxx() references

索引
=============================================
(建索引浪费空间和插入效率，提高查询效率)

单列索引
create index 索引名 on 表名(列名)

复合索引
create index 索引名 on 表名(列名1,列名2);(sql语句扫描时从后往前,尽量把能排除多个的列放后面)

查询索引
select index_name,index_type from user_indexes where table_name=‘表名‘;
unique上都有索引

存储过程
=============================================
块-》包-》函数
块-》包-》过程

create procedure 存储过程名 is
begin
--执行部分语句
end;
/
show error

调用存储过程
exec 过程名(参数..)
declear 定义
gigin 执行
exception 错误

create procedure 存储过程名 is
begin
--这是pl/sql编程中的包
dbms_output.put_line(‘hello‘);
end;
/

set serveroutput on打开输出选项
select 列名 into V_列名 from 表名 where 列名=@aa;

java调用存储过程
CallableStatement cs=connection.prepareCall("{call 存储过程(?,?)}");

函数
create funtion 函数名(name varchar) return number 名字 number（7,1）;
begin
--执行部分
end;
/
var abc number;
call 函数() into:abc;

包
create package 包名 is
函数();
过程();
end;
/

触发器:隐含执行的存储过程。必须定义触发的时间和操作。
create trigger
游标 -ref cuisor
declare
定义一个游标类型
type 游标名 is ref cuisor;
定义一个游标变量
游标名1 游标名;
v_列名 vaechar;
begin
open 游标名1 for select 列名 from 表名 where 列名=&xx;
loop
fetch 游标名1 into v_列名;
exit when 游标名1 notfound;
dbms.output.putline(‘v_列名‘);
end loop
end;

创建视图
create or replace view myview as select * from 表名;(with read only)
drop view myview;

其他零散
=============================================
oracle版本=i代表internet，g代表grid
针对电子商务推出的版本-9i, g--grid 针对网格推出的，有10g、11g,当前最新版11g
select查询语句的执行步骤
1.编译(parse)
2.执行(execute)
3.提取数据(fetch)
</其他零散>

oracle服务
=============================================
1.Oracle 11g七个Windows服务功能介绍
-------------------------------------------
①OracleServiceORCL：Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(默认开机启动，必须启动)

②OracleOraDB11g_home1TNSListener：Orace监听服务，服务只有在数据库需要远程访问的时候才需要，如PL/SQL Developer。(默认开机启动，非必须启动)

③OracleJobSchedulerORCL：作业调度（定时器）服务，（默认为禁用，若需开启，需改为手动，非必须启动）

④OracleDBConsoleorcl：oracle数据库控制台服务，访问地址：http://localhost:1158/em。(默认开机启动，非必须启动)

⑤OracleVssWriterORCL：Oracle ORCL VSS Writer Service，Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时
间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。(非必须启动)

⑥OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）

⑦OracleOraDb11g_home1ClrAgent：Oracle数据库.NET扩展服务的一部分。（默认手动，非必须启动）

2.oracle启动批处理
-------------------------------------------
@echo off

echo 确定要启动Oracle 11g 服务吗
pause
echo Starting OracleService, please wait ...

rem 1、Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(默认开机启动，必须启动)
net START OracleServiceORCL

rem 2、Orace监听服务，服务只有在数据库需要远程访问的时候才需要，如PL/SQL Developer。(默认开机启动，非必须启动)
net START OracleOraDB11g_home1TNSListener

rem 3、作业调度（定时器）服务，（默认为禁用，若需开启，需改为手动，非必须启动）
rem net START OracleJobSchedulerORCL

rem 4、oracle数据库控制台服务，访问地址：http://localhost:1158/em。(默认开机启动，非必须启动)
rem net START OracleDBConsoleorcl

rem 5、卷映射拷贝写入服务，VSS(Volume Shadow Copy Service)能够让存储基础设备(比如磁盘，阵列等)创建高保真的时间点映像(非必须启动)
rem net START OracleVssWriterORCL

rem 6、服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（默认开机启动，非必须启动）
rem net START OracleMTSRecoveryService

rem 7、Oracle数据库.NET扩展服务的一部分。（默认手动，非必须启动）
rem net START OracleOraDb11g_home1ClrAgent

pause

3.oracle停止批处理
-------------------------------------------
@echo off

echo 确定要停止Oracle 11g 服务吗
pause
echo Stopping OracleService, please wait ...

rem 1、Oracle核心服务该服务，是数据库启动的基础，只有该服务启动，Oracle数据库才能正常启动。(默认开机启动，必须启动)
net stop OracleServiceORCL

rem 2、Orace监听服务，服务只有在数据库需要远程访问的时候才需要，如PL/SQL Developer。(默认开机启动，非必须启动)
net stop OracleOraDB11g_home1TNSListener

rem 3、作业调度（定时器）服务，（默认为禁用，若需开启，需改为手动，非必须启动）
net stop OracleJobSchedulerORCL

rem 4、oracle数据库控制台服务，访问地址：http://localhost:1158/em。(默认开机启动，非必须启动)
net stop OracleDBConsoleorcl

rem 5、卷映射拷贝写入服务，VSS(Volume Shadow Copy Service)能够让存储基础设备(比如磁盘，阵列等)创建高保真的时间点映像(非必须启动)
net stop OracleVssWriterORCL

rem 6、服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（默认开机启动，非必须启动）
net stop OracleMTSRecoveryService

rem 7、Oracle数据库.NET扩展服务的一部分。（默认手动，非必须启动）
net stop OracleOraDb11g_home1ClrAgent

pause

4.Oracle基本开发中需要启动的服务
-------------------------------------------
对新手来说，要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。
OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用。

注：ORCL是数据库实例名，默认的数据库是ORCL，你可以创建其他的，即OracleService+数据库名。

ORACLE远程连接不上
==============================================================
D:\app\lb\product\11.2.0\dbhome_1\NETWORK\ADMIN
tnsnames.ora
ORCL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
(SERVER = DEDICATED)
)
)

开始- 运行 - 输入“CMD” 确定
在弹出来的窗口中，输入SQLPLUS / AS SYSDBA 回车
然后，用命令修改：
alter user 用户名 identified by "密码";
回车~ 注意冒号~