Oracle常用SQL操作技巧

Oracle常用SQL操作技巧
1.以管理员身份连接到数据库实例
sqlplus "/as sysdba"
2.查看当前连接数据库实例名称、状态、是否启用归档select name,open_mode,log_mode from v$database;
3.查看当前数据库版本
select * from v$version;
4.查看指定数据表的字段结构
desc v$database;
5.本机安装有多个数据库实例
查看已安装好的实例名称，例如下图中的DBSVR001
（如果本机安装多个数据库实例，直接执行sqlplus "/as sysdba"会连接到最后安装的一个实例，那我们想连接到指定的实例怎么办呢？例如要连接的实例为：DBSVR001）
set oracle_sid=DBSVR001
6.以指定用户连接到数据库
sqlplus sdzy_mis/cont@lb_dbsvr001
7.查看当前连接用户有什么角色、什么权限
select * from user_role_privs;
select * from session_privs;
8.查看当前用户的系统权限和表级权限select * from user_sys_privs;
select * from user_tab_privs;
9.获取当前连接Oracle的用户信息
SELECT ERNAME,S.OSUSER,S.MACHINE,S.PROGRAM,S.LOGON_TIME
FROM V$SESSION S
WHERE S.SID=(SELECT DISTINCT SID FROM V$MYSTAT);
10.查看表数据
例如：表名SYS_META_CTVALUE；存在列：CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER SELECT CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
FROM SYS_META_CTVALUE
WHERE CT_ID IN ('SYS_SYSTEMTYPE','SYS_OPERATOR')
ORDER BY CT_ID,KEY_ORDER;
11.创建一个临时表drop01
例如：
原表名SYS_META_CTVALUE；存在列：CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
新表名：DROP01；新列名：CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2 其中列KEY_ORDER2为原值扩展为2位，不足2位前补0
CREATE TABLE DROP01 AS
SELECT CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
,LPAD(TO_CHAR(KEY_ORDER),3,'0') AS KEY_ORDER2
FROM SYS_META_CTVALUE
WHERE CT_ID IN ('SYS_SYSTEMTYPE','SYS_OPERATOR')
ORDER BY CT_ID,KEY_ORDER;
注意：此种生成的新表无主键。

12.在已存在数据表中生成一个索引列
SELECT ROW_NUMBER() OVER(ORDER BY CT_ID,KEY_ORDER) AS TMP01 ,CT_ID,KEY_ORDER,KEY_CODE,KEY_NAME,KEY_ORDER2
FROM DROP01;
13.在已存在数据表中生成一个索引列、一个分类索引列
SELECT ROW_NUMBER() OVER(ORDER BY CT_ID,KEY_ORDER) AS TMP01
,ROW_NUMBER() OVER(PARTITION BY CT_ID ORDER BY CT_ID,KEY_ORDER) AS TMP02 ,CT_ID,KEY_ORDER,KEY_CODE,KEY_NAME,KEY_ORDER2
FROM DROP01;
14.在已存在数据表中生成一个类似主键列（值不唯一列）
SELECT ROW_NUMBER() OVER(ORDER BY CT_ID,KEY_ORDER) AS TMP01
,ROW_NUMBER() OVER(PARTITION BY CT_ID ORDER BY CT_ID,KEY_ORDER) AS TMP02 ,SYS_GUID() AS TMP03
,CT_ID,KEY_ORDER,KEY_CODE,KEY_NAME,KEY_ORDER2
FROM DROP01;
15.生成两个数据表，制作测试数据
表名：DROP11,DROP12
列名：POID,T_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
值列要求：POID列值不能重复
--创建临时表 drop11
CREATE TABLE DROP11 AS
SELECT SYS_GUID() AS
POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
FROM DROP01;
--创建临时表 drop12
CREATE TABLE DROP12 AS
SELECT POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
FROM DROP11;
--11:13,12:13 SUM11:13 + 13=26
INSERT INTO DROP11(POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2) (SELECT POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2 FROM DROP12);
--11:26,12:13 SUM11:26 + 13=39
INSERT INTO DROP12(POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2) (SELECT POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2 FROM DROP11);
--保存数据
COMMIT;
--查看两个表的数据行数
SELECT
(SELECT COUNT(1) FROM DROP11) AS DROP11_COUNT
,(SELECT COUNT(1) FROM DROP12) AS DROP12_COUNT
FROM DUAL;
15.给数据表加主键
alter table DROP11
add constraint DROP11_POID primary key (POID);
16.快速启动网络配置向导
netca
刘兵2012-01-06
11 / 11。