ORACLE常用命令

ORACLE常用命令

查询或者的session(需要管理员权限)
SELECT S.USERNAME,
       S.SID,
       S.SERIAL#,
       S.INST_ID,
       S.EVENT,
       S.WAIT_CLASS,
       S.LOGON_TIME,
       S.ACTION,
       SQ.SQL_TEXT
  FROM GV$SESSION S, GV$SQLAREA SQ
 WHERE S.STATUS = 'ACTIVE'
   AND S.USERNAME IS NOT NULL
   AND S.SQL_ID = SQ.SQL_ID;

查询触发器状态(当前登录用户)

SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = '触发器名称';

查询锁表情况(管理员)

SELECT lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,s.MACHINE,s.MODULE,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM gv$locked_object l,dba_objects o,gv$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC;

查询表所在表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES a where a.table_name ='表名称';

查询数据库文件信息

select * from dba_data_files t where t. tablespace_name= '表空间名称';

查询指定表在其表空间所占大小

select bytes /1024/1024/1024 G from dba_segments where owner='用户名称' and segment_name='表名称';

统计执行的session对象(管理员)

select username,machine,count(*) from v$session group by username,machine;

创建表空间

create tablespace 表空间名称 
datafile 'D:APPADMINISTRATORPRODUCT11.2.0DBHOME_1DATABASEGQRC.dbf' 
size 1500M 
autoextend on next 5M maxsize 3000M;

查看表空间的名称及大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 

查看表空间物理文件的名称及大小

SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name;

在指定表空间下创建用户

create user 用户名 identified by 密码             
default tablespace 指定表空间名 
temporary tablespace 临时表空间名;

修改用户密码

alter user 用户名 identified by 新密码;

用户授权

grant connect,resource,dba to 用户名称;

查询锁的表并解锁(管理员)

#查询锁住的表
select b.OWNER,b.OBJECT_NAME,c.sid,c.SERIAL# 
from v$locked_object a,dba_objects b,v$session c 
where a.OBJECT_ID=b.OBJECT_ID 
and a.SESSION_ID = c.sid;

#解锁
alter system kill session 'sid,serial#';

查询表的创建日期(管理员权限)

select CREATED from dba_objects where  OBJECT_NAME ='表名';

索引

单索引
create index 索引名称 on table(column)

删除索引
drop index 索引名称

复合索引
create index WBSINDEX ON project_info(wbs,is_delete)

查询某张表中所有索引
select * from ALL_INDEXS where table_name = project_info

查询某张表加了索引的列
select * from ALL_IND_COLUMN where table_name = project_info

索引优缺点:
优点 : 加快查询速度
缺点 : 更新,要把索引也进行更新

清理当前用户回收站

purge recyclebin;

所有表名及该表总数据

select table_name,num_rows from user_tables

当前登陆用户所有表数据总和

select sum(num_rows) from user_tables;

移动分区到指定表空间

-- 移动分区到指定表空间
alter table hn_conform_essc_sign_parttion move partition P20 tablespace 表空间名称;

并发数据导入(由一张表导入到另一张表)

--方式一:

Insert /*+ append parallel(m,18)*/  into az03_extendm  m (TABID,
AAB301,
AAZ500,
AAC002,
AAC003,
CARDTYPE,
TRANSACTTYPE,
BHYY,
BATCHNO,
BATCHNUMBER,
SELECTED,
ORGANID,
SZSQ,
AAB001,
KS,
ZXWZ,
AAE008,
AAE008B,
AAE010,
AAE010A,
AAE010B,
APPLYTIME,
BANKTIME0,
BANKFINISHTIME0,
INSURETIME,
INSUREFINISHTIME0,
INSUREFINISHTIME,
BANKTIME,
BANKFINISHTIME,
PROVINCETIME,
CITYTIME,
GETTIME,
GETTIME1,
REMARKS,
USERORGANID,
USEROPERNAME,
USEROPERTIME,
VALIDTAG,
SLFF,
JFJL,
ZKJS,
CSHS,
SJZLQR,
AAZ508,
LKYHWD,
ISPOST,
POSTNO,
FPXH,
XHBQ,
SQMC,
SKDZ,
SKLXR,
SKDH,
KXLH,
FJKXLH,
ENDADDRESS,
SJLY,
SJLYLX,
PAYCODE,
POSTCOMPANY,
YZKBANKTIME,
YZKDCBATCHNO,
KHSBYY,
YZKBANKTIME0,
SZKZX,
BANKSTATE,
TSZT,
MAILSTATE,
MAILSTATETIME,
JSBZ,
JSBS) 

select  /*+parallel (b,18)*/ 
 TABID,
