Oracle操作语句

Oracle 常用操作语句

启动数据库

启动服务

su - oracle
sqlplus /nolog

conn /as sysdba

# 启动
startup

# 关闭
shutdown

启动监听

需要在数据库服务启动完成后退出 >sql
到oracle安装目录下运行

#启动监听
lsnrctl start

关闭监听
lsnrctl stop

链接数据库

创建数据库

导入数据

导出数据

自动备份

需要使用Oracle账号进行操作
查看oracle用户名是否
cat /etc/passwd

su - oralce

查看oralce用户组
ls -l /home

首先创建备份目录并且将目录的用户改成Oracle的账号

mkdir -p /bak/data
mkdir -p /bak/bin
chown -R oracle:dba /bak
cd /bin
vim test.sh
chmod 755 test.sh

test.sh

#!/bin/bash
date=`date +%Y%m%d%H%M`
days=10  #保留天数
orsid=orcl
orowner=TEST #执行备份的用户
bakuser=TEST #备份的用户
bakpass=test #执行备份的用户密码
bakdir=/bak/data  #备份路径
bakname=test #备份文件名
bakdata=$bakname"_"$date.dmp #导出数据文件
baklog=$bakname"_"$date.log #导出日志文件
ordatabak=$bakname"_"$date.tar.gz #打包压缩文件
cd $bakdir
mkdir -p $bakname
cd $bakname
exp $bakuser/$bakpass@$orsid grants=y owner=$orowner file=$bakdir/$bakname/$bakdata log=$bakdir/$bakname/$baklog
tar -zcvf $ordatabak $bakdata  $baklog # 打包压缩
find $bakdir/$bakname  -type f -name "*.log" -exec rm {} \; #删除log文件
find $bakdir/$bakname  -type f -name "*.dmp" -exec rm {} \; #删除dmp文件
find $bakdir/$bakname  -type f -name "*.tar.gz" -mtime +$days -exec rm -rf {} \; # 删除$days=10天前的压缩文件

crontab -e

-e 3分钟内的计划都不会执行 如果想看到执行效果需要设置到3分钟后

#每周1和周3的2点1分 使用Oracle账号执行 /bak/bin/test.sh 命令进行备份
1 2 * * 1,3 oracle /bak/bin/test.sh

重启crond
service crond restart

1、查找表的所有索引（包括索引名，类型，构成列）：

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表

2、查找表的主键（包括名称，构成列）：

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表

3、查找表的唯一性约束（包括名称，构成列）：

select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表

4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：

select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表

查询外键约束的列名：

select * from user_cons_columns cl where cl.constraint_name = 外键名称

查询引用表的键的列名：

select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名

5、查询表的所有列及其属性

select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表

6 获取多个序列

获取10条SEQ_的序列生成id

select SEQ_.nextval from (select 1 from all_objects where rownum<=10)

all_objects为系统表

7 组合分析

分组后根据某个字段排序取第一(或最后)条的某个值

例子:

--表结构
create table WM_STOCK
(
    MAINID                NUMBER not null primary key,
    WAREHOUSE_ID          VARCHAR2(50),
    MATERIAL_ID           VARCHAR2(50),
    UNIT_PRICE            NUMBER(12,2),
    ACTUAL_COUNT          NUMBER(12,2),
)
--数据
INSERT INTO WM_STOCK (MAINID, MATERIAL_ID, WAREHOUSE_ID, UNIT_PRICE, ACTUAL_COUNT) VALUES ('1', '1004849', '205', 6.44, 1.00);
INSERT INTO WM_STOCK (MAINID, MATERIAL_ID, WAREHOUSE_ID, UNIT_PRICE, ACTUAL_COUNT) VALUES ('2', '1004849', '205', 6.58, 59.00);
INSERT INTO WM_STOCK (MAINID, MATERIAL_ID, WAREHOUSE_ID, UNIT_PRICE, ACTUAL_COUNT) VALUES ('3', '1008', '205', 5.00, 37.00);
INSERT INTO WM_STOCK (MAINID, MATERIAL_ID, WAREHOUSE_ID, UNIT_PRICE, ACTUAL_COUNT) VALUES ('4', '1008', '205', 6.50, 50.00);
INSERT INTO WM_STOCK (MAINID, MATERIAL_ID, WAREHOUSE_ID, UNIT_PRICE, ACTUAL_COUNT) VALUES ('5', '1009', '205', 1.20, 330.00);
INSERT INTO WM_STOCK (MAINID, MATERIAL_ID, WAREHOUSE_ID, UNIT_PRICE, ACTUAL_COUNT) VALUES ('6', '1009', '205', 2.80, 78.00);

