oracle常用总结

最新推荐文章于 2024-08-05 17:42:49 发布

kepa520

最新推荐文章于 2024-08-05 17:42:49 发布

阅读量271

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

53 篇文章 1 订阅

订阅专栏

原文地址：http://blog.csdn.net/makang110/article/details/78892511

1、修改字段名称
ALTER TABLE 表名 rename column 列名 to 新列名注意column是关键字 /*重命名列名*/
2、修改表名
ALTER TABLE 表名 rename to 新表名 /*重命名表名*/
3、select into 报错，用 create table as
4、解决PL/SQL 乱码问题
创建一个名为“NLS_LANG”的系统环境变量，设置其值为“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
5、oracle 查看被锁的对象例如：表
select t2.username, t2.sid, t2.serial#, t2.logon_time, t3.object_name
from v$locked_object t1, v$session t2, user_objects t3
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
order by t2.logon_time
6、通过上边查出被锁的对象，那么释放他
--释放SESSION SQL:
--alter system kill session 'sid, serial#';
--ALTER system kill session '67, 7508';
7、oracle 创建一个与已有表一样的表
create table 新表 as select * from 旧表
8.oracle 同步两张表（或者用一个表的数据更新另一个表）
update test_region set(org_name, parent_org_code) =
(
select A.mc as org_name, a.p_dm as parent_org_code
from wjjs_org_info A
where
A.code = test_region.org_code
)
where exists(select code
from wjjs_org_info A
A.code = test_region.org_code
)
9、oracle 列转行
使用函数wm_concat(列),如果出现乱码，那么用 to_char 函数转换为字符

举例 shopping表
shopping:

-----------------------------------------
u_id goods num
------------------------------------------
1 苹果 2
2 梨子 5
1 西瓜 4
3 葡萄 1
3 香蕉 1
1 橘子 3
=======================
1、想要的结果为:
--------------------------------
u_id goods_sum
--------------------------------

1 苹果,西瓜,橘子
2 梨子
3 葡萄,香蕉
---------------------------------------
实现sql：
select u_id, wmsys.wm_concat(goods) goods_sum
from shopping
group by u_id
----------------------------------------
--------------------------------
=======================
2、想要的结果为:
--------------------------------
u_id goods_sum
---------------------------------
1 苹果(2斤),西瓜(4斤),橘子(3斤)
2 梨子(5斤)
3 葡萄(1斤),香蕉(1斤)
---------------------------------
实现sql：select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum
from shopping
group by u_id
3、如果出现乱码那么使用：wm_concat(to_char(goods)) to_char 转换字符
10.在调用存储过程时，如果在命令行窗口没有执行 "set serveroutput on;" 语句，那么不会打印存储过程返回信息。如果能打印信
息，前提是在存储过程中有输出语句比如：dbms_output.put_line(内容)
11.存储过程在动态创建表或者试图时，解决没有权限错误
在创建存储过程时添加 AUTHID CURRENT_USER ，详见自己的百度收藏
12、oracle timestamp 值
create table test (
create_time TIMESTAMP default SYSTIMESTAMP ,
login_ip VARCHAR2(50)
);
update test set create_time = systimestamp;
select to_char(create_time, 'yyyy-mm-dd hh24:mi:ss.ff') from test;
13、查看回收站中表
select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;
使用drop 语句删除表后，再恢复表
SQL>flashback table 表名 to before drop;或
SQL>flashback table "BIN$b+XkkO1RS5K10uKo9BfmuA==$0" to before drop;
14、oracle 命令行工具
cmd -> sqlplus
提示输入用户名： sys as sysdba
提示输入密码：Dong
如果忘记 sys 密码，那么
1）进入cmd
2）sqlplus / as sysdba
3）alter user sys identified by 新密码;
2、如果是你新建的普通用户登录的话，用户名和密码请重新确认是否正确，如果实在是确认不了的话，使用sqlplus工具按照如下方式重新建一个用户再试

1）进入cmd
2）sqlplus / as sysdba 或者 sqlplus sys/密码 as sysdba
3) create user 用户名 identified by 密码;
4) grant connect,resource to 用户名;
5) conn 用户名/密码
15、oracle 执行sql 脚本
SQL> @E:\赛尔\组织机构\山西省数据\新建文件夹\xxjgsxbhqk_201404.sql
16、oracle 递归查询
select * from table_name
START WITH id='3'
CONNECT BY PRIOR PARENT_id = id
ORDER BY message_time desc
--注意在使用上报sql 递归查询时 CONNECT BY PRIOR PARENT_id = id ，id 与 PARENT_id 先后顺序对查询结果有影响。
如上sql解释从id =3 的记录开始，从该记录的 PARENT_id 等于其他记录的 id 的记录递归查找
反之 id = PARENT_id 说明从该记录的 id 等于其他记录的 PARENT_id 的记录递归查找
17、查找 oracle 版本
select * from v$version;
18、查看 oracle 字符集
select userenv('LANGUAGE') from dual;
19、oracle 导出数据库
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。
不过在上面命令后面加上 compress=y 就可以了