AAB301,
AAZ500,
AAC002,
AAC003,
CARDTYPE,
TRANSACTTYPE,
BHYY,
BATCHNO,
BATCHNUMBER,
SELECTED,
ORGANID,
SZSQ,
AAB001,
KS,
ZXWZ,
AAE008,
AAE008B,
AAE010,
AAE010A,
AAE010B,
APPLYTIME,
BANKTIME0,
BANKFINISHTIME0,
INSURETIME,
INSUREFINISHTIME0,
INSUREFINISHTIME,
BANKTIME,
BANKFINISHTIME,
PROVINCETIME,
CITYTIME,
GETTIME,
GETTIME1,
REMARKS,
USERORGANID,
USEROPERNAME,
USEROPERTIME,
VALIDTAG,
SLFF,
JFJL,
ZKJS,
CSHS,
SJZLQR,
AAZ508,
LKYHWD,
ISPOST,
POSTNO,
FPXH,
XHBQ,
SQMC,
SKDZ,
SKLXR,
SKDH,
KXLH,
FJKXLH,
ENDADDRESS,
SJLY,
SJLYLX,
PAYCODE,
POSTCOMPANY,
YZKBANKTIME,
YZKDCBATCHNO,
KHSBYY,
YZKBANKTIME0,
SZKZX,
BANKSTATE,
TSZT,
MAILSTATE,
MAILSTATETIME,
JSBZ,
JSBS from az03 b WHERE   b.TABID > 150218775;

commit;

--方式二:

Insert /*+ append parallel(m,18)*/  into AZ03_EX   

select  /*+parallel (b,18)*/ 
 *
 from az03_expand_bh_exp;

commit;
定时job执行存储过程
1.创建定时被调用的存储过程

---- create procedure

create or replace procedure job_test_pro
   
  begin
    ....
  exception
    ....
  end job_test_pro;
2.定义job名称

   2.1 variable job2014 number —定义在oracle中自动生成job的序号的数据类型对应到dba_jobs的数据字典中的字段job的值。现在job值为41对应名称为job2014	   数据类型为number

	2.2将job注册到dba_jobs表中定时启动job调用存储过程

        begin

        dbms_job.submit(:job2014,‘JOB_TEST_PRO;,sysdate,‘sysdate+1/1440);

        end;

        /

        commit;
 3.手动运行job的指令
  begin
    dbms_job.run(:job2014);
  end;
  /
	删除job
  begin
    dbms_job.remove(:job2014);
  end;
  /    
  
  4.
          查看job相关信息sqlSELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN  

        FROM DBA_JOBS;

        查看正在运行的job相关信息sqlSELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC  

        FROM DBA_JOBS_RUNNING r, DBA_JOBS j  

        WHERE r.JOB = j.JOB;  

        查看JOB QUEUE LOCK相关信息sqlSELECT SID, TYPE, ID1, ID2  FROM V$LOCK  WHERE TYPE = ’JQ’;



cmd下以管理员身份进入oracle

sqlplus / as sysdba 

表分析

----分析表

begin
dbms_stats.gather_table_stats(
OWNNAME=>'HNCARD',  --用户名
TABNAME=>'AZ03',--表名
ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size repeat',
DEGREE=>24,  --cpu核数
GRANULARITY=>'ALL',
CASCADE=> true,
NO_INVALIDATE=>false
);
 end;
/

回收表空间

ALTER TABLE LOG_PRO_CARD_HANDLE ENABLE ROW MOVEMENT;--开启位移操作

ALTER TABLE LOG_PRO_CARD_HANDLE SHRINK SPACE COMPACT;
ALTER TABLE LOG_PRO_CARD_HANDLE SHRINK SPACE;
ALTER TABLE LOG_PRO_CARD_HANDLE shrink space cascade;--收缩表并降低hwm,并且回收相应的索引。

ALTER TABLE LOG_PRO_CARD_HANDLE DISABLE ROW MOVEMENT;--关闭位移

 --查询表是否有函数索引
 SELECT table_name,index_name,index_type FROM User_Indexes WHERE index_type LIKE 'FUNCTION-BASED%' and table_name='LOG_EMS_NOTIFY';

Oracle中把一张表查询结果插入到另一张表中

insert into 要插入的表名 select * from 需要插入的数据表;
insert into HN_CONFORM_ESSC_SIGN_ZZ select * from HN_CONFORM_ESSC_SIGN WHERE region_no='410100';

查看正在执行的进程

------------查看正在执行的进程
SELECT S.USERNAME,
       S.SID,
       S.SERIAL#,
       S.INST_ID,
       S.EVENT,
       S.WAIT_CLASS,
       S.LOGON_TIME,
       S.ACTION,
       SQ.SQL_TEXT
  FROM GV$SESSION S, GV$SQLAREA SQ
 WHERE S.STATUS = 'ACTIVE'
   AND S.USERNAME IS NOT NULL
   AND S.SQL_ID = SQ.SQL_ID;