SELECT 
--对MATERIAL_ID, WAREHOUSE_ID 分组
distinct MATERIAL_ID,
WAREHOUSE_ID,
--取根据 MATERIAL_ID, WAREHOUSE_ID 分组后 MAINID 排序后 第一个UNIT_PRICE的值
SUM(ACTUAL_COUNT) over (partition by MATERIAL_ID, WAREHOUSE_ID) SUM_ACTUAL_COUNT --取根据 MATERIAL_ID, WAREHOUSE_ID 分组后 ACTUAL_COUNT的合计
FIRST_VALUE(UNIT_PRICE)over ( partition by MATERIAL_ID, WAREHOUSE_ID order by MAINID asc ) REFER_UNIT_PRICE,
FROM WM_STOCK
WHERE ACTUAL_COUNT > 0
ORDER BY MATERIAL_ID, WAREHOUSE_ID;

if exists用法

1 判断数据库是否存在
if exists (select * from sys.databases where name = ’数据库名’)  
  drop database [数据库名]
2 判断表是否存在
if exists (select * from sysobjects where id = object_id(N’[表名]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)  
  drop table [表名]
3 判断存储过程是否存在
if exists (select * from sysobjects where id = object_id(N’[存储过程名]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)  
  drop procedure [存储过程名]
4 判断临时表是否存在
if object_id(’tempdb..#临时表名’) is not null    
  drop table #临时表名
5 判断视图是否存在
--SQL Server 2000   
IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’  
--SQL Server 2005   
IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’  --SQL Server 2000
IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’
--SQL Server 2005
IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’6 判断函数是否存在
--  判断要创建的函数名是否存在    
  if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[函数名]’) and xtype in (N’FN’, N’IF’, N’TF’))    
  drop function [dbo].[函数名]    --  判断要创建的函数名是否存在  
7 获取用户创建的对象信息 
SELECT [name],[id],crdate FROM sysobjects where xtype=’U’  
/*  
xtype 的表示参数类型，通常包括如下这些  
C = CHECK 约束  
D = 默认值或 DEFAULT 约束  
F = FOREIGN KEY 约束  
L = 日志  
FN = 标量函数  
IF = 内嵌表函数  
P = 存储过程  
PK = PRIMARY KEY 约束（类型是 K）  
RF = 复制筛选存储过程  
S = 系统表  
TF = 表函数  
TR = 触发器  
U = 用户表  
UQ = UNIQUE 约束（类型是 K）  
V = 视图  
X = 扩展存储过程*/
8 判断列是否存在
if exists(select * from syscolumns where id=object_id(’表名’) and name=’列名’)  
  alter table 表名 drop column 列名9 判断列是否自增列
if columnproperty(object_id(’table’),’col’,’IsIdentity’)=1  
  print ’自增列’  
else  
  print ’不是自增列’  
  
10 判断表中是否存在索引
if exists(select * from sysindexes where id=object_id(’表名’) and name=’索引名’)    
  print  ’存在’    
else    
  print  ’不存在11 查看数据库中对象
SELECT * FROM sys.sysobjects WHERE name=’对象名’