数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)
20、清空数据
说明：truncate 语句会回收数据物理地址
truncate table t_message_notice_bak;
如果truncate 后空间没有释放，那么执行：alter table t_message_notice_bak deallocate UNUSED KEEP 0;

21、查看存储过程是否运行
select name,locks,pins
from v$db_object_cache
where type='PROCEDURE'

22、查看表空间
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
22、查看表空间使用情况
select a.tablespace_name,a.bytes/1024/1024 "sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%" from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc;
23、表空间扩展
第二步：增大所需表空间大小：

alter database datafile '表空间位置'resize 新的尺寸
例如：
alter database datafile '\oracle\oradata\anita_2008.dbf' resize 4000m

对于oracle数据库的表空间，除了用手动增加大小外，还可以增加数据文件等方式扩展表空间大小。

方法一：增加数据文件个数

alter tablespace 表空间名称
add datafile '新的数据文件地址' size 数据文件大小
例如：
alter tablespace ESPS_2008
add datafile '\oracle\oradata\anita_2010.dbf' size 1000m

方法二：设置表空间自动扩展。
alter database datafile '数据文件位置'
autoextend on next 自动扩展大小 maxsize 最大扩展大小
例如：
alter database datafile '\oracle\oradata\anita_2008.dbf'
autoextend on next 100m maxsize 10000m
24、查看sql语句执行计划
sql>EXPLAIN PLAN FOR SELECT SUM(salary) FROM emp2 GROUP BY department_id;
sql>SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
25、更改表的并行度
ALTER TABLE T_TEACHER_BASIC_TEMP PARALLEL 1;
26、查询表占用空间
select sum(bytes)/1024/1024||'MB' from dba_extents where owner='WJJS' and segment_name='T_TEACHER_INSURANCE';

select segment_name , bytes/1048576 MB
from user_segments
order by bytes
27、查询归档模式
select log_mode FROM v$database;
--NOARCHIVELOG 非归档模式
28、查询表空间状态
select tablespace_name,status from dba_tablespaces;
29、查询表索引
select * from user_indexes where table_name='T_TEACHER_OFFICE' ;
30、查询用户缺省表空间
select username,default_tablespace from dba_users;
31、查询表所在表空间
select table_name,TABLESPACE_NAME from all_tables where OWNER='WJJS' table_name='T_TEACHER_BASIC_TEMP';
32、查看索引所在表空间
select tablespace_name from dba_indexes where index_name = 'SYS_C0056417'
33、查询并行执行进程
select * from v$process;
34、oracle 启用并行
1、使用Hint 指定并行度。
2、使用alter session force parallel 设定并行度。
3、使用SQL中引用的表或者索引上设定的并行度，原则上Oracle 使用这些对象中并行度最高的那个值作为当前执行的并行度。
示例：
SQL>Select /*+parallel(t 4) */ count(*) from t;
SQL>Alter table t parallel 4;
SQL>Alter session force parallel query parallel 4;
SQL>alter session force parallel query;
并行度的优先级别从高到低：
Hint->alter session force parallel->表，索引上的设定-> 系统参数
35、oracle 导出\导入数据至文本
新建sql文件 exp_table.sql ,并编辑如下内容：
set line 1000 --设置行的长度
set pagesize 0 --输出不换页
set feedback off --默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功命令行会返回类似：Table created的反馈,off后不显示反馈
set heading off --不显示表头信息
set trimspool on --如果trimspool设置为on，将移除spool文件中的尾部空
set trims on --去掉空字符
set echo off;　　　　 --显示start启动的脚本中的每个sql命令，缺省为on
set colsep '|' --设置分隔符
set termout off --不在屏幕上显示结果
spool db1.txt --记录数据到db1.txt
select object_id,object_name from all_objects; --导出数据语句
spool off --收集完毕
--exit
执行该文件：@exp_table.sql

sqlldr是通过一个control文件设定后，从文本导入数据

建立一张测试表

SQL> create table tb_sqlldr (id number,name varchar2(50));

Table created.
建立一个control文件
vi tb_sqlldr.ctl

load data
infile 'db1.txt' --数据来源文本
append into table tb_sqlldr --数据导入到表tb_sqldr中，导入方式为追加，如果想覆盖
fields terminated by "|" --4、字段终止于X'09'，是一个制表符(tab)
(id,name) --定义对应的字段名称，注意顺序
导入数据分成四种模式，可以根据需求选择：

APPEND // 原先的表有数据就加在后面
INSERT // 装载空表如果原先的表有数据 sqlloader会停止默认值
REPLACE // 原先的表有数据原先的数据会全部删除
TRUNCATE // 指定的内容和replace的相同会用truncate语句删除现存数据