Oracle 相关
valten Lv4
 2019-12-13 23:18:43    

数据迁移

空表不能导出

Oracle从11g开始有一个新的特性，当表中从未拥有过数据，那么不分配segment，以节省空间。当使用数据泵导出数据的时候，需要注意，如果表未分配segment，那么无法导出。

1
2
3
4
5
6
-- 单张表
alter table sys_log allocate extent;  
-- 查询出所有数据量为零的表
select table_name from user_tables where num_rows=0;

select'alter table '||table_name||' allocate extent;'from user_tables where num_rows=0;

执行生成的sql

数据泵的使用

数据泵的使用

表空间相关操作

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- 创建数据表空间
CREATE TABLESPACE TBS_HCZZZYV2_DATA DATAFILE
'/u01/app/oracle/oradata/hisign/tbs_hczzzy_data_01.dbf' SIZE 20000m
--AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED
LOGGING ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

-- 扩展表空间
ALTER TABLESPACE TBS_HCZZZYV2_DATA
ADD DATAFILE '/u01/app/oracle/oradata/hisign/tbs_hczzzy_data_02.dbf'
SIZE 20000m AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED;

-- 创建索引表空间
CREATE TABLESPACE TBS_HCZZYWV2_INDEX DATAFILE 
'/u01/app/oracle/oradata/hisign/tbs_hczzyw_index_01.dbf' SIZE 20000m 
--AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED
LOGGING ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

-- 扩展表空间
ALTER TABLESPACE TBS_HCZZYWV2_INDEX
ADD DATAFILE '/u01/app/oracle/oradata/hisign/tbs_hczzyw_index_02.dbf'
SIZE 20000m 
--AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TBS_HCZZYWV2_TMP TEMPFILE 
'/u01/app/oracle/oradata/hisign/tbs_hczzyw_tmp_01.dbf' SIZE 20000m 
--AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 扩展表空间
ALTER TABLESPACE TBS_HCZZYWV2_TMP
ADD TEMPFILE '/u01/app/oracle/oradata/hisign/tbs_hczzyw_tmp_02.dbf'
SIZE 20000m 
--AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED;

-- 删除指定表空间
drop tablespace TBS_MPP_SYSTEM including contents and datafiles;

-- 创建用户，指定默认表空间
CREATE USER ywv2 IDENTIFIED BY ywv2 DEFAULT TABLESPACE TBS_HCZZYWV2_DATA
TEMPORARY TABLESPACE TBS_HCZZYWV2_TMP
PROFILE DEFAULT ACCOUNT UNLOCK;

