# Oracle
# sys用户操作
-- 创建用户
create user ESTATE_BINZHOU identified by estate;
-- 修改用户
alter user user1 identified by "";
-- 删除用户（级联删除）
drop user ESTATE_BINZHOU cascade;
-- 授权
grant connect, resource,dba to ESTATE_BINZHOU;
-- 无法删除当前已连接用户时
select username,sid,serial# from v$session where username = 'PFUSER_BINZHOU';
-- 生成删除连接语句
select 'alter system kill session''' || sid || ',' || serial# || ''';' from v$session where username = 'PFUSER_BINZHOU';
-- 远程登录
sqlplus ESTATE_BINZHOU/estate@//192.168.12.88:1521/orcl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 测试连接
tnsping 192.168.12.25:1521/orcl
1
# 导出dmp文件
exp ESTATE_BINZHOU/estate@192.168.12.25:1521/orcl BUFFER=64000 file=D:\export\business.dmp owner=ESTATE_BINZHOU
exp ESTATE_BINZHOU/estate@192.168.12.25:1521/orcl BUFFER=64000 file=D:\export\business.dmp log=D:\export\business.log owner=ESTATE_BINZHOU feedback=1000
exp ESTATE_BINZHOU/estate@192.168.12.25:1521/orcl BUFFER=64000 file=E:\export\business.dmp log=E:\export\business.log owner=ESTATE_BINZHOU feedback=1000
exp PFUSER_BINZHOU/123@192.168.12.25:1521/orcl BUFFER=64000 file=D:\export\workflow.dmp owner=PFUSER_BINZHOU
1
2
3
4
5
6
7
2
3
4
5
6
7
# 导入dmp文件
imp PFUSER_BINZHOU/123@192.168.12.88/orcl file=D:\export\workflow.dmp full=y ignore=y feedback=1000
imp ESTATE_BINZHOU/estate@192.168.12.88/orcl file=D:\export\business.dmp full=y ignore=y
imp ESTATE_BINZHOU/estate@192.168.12.88/orcl file=E:\export\business.dmp full=y ignore=y feedback=1000
imp PFUSER_BINZHOU/123@192.168.12.88/orcl file=E:\export\workflow.dmp full=y ignore=y feedback=1000
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# 删除用户下的表
SELECT 'DROP TABLE '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;
-- 主键导致无法删除时
SELECT 'DROP TABLE '|| table_name || ' cascade CONSTRAINTS;' FROM USER_TABLES ORDER BY TABLE_NAME;
1
2
3
4
2
3
4
# dmp字符集不一致问题
-- 进入服务端查看字符集：
select userenv('language') from dual;
-- 字符集编码是：ZHS16GBK
1
2
3
4
5
2
3
4
5
临时修改环境变量，在命令行中设置NLS_LANG为环境变量，它将覆盖注册表和系统属性中的NLS_LANG的定义。
在Windows下通过set nls_lang来设置，只是Session级别的，关闭cmd窗口后再打开，就又变为原来的设置了。
注意，在这里我们要将环境变量NLS_LANG的字符集编码设置成dmp的字符集编码，这样我们的cmd窗口作为客户端才能够正确编译dmp文件
//查看环境变量
echo %NLS_LANG%
//临时设置环境变量
set nls_lang=AMERICAN_AMERICA.ZHS16GBK
1
2
3
4
2
3
4
# 查看端口占用进程
sudo lsof -i:8443
-- 远程
mstsc
-- windows查看相关进程
netstat -ano | findstr 80 //列出进程极其占用的端口，且包含 80
tasklist | findstr 9268 //据进程号寻找进程名称
-- windows杀进程
netstat -ano | find "8657" //首先用netstat -ano | find “端口号”查出进程号
taskkill -PID 进程号 -F //强制关闭某个进程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# sql脚本
-- 生成按表导出脚本
SELECT 'exp ESTATE_BINZHOU/estate@192.168.12.88/orcl file=D:\export\business\' || table_name || '.dmp owner=ESTATE_BINZHOU tables=(' || table_name || ')' FROM USER_TABLES ORDER BY TABLE_NAME;
-- 查询表空间
select * from dba_data_files where tablespace_name = 'PFUSER_BINZHOU_DATA';
select * from dba_data_files where tablespace_name = 'USERS';
-- E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
-- 创建表空间
create tablespace USERS datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS02.DBF' size 50m autoextend on maxsize 2048m;
ALTER USER PFUSER_BINZHOU QUOTA UNLIMITED ON USERS;
-- 查询用户表空间
select username, default_tablespace from dba_users where username='PFUSER_BINZHOU';
-- PFUSER_BINZHOU PFUSER_BINZHOU_DATA
-- PFUSER_BINZHOU_DATA
-- 修改表空间
alter tablespace PFUSER_BINZHOU rename to PFUSER_BINZHOU_DATA;
alter tablespace PFUSER_BINZHOU rename to users;
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 触发器查询
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND OWNER='ESTATE_BINZHOU';
1
2
3
2
3
# 批量执行计划
-- 查询创建用户及权限脚本
SELECT
ALL_USERS.USERNAME, dba_role_privs.granted_role AS granted_role
FROM ALL_USERS
LEFT JOIN DBA_ROLE_PRIVS
ON ALL_USERS.USERNAME = DBA_ROLE_PRIVS.GRANTEE
where
ALL_USERS.USERNAME like 'ESTATE%'
or
ALL_USERS.USERNAME like 'PFUSER%'
order by SUBSTR（ALL_USERS.USERNAME,8）, ALL_USERS.USERNAME;
-- 生成批量授权语句
select 'grant connect, resource, dba to '|| ALL_USERS.USERNAME || ';'
FROM ALL_USERS
LEFT JOIN DBA_ROLE_PRIVS
ON ALL_USERS.USERNAME = DBA_ROLE_PRIVS.GRANTEE
where
granted_role IS NOT NULL
AND
(ALL_USERS.USERNAME like 'ESTATE%'
or
ALL_USERS.USERNAME like 'PFUSER%')
order by SUBSTR（ALL_USERS.USERNAME,8）, ALL_USERS.USERNAME;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# java脚本
java -jar ./pear-admin-pro-0.1.0-SNAPSHOT.jar > ./log.txt &
(exclude = DataSourceAutoConfiguration.class)
1
2
2