Oracle 相关
数据迁移
空表不能导出
Oracle从11g开始有一个新的特性,当表中从未拥有过数据,那么不分配segment,以节省空间。当使用数据泵导出数据的时候,需要注意,如果表未分配segment,那么无法导出。
-- 单张表
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
数据泵的使用
表空间相关操作
-- 创建数据表空间
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
用户、权限相关
-- 创建用户,指定默认表空间
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
=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,"');")
# 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
# 导入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
递归查询
-- 本条记录的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.查看数据库被锁的表
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文件
select username, profile from dba_users;
查看指定的概要文件密码有效期
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
将密码有效期修改为无限制
alter profile default limit password_life_time unlimited;
解锁被锁定的账户
ALTER USER DXZP_HENAN ACCOUNT UNLOCK;
设置尝试输入次数
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS 10;
或者不限制输入次数
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
字符集编码
查看Oracle数据库的编码:
select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
查看Oracle客户端编码:
select * from nls_instance_parameters where parameter='NLS_LANGUAGE';
常用查询
查询Oracle目录:
select * from dba_directories;
查询数据文件:
select * from dba_data_files;
查询表空间文件:
select username, temporary_tablespace from dba_users;
dba_users查询所有用户信息:
select * from dba_users;
查询所有表空间的大小:
select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
查询空闲表空间的大小:
select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
查询当前用户能访问到的表:
select * from user_tables;
查询Oracle用户表:
select * from user_all_tables;
查询Oracle用户下的视图:
select * from user_views;
查询用户下的函数和存储过程
select * from user_source;
查询当前用户连接:
select * from v$Session;
查询用户下的角色:
select * from user_role_privs;
查询当前用户权限:
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的时候发现无法启动数据库实例,提示信息:
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可以测试与远程数据库连接是否正常
本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!