-- 查询表空间使用情况
select a.tablespace_name as name,
       total,
       free,
       total - free as used,
       round(free / sum(total) over(PARTITION BY a.tablespace_name), 4) * 100 freeRate,
       round((total - free) / sum(total) over(PARTITION BY a.tablespace_name), 4) * 100 usedRate
  from (select tablespace_name, sum(bytes) / 1024 / 1024 as total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 as free
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by a.tablespace_name

用户、权限相关

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
28
-- 创建用户，指定默认表空间
CREATE USER zyv2 IDENTIFIED BY zyv2 DEFAULT TABLESPACE TBS_HCZZZYV2_DATA
TEMPORARY TABLESPACE TBS_HCZZZYV2_TMP
PROFILE DEFAULT ACCOUNT UNLOCK;

--修改用户密码
sqlplus / as sysdba
ALTER USER XXX IDENTIFIED BY NEWPWD;

--给用户赋权
GRANT CONNECT,RESOURCE,CREATE DATABASE LINK,CREATE VIEW,CREATE SYNONYM TO zyv2;

-- 创建用户并授权
CREATE USER CSIFMIS IDENTIFIED BY CSIFMIS;
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO CSIFMIS;

-- oracle授权表权限给用户
命令：grant xxx权限 on Table to USER
grant select,insert,update,delete,all on 表名 to 用户名
例如：将test表的查询权限赋予tom这个用户
grant select on test to tom
-----------------------------------
-- 授权
grant select on tb_xw_jjxx to h2yw;
select * from user_tables;
select 'grant select on '||table_name||' to h2yw' from user_tables;
-- 收回授权
revoke select on tb_xw_jjxx from h2yw;

Excel批量生成sql

1
2
=CONCATENATE("update SIMPLE_CASE c set c.occurrcode = '",J2,"' WHERE C.JJBH='",A2,"';")
=CONCATENATE("insert into operate_type values(sys_guid(),'",B2,"' ,'",C2,"','",D2,"' ,'",E2,"');")
1
2
3
4
5
# sql
insert into sys_dict (id, dict_level, dict_key, parent_key, root_key, dict_value1, remark, del) 
              values (sys_guid(), '1', '",A254,"', 'ZDLXDM', 'ZDLXDM', '",B254,"', '",C254,"', '0');
# excel函数
=CONCATENATE("")

命令行导入导出dmp

1
2
3
4
5
6
7
8
9
10
11
12
13
# 导入dmp
imp CSIFMIS/CSIFMIS@192.168.41.73：1521/csifmis \
file=C:\csifmis.dmp log=C:\csifmis.log full=y ignore=y

# 导出dmp
exp CSIFMIS/CSIFMIS@192.168.41.73：1521/csifmis \
file=C:\csifmis.dmp log=C:\csifmis.log buffer=65536 full=y
# 导出指定表
exp CSIFMIS/CSIFMIS@192.168.41.73：1521/csifmis \
file=C:\csifmis.dmp tables=(table1,table2)
# rows=n 只导出表结构不导出数据
exp CSIFMIS/CSIFMIS@192.168.41.73：1521/csifmis \
file=C:\csifmis.dmp log=C:\csifmis.log rows=n

递归查询

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 本条记录的id是下一条记录的父id,遍历子节点
SELECT T.CID, T.CNAME, T.PARENT_ID, LEVEL
 FROM Z_ORG T
START WITH T.CNAME = '加利福尼亚州'
CONNECT BY PRIOR T.CID = T.PARENT_ID
ORDER BY LEVEL ASC

-- 本条记录的父id是下一条记录的id,遍历根节点
SELECT T.CID, T.CNAME, T.PARENT_ID, LEVEL
 FROM Z_ORG T
START WITH T.CNAME = '加利福尼亚州'
CONNECT BY PRIOR T.PARENT_ID = T.CID
ORDER BY LEVEL ASC

start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历;prior跟子结点列subid放在一起，则往叶子结点方向遍历，

parentid、subid 两列谁放在“=”前都无所谓，关键是prior跟谁在一起。

order by 子句：排序，不用多说。

锁表

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
28
29
30
31
-- 1.查看数据库被锁的表
select a.object_name,
      b.session_id,
      c.serial#,
      c.program,
      c.username,
      c.command,
      c.machine,
      c.lockwait
 from all_objects a, v$locked_object b, v$session c
where a.object_id = b.object_id
  and c.sid = b.session_id;

-- 2.查看表被锁的原因
select l.session_id sid,      
      s.serial#,      
      l.locked_mode,      
      l.oracle_username,      
      s.user#,      
      l.os_user_name,      
      s.machine,      
      s.terminal,      
      a.sql_text,      
      a.action
 from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid      
  and s.prev_sql_addr = a.address
order by sid, s.serial#;

-- 3.解除被锁定的表
alter system kill session 'SID,SERIAL#';

用户过期设置

查看要过期用户使用的profile文件

1
select username, profile from dba_users；

查看指定的概要文件密码有效期

1
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME'；

将密码有效期修改为无限制

1
alter profile default limit password_life_time unlimited；

解锁被锁定的账户

1
ALTER USER DXZP_HENAN ACCOUNT UNLOCK;

设置尝试输入次数

1
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS 10;

或者不限制输入次数

1
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

字符集编码

查看Oracle数据库的编码：

1
select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

查看Oracle客户端编码：

1
select * from nls_instance_parameters where parameter='NLS_LANGUAGE';

常用查询

查询Oracle目录：
1
select * from dba_directories；
查询数据文件：
1
select * from dba_data_files；
查询表空间文件：
1
select username, temporary_tablespace from dba_users；
dba_users查询所有用户信息：
1
select * from dba_users;
查询所有表空间的大小：
1
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
查询空闲表空间的大小：
1
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
查询当前用户能访问到的表：
1
select * from user_tables；
查询Oracle用户表：
1
select * from user_all_tables;
查询Oracle用户下的视图：
1
select * from user_views;
查询用户下的函数和存储过程
1
select * from user_source;
查询当前用户连接：
1
select * from v$Session；
查询用户下的角色：
1
select * from user_role_privs；
查询当前用户权限：
1
select * from session_privs;

ORA异常

ORA-12514: TNS: no listener

问题描述：用pl/sql客户端登陆远程Oracle数据库的时候，提示。在服务器登陆是可以的，但是远程客户端无法登陆。

解决方案：将服务端的listener.ora中的HOST=127.0.0.1改为HOST=计算机名称，重启listener服务

ORA-00119 ORA-00230

在一次服务器出问题，重启之后，启动Oracle的时候发现无法启动数据库实例，提示信息：

1
2
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address (ADDRESS=(PROTOCOL=TCP)(HOST=orcl11g)(PORT=1521))

查找问题发现是由于hosts修改了，名称与hostname及network配置不一致，修改hostname及network，改为与hosts中一致，问题解决。

ORA-12535：TNS operation timed out tips

原因：请求操作在超时时间范围内未完成

使用tnsping <servicename> 来确定：

  • 验证名字解析，网络服务名；

  • 远程的listener是否已经启动；

tnsping可以测试与远程数据库连接是否正常

  • 本文标题：Oracle 相关
  • 本文作者：valten
  • 创建时间：2019-12-13 23:18:43
  • 本文链接：https://valtenhyl.github.io/数据库/Oracle/oracle-related/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
 评论