a.服务器配置
1)启动 "OracleOraDb11g_home1TNSListener" 服务
2)启动 "OracleServiceORCL" 服务
3)提供连接参数
地址:127.0.0.1
端口:1521
服务吗:ORCL
用户名:system
密码:admin
b.客户端配置
方案1:
Oracle自带工具 (推荐->安全)
Oracle目录/配置和移植工具/Net Manager
方案2:
修改配置文件
Oracle安装路径:
一般为路径为
盘符:\app\用户名\product\11.2.0\dbhome_1\NETWORK\ADMIN\
C:\OracleDBApps\product\11.2.0\dbhome_1\NETWORK\ADMIN\
listener.ora -> 服务器侦听配置
tnsnames.ora -> 网络服务名配置
JDBC连接参数
驱动类:
oracle.jdbc.OracleDriver
链接地址:
jdbc:oracle:thin:@主机地址:端口号:服务名
jdbc:oracle:thin:@localhost:1521:ORCL
Oracle备份
1)技巧
备份->
create table tablename_time as select * from tablename
还原->
truncate table tablename;
insert into tablename select * from tablename_time;
commit;
缺点:备份表不存在主外键约束
2)备份
模式:a.数据库 b.用户 c.对象表
导出:exp
导入:imp
注意:
Oracle命令子啊DOS环境执行导入和导出的用户可以不同,但不推荐
在导入之前需要进行初始化(清除对应的数据)
对象表 -> 要求移除表
用户 -> 要求级联删除用户 并创建一个同名用户 且授予权限
原因:备份文件保存的是SQL脚本
Oracle锁
规则
a.在Oracle环境中,当执行DML(INSERT UPDATE DELETE)语句时,所有影响的资源会自动添加隐式锁,
保证会话独占模式。
b.在Oracel环境中,操作结束的标示是事务(COMMIT ROLLBACK)
c.在Oracle环境中,DQL语句只在查询过程中加锁,查询完毕后自动解除。其他语句执行完成后自动提交。
分类
a.锁表现形式
1)隐式锁 -> 参考规则a
2)显示锁 -> select * from [表] where {条件} for update wait [时间 单位:秒];
b.锁作用范围
1)行锁 -> 参考显示锁
2)表锁 -> lock table [表] in share|exclusive mode;
c.锁共存性
1)共享锁 share -> 锁可以共存 如果要操作 必须解除所有共享锁
2)排它锁 exclusive -> 只能允许单个锁
d.锁是否作为
1)乐观锁 -> 作为(否定)
2)悲观锁 -> 不作为
死锁现象(锁的互斥)
1)A会话 删除id=1的数据 A锁1
2)B会话 删除id=2的数据 B锁2
3)A会话 删除id=2的数据 A卡住
4)B会话 删除id=1的数据 B卡住
铺垫->业务操作一般通过过程(脚本块)实现
package top.baoit.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Test {
static String drives = "oracle.jdbc.OracleDriver";
static String urls = "jdbc:oracle:thin:@localhost:1521:ORCL";
static String username = "system";
static String password = "admin";
protected static Connection connection = null;
public static Connection getConnection() {
try {
Class.forName(drives);
connection = DriverManager.getConnection(urls, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void main(String[] agrs) {
System.out.println(getConnection());
}
}
附件:Oracle驱动包(ojdbc14.jar)[${安装目录}$\product\11.2.0\dbhome_1\jdbc\lib]
SQL Plus环境下常用练习
sys as sysdba;
conn sys as sysdba
show user;
alter user username account unlock;
alter user scott account unlock;
disconnect
disconn
username/password
system/admin
conn username/password
conn system/admin
show user;
create tablespace tablespace_name datafile 'path' size [32k~128G] autoextend on/off;
create tablespace test datafile 'c:/test.dbf' size 1m autoextend on;
create user username identified by password default tablespace tablespace_name ;
create user lichee identified by admin default tablespace test;
grant connect,resource to username;
grant connect,resource to lichee;
disconnect
disconn
username/password
lichee/admin
conn username/password
conn lichee/admin
show user;
set linesize length;
set linesize 100;
set pagesize length;
set pagesize 100;
create table 表名(
field1 field1 Type(length) primary key not null,
field2 field2 Type(length) unique,
......
);
drop table tb_test;
create table tb_test(
tid int primary key not null,
tname varchar(20) unique
);
desc tablename;
desc tb_test;
insert into tablename values(field1,field1,...)
insert into tb_test values(1,'lichee');
select * from tablename;
select * from tb_test;
grant select,insert on username1.tb_test to username2;
grant select,insert on lichee.tb_test to scott;
disconnect
disconn
username/password
scott/admin
conn username/password
conn scott/admin
show user;
insert into tb_test values(2,'scott');
select * from username.tablename;
select * from lichee.tb_test;
grant dba to username
grant dba to lichee;
alter table tablename add field3 Type(length);
alter table tb_test add time varchar2(233);
alter table tablename drop column field3 ;
alter table tb_test drop column time;
alter table tablename add field3 Type(length);
alter table tb_test add ttime varchar2(233);
alter table tablename add constraint Constraint_name Constraint(field1,field2);
alter table tb_test add constraint pk unique(ttname);
alter profile dafault limit failed_login_attempts unlimited;
alter user 用户名 account lock;
drop tablespace tablespace_name;
drop tablespace tablespace_name including contents;
drop tablespace tablespace_name including datafiles;
drop tablespace tablespace_name including contents and datafiles;
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
drop user username;
select * from role_sys_privs;
select * from user_users;
select * from user_role_privs;
select username,default_tablespace form dba_user;
select * from user_source
select * from session_privs
select table_name from user_tables;
select table_name from all_tables;
select * from all_users;
select * from user_users;
select * from dba_sys_privs;
select * from user_sys_privs;
select * from all_tab_privs;
select * from dba_roles;
select * from user_role_privs;
alter profile dafault limit failed_login_attempts unlimited;