【oracle】oracle的一些操作



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

        ) ;   

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,636评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,890评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,680评论 0赞 330
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,766评论 1赞 271
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,665评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,045评论 1赞 276
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,515评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,182评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,334评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,274评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,319评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,002评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,599评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,675评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,917评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,309评论 2赞 345
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,885评论 2赞 341