1. linux 下登录数据 执行sql
su - oracle
<切换到oracle账号： 加- 使用全新的环境变量，不加-使用切换前用户的环境变量>
sqlplus /nolog
<进入sqlplus 程序>
conn sys/sys as sysdba
<使用具体账号密码，账号类型链接数据库>
2.查询指定时间内执行过多sql记录
select t.SQL_TEXT, t.FIRST_LOAD_TIME,t.ELAPSED_TIME from v$sqlarea t where TO_DATE(t.FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS')>= TO_DATE('2019-06-24 20:40:00','YYYY-MM-DD HH24:MI:SS') AND TO_DATE(t.FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2019-06-24 20:42:00','YYYY-MM-DD HH24:MI:SS') ORDER BY t.FIRST_LOAD_TIMe
<查询三个字段的结果集，按顺序：具体执行的sql、第一次加载sql的时间、sql执行消耗时间>
A.查询链接信息
select username,count(username) from v$session where username is not null group by username;
3.Oracle对表进行操作
1、创建表
create table 表名(
字段名 VARCHAR2(36 CHAR) not null,
字段名 VARCHAR2(200 CHAR)
)
tablespace 表空间名
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
2、修改字段、字段类型
alter table tableName rename column oldCName to newCName; -- 修改字段名
alter table tableName modify (cloumnName 数据类型); -- 修改数据类型
3、添加表、字段注释
comment on table 表名 is '表的注释信息';
comment on column 表名.字段名 is '字段的注释信息';
4、添加索引
create index 索引名 on 表名( 字段名）
5、修改字段长度
alter table 表名 modify 列名 数据类型；
alter table bl_yhsz modify zcmc varchar2(120);
5、查询表空间使用率
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
6、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
7、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
8、统计表空间试用率
细分表文件
SELECT a.tablespace_name "tablespace_name", total "tablespace_namesize", free "tablespace_namefreesize", (total - free) "tablespace_name_use", total / (1024 * 1024 * 1024) "tablespace_namesize(G)", free / (1024 * 1024 * 1024) "tablespace_namefreesize(G)", (total - free) / (1024 * 1024 * 1024) "tablespace_name_use(G)", round((total - free) / total, 4) * 100 "use %",b.FILE_NAME FROM (SELECT tablespace_name, SUM(bytes) free ,file_id FROM dba_free_space GROUP BY tablespace_name,file_id) a, (SELECT tablespace_name, SUM(bytes) total,file_id,FILE_NAME FROM dba_data_files GROUP BY tablespace_name,file_id,FILE_NAME) b WHERE a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and b.tablespace_name = 'EKP' ;
统计所有表文件
SELECT a.tablespace_name "tablespace_name", total "tablespace_namesize", free "tablespace_namefreesize", (total - free) "tablespace_name_use", total / (1024 * 1024 * 1024) "tablespace_namesize(G)", free / (1024 * 1024 * 1024) "tablespace_namefreesize(G)", (total - free) / (1024 * 1024 * 1024) "tablespace_name_use(G)", round((total - free) / total, 4) * 100 "use %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name and b.tablespace_name = 'EKP'
9、添加表空间
创建表空间
CREATE TABLESPACE UATDBEKP LOGGING DATAFILE '/oracle/app/oracle/oradata/uat dbekp/uatdbekp.dbf' SIZE 20480M AUTOEXTEND ON NEXT 100M MAXSIZE unlimited;
修改表空间
alter tablespace EKP add datafile '/oracle/app/oracle/oradata/ekp/ekp01.dbf' size 20480M AUTOEXTEND ON NEXT 100M maxsize unlimited ;
创建用户
create user uatdbekp identified by uatdbekp default tablespace uatdbekp;
授予登录权限
grant create session,resource to uatdbekp
10、数据库备份还原 expdp\impdp
备份
cd /home/oracle
mkdir ekpbak
chmod 777 ekpbak
create directory ekpbak as '/home/oracle/ekpbak'; -- 登录oracle创建映射目录
grant read,write on directory ekpbak to ekp; -- 给目录授权
expdp ekp/ekp directory=ekpbak dumpfile=ekp.dmp tablespaces=EKP --备份
expdp ekp/ABCabc123 directory=ekpbak dumpfile=ekp.dmp tablespaces=EKP
expdp uatdbekp/uatdbekp directory=uatbak dumpfile=ekp.dmp tablespaces=uatdbekp exclude=table:\"LIKE \'%_BAK\'\",table:\"LIKE \'%LOGS\'\" --备份
还原
create directory IMPDIR as '/home/oracle/impdir'; -- 登录oracle创建映射目录
grant read,write on directory IMPDIR to ekp; -- 给目录授权
impdp ekp/ekp directory=IMPDIR table_exists_action=replace dumpfile=ekp.dmp logfile=impdp.log --还原
impdp uatdbekp/uatdbekp directory=UATIMPDIR table_exists_action=replace remap_schema=EKP:UATDBEKP remap_tablespace=EKP:UATDBEKP dumpfile=ekp.dmp logfile=impdp.log
table_exists_action：
skip 是如果已存在表，则跳过并处理下一个对象；
append是为表增加数据;
truncate是截断表，然后为其增加新数据；
replace是删除已存在表，重新建表并追加数据;
查询 ORACLE_SID值
echo $ORACLE_SID
修改 ORACLE_SID值
export ORACLE_SID=UATDBEKP
重启数据库
（1） 以oracle身份登录数据库，命令：su – oracle
（2） 进入Sqlplus控制台，命令：sqlplus /nolog
（3） 以系统管理员登录，命令：connect / as sysdba
（4） 启动数据库，命令：startup
（5） 如果是关闭数据库，命令：shutdown immediate
（6） 退出sqlplus控制台，命令：exit
（7） 进入监听器控制台，命令：lsnrctl
（8） 启动监听器，（如果已经启动就无需管了）命令：start
（9） 退出监听器控制台，命令：exit
（10） 重启数据库结束
查询最慢的sql
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
查询外键关联的表信息
select aaa.owner "外键库",
aaa.table_name "外键表",
substr(c.column_name, 1, 127)"外键列",
bb.owner "主键库",
bb.table_name "主键表",
substr(d.column_name, 1, 127) "主键列"
from user_constraints aaa,
user_constraints bb,
user_cons_columns c,
user_cons_columns d
where aaa.r_constraint_name = bb.constraint_name
and aaa.constraint_type = 'R'
and bb.constraint_type = 'P'
and aaa.r_owner = bb.owner
and aaa.constraint_name = c.constraint_name
and bb.constraint_name = d.constraint_name
and aaa.owner = c.owner
and aaa.table_name = c.table_name
and bb.owner = d.owner
and bb.table_name = d.table_name
and bb.constraint_name='SYS_C0023998'
merge更新
merge into SYS_ORG_ELEMENT_BACK070603 v
using (select
FD_ID,
FD_ORG_TYPE,
FD_NAME,
FD_NAME_PINYIN,
FD_NAME_SIMPLE_PINYIN,
FD_ORDER,
FD_NO,
FD_KEYWORD,
FD_IS_AVAILABLE,
FD_IS_ABANDON,
FD_IS_BUSINESS,
FD_IMPORT_INFO,
FD_FLAG_DELETED,
FD_LDAP_DN,
FD_MEMO,
FD_HIERARCHY_ID,
FD_CREATE_TIME,
FD_ALTER_TIME,
FD_ORG_EMAIL,
FD_PERSONS_NUMBER,
FD_PRE_DEPT_ID,
FD_PRE_POST_IDS,
FD_THIS_LEADERID,
FD_SUPER_LEADERID,
FD_PARENTORGID,
FD_PARENTID,
FD_CATEID,
AUTH_READER_FLAG,
FROM_CODE,
PARENT_FROM_CODE,
MANAGE_FROM_CODE,
BRANCHES_FROM_CODE,
DEPT_FROM_CODE,
PERSON_FROM_CODE,
FD_DEPT_LVL,
FD_POSN_LVL
from SYS_ORG_ELEMENT) b
on (v.FD_ID = b.FD_ID)
when matched then
update set
v.FD_ORG_TYPE = b.FD_ORG_TYPE,
v.FD_NAME = b.FD_NAME,
v.FD_NAME_PINYIN= b.FD_NAME_PINYIN,
v.FD_NAME_SIMPLE_PINYIN= b.FD_NAME_SIMPLE_PINYIN,
v.FD_ORDER= b.FD_ORDER,
v.FD_NO= b.FD_NO,
v.FD_KEYWORD= b.FD_KEYWORD,
v.FD_IS_AVAILABLE= b.FD_IS_AVAILABLE,
v.FD_IS_ABANDON= b.FD_IS_ABANDON,
v.FD_IS_BUSINESS= b.FD_IS_BUSINESS,
v.FD_IMPORT_INFO= b.FD_IMPORT_INFO,
v.FD_FLAG_DELETED= b.FD_FLAG_DELETED,
v.FD_LDAP_DN= b.FD_LDAP_DN,
v.FD_MEMO= b.FD_MEMO,
v.FD_HIERARCHY_ID= b.FD_HIERARCHY_ID,
v.FD_CREATE_TIME= b.FD_CREATE_TIME,
v.FD_ALTER_TIME= b.FD_ALTER_TIME,
v.FD_ORG_EMAIL= b.FD_ORG_EMAIL,
v.FD_PERSONS_NUMBER= b.FD_PERSONS_NUMBER,
v.FD_PRE_DEPT_ID= b.FD_PRE_DEPT_ID,
v.FD_PRE_POST_IDS= b.FD_PRE_POST_IDS,
v.FD_THIS_LEADERID= b.FD_THIS_LEADERID,
v.FD_SUPER_LEADERID= b.FD_SUPER_LEADERID,
v.FD_PARENTORGID= b.FD_PARENTORGID,
v.FD_PARENTID= b.FD_PARENTID,
v.FD_CATEID= b.FD_CATEID,
v.AUTH_READER_FLAG= b.AUTH_READER_FLAG,
v.FROM_CODE= b.FROM_CODE,
v.PARENT_FROM_CODE= b.PARENT_FROM_CODE,
v.MANAGE_FROM_CODE= b.MANAGE_FROM_CODE,
v.BRANCHES_FROM_CODE= b.BRANCHES_FROM_CODE,
v.DEPT_FROM_CODE= b.DEPT_FROM_CODE,
v.PERSON_FROM_CODE= b.PERSON_FROM_CODE,
v.FD_DEPT_LVL= b.FD_DEPT_LVL,
v.FD_POSN_LVL= b.FD_POSN_LVL
when not matched then
insert (
v.FD_ID,
v.FD_ORG_TYPE,
v.FD_NAME,
v.FD_NAME_PINYIN,
v.FD_NAME_SIMPLE_PINYIN,
v.FD_ORDER,
v.FD_NO,
v.FD_KEYWORD,
v.FD_IS_AVAILABLE,
v.FD_IS_ABANDON,
v.FD_IS_BUSINESS,
v.FD_IMPORT_INFO,
v.FD_FLAG_DELETED,
v.FD_LDAP_DN,
v.FD_MEMO,
v.FD_HIERARCHY_ID,
v.FD_CREATE_TIME,
v.FD_ALTER_TIME,
v.FD_ORG_EMAIL,
v.FD_PERSONS_NUMBER,
v.FD_PRE_DEPT_ID,
v.FD_PRE_POST_IDS,
v.FD_THIS_LEADERID,
v.FD_SUPER_LEADERID,
v.FD_PARENTORGID,
v.FD_PARENTID,
v.FD_CATEID,
v.AUTH_READER_FLAG,
v.FROM_CODE,
v.PARENT_FROM_CODE,
v.MANAGE_FROM_CODE,
v.BRANCHES_FROM_CODE,
v.DEPT_FROM_CODE,
v.PERSON_FROM_CODE,
v.FD_DEPT_LVL,
v.FD_POSN_LVL
)
values (
b.FD_ID,
b.FD_ORG_TYPE,
b.FD_NAME,
b.FD_NAME_PINYIN,
b.FD_NAME_SIMPLE_PINYIN,
b.FD_ORDER,
b.FD_NO,
b.FD_KEYWORD,
b.FD_IS_AVAILABLE,
b.FD_IS_ABANDON,
b.FD_IS_BUSINESS,
b.FD_IMPORT_INFO,
b.FD_FLAG_DELETED,
b.FD_LDAP_DN,
b.FD_MEMO,
b.FD_HIERARCHY_ID,
b.FD_CREATE_TIME,
b.FD_ALTER_TIME,
b.FD_ORG_EMAIL,
b.FD_PERSONS_NUMBER,
b.FD_PRE_DEPT_ID,
b.FD_PRE_POST_IDS,
b.FD_THIS_LEADERID,
b.FD_SUPER_LEADERID,
b.FD_PARENTORGID,
b.FD_PARENTID,
b.FD_CATEID,
b.AUTH_READER_FLAG,
b.FROM_CODE,
b.PARENT_FROM_CODE,
b.MANAGE_FROM_CODE,
b.BRANCHES_FROM_CODE,
b.DEPT_FROM_CODE,
b.PERSON_FROM_CODE,
b.FD_DEPT_LVL,
b.FD_POSN_LVL
) ;
