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 字段名
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。