查询表所占表空间大小

SELECT
	segment_name,
	bytes 
FROM
	user_segments 
WHERE
	segment_type = 'TABLE' 
	AND segment_name LIKE 'LOG%' 
ORDER BY
	bytes DESC;
	
	
	
SELECT
  segment_name,  
  bytes /1024/1024/1024
FROM
  user_segments 
WHERE
  segment_type = 'TABLE' 
ORDER BY
  bytes DESC;

导出dmp

exp INTERFACETESTUSER/SBKjktest#[email protected]:1521/kgH5qf file=d:api.dmp  tables=(API_INFO) log=d:api.log

导入dmp

imp INTERFACETESTUSER/SBKjktest#[email protected]:1521/kgH5qf file=d:api.dmp log=d:api.log full=y

imp INTERFACETESTUSER/SBKjktest#[email protected]:1521/kgH5qf file=d:api.dmp log=d:api.log buffer=40960000 ignore=y rows=y indexes=n

imp escard/SBKescard#[email protected]:1521/sbkzhyy file=d:datetatemp_419900.dmp  tables=(hn_conform_essc_sign_419900) ignore=y  buffer=40960000 indexes=n 

imp escard/SBKescard#[email protected]:1521/sbkzhyy file=d:escc_msg_410400_230418.dmp fromuser=HNSINTERFACE touser=escard

plsq连接远程数据库

sqlplus usr/pwd@//host:port/sid

查询所有表空间以及每个表空间的大小,已用空间,剩余空间,使用率和空闲率

SELECT
	a.tablespace_name,
	total,
	free,
	total - free AS used,
	substr( free / total * 100, 1, 5 ) AS "FREE%",
	substr( ( total - free ) / total * 100, 1, 5 ) AS "USED%" 
FROM
	( SELECT tablespace_name, sum( bytes ) / 1024 / 1024 AS total FROM dba_data_files GROUP BY tablespace_name ) a,
	( SELECT tablespace_name, sum( bytes ) / 1024 / 1024 AS free FROM dba_free_space GROUP BY tablespace_name ) b 
WHERE
	a.tablespace_name = b.tablespace_name 
ORDER BY
	a.tablespace_name;

删除表

truncate table 表名

重命名表

RENAME HN_CONFORM_PDS1 TO new_name;

非分区表导入分区表并行12所用时长:13445.407s

oracle查看用户所占空间大小

查询当前登陆用户所占空间大小:
select sum(BYTES) / 1024 / 1024/1024 as SIZE_G
from user_segments

查询所有dba用户所占空间大小:
select sum(BYTES) / 1024 / 1024/1024 as SIZE_G
from dba_segments
查看当前用户下所有表占用的空间情况:
select segment_name,tablespace_name,bytes,blocks from user_segments


ORACLE-查看当前ORACLE版本是否支持表分区功能

select * from v$option;

可以看到Partitioning的值为FALSE,故不支持表分区功能。
如果已经安装的是企业版了,但是Partitioning仍然为FALSE,则应install数据库,将组件选上即可。

并行对大数据表加索引

create index IND_XZ_FINDJSMXDATA ON HC_XZ_UNSETTLED_CARD (card_property,EXTRACT_STATUS,citytime,batchno,aae008) online parallel 8

创建表空间

CREATE tablespace MQ DATAFILE '+DATA'SIZE 2G autoextend on;

重命名表空间

alter tablespace oldnamespace rename to newnamespace;

给表移动表空间

alter table 表名 move tablespace 目标表空间名称;

查看当前用户信息:

select * from user_users;

查看Oracle数据库版本命令

select * from v$version;

可以看到Partitioning的值为FALSE,故不支持表分区功能。
如果已经安装的是企业版了,但是Partitioning仍然为FALSE,则应install数据库,将组件选上即可。

并行对大数据表加索引

create index IND_XZ_FINDJSMXDATA ON HC_XZ_UNSETTLED_CARD (card_property,EXTRACT_STATUS,citytime,batchno,aae008) online parallel 8

创建表空间

CREATE tablespace MQ DATAFILE '+DATA'SIZE 2G autoextend on;

重命名表空间

alter tablespace oldnamespace rename to newnamespace;

给表移动表空间

alter table 表名 move tablespace 目标表空间名称;

查看当前用户信息:

select * from user_users;

修改字段默认属性值

ALTER TABLE table_name add 字段名 CHAR(1) default 0

删除字段属性

ALTER TABLE table_name drop column   字段名 

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>