ORACLE常用命令

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:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\GQRC.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相关信息sql：
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN

FROM DBA_JOBS;

查看正在运行的job相关信息sql：
SELECT 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相关信息sql：
SELECT 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#2021@10.120.4.135:1521/kgH5qf file=d:\api.dmp tables=(API_INFO) log=d:\api.log
1
导入dmp
imp INTERFACETESTUSER/SBKjktest#2021@10.120.4.135:1521/kgH5qf file=d:\api.dmp log=d:\api.log full=y

imp INTERFACETESTUSER/SBKjktest#2021@10.120.4.135:1521/kgH5qf file=d:\api.dmp log=d:\api.log buffer=40960000 ignore=y rows=y indexes=n

imp escard/SBKescard#2021@10.120.2.16:1521/sbkzhyy file=d:\dateta\temp_419900.dmp tables=(hn_conform_essc_sign_419900) ignore=y buffer=40960000 indexes=n

imp escard/SBKescard#2021@10.120.2.16: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 字段名