Oracle点滴

最新推荐文章于 2025-08-16 09:13:57 发布

weixin_30768175

最新推荐文章于 2025-08-16 09:13:57 发布

阅读量46

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/9sheng/archive/2012/09/14/2684468.html

复制表，包括表结构

create table TACE_TRAFFIC_HUB as select * from TACE_TRAFFIC_HUB1

复制表中数据，表结构存在

insert into TACE_TRAFFIC_HUB select * from TACE_TRAFFIC_HUB1

修改表结构

alter table pnr_path add (dep_city VARCHAR2(3), arr_city VARCHAR2(3))
alter table pnr_path rename column city to dep_city
alter table pnr_path modify dep_city varchar(40)
alter table pnr_path drop column dep_city

查看当前用户每个表占用空间的大小

select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name

查看每个表空间占用空间的大小

select tablespace_name,sum(bytes)/1024/1024 from dba_segments group by tablespace_name

查看 avs_i 用户哪些表建立了别名

select * from dba_synonyms where tabel_owner = 'AVS_I'

查看表以及字段

select table_name, column_name, data_type from user_tab_columns order by table_name, column_name

统计数据库性能数据

sqlplus sys/oracle as sysdba

--begin snapshot.(return snap_id)
select DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT from dual;

--do your query here

--end snapshot.(return snap_id)
select DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT from dual;

set veri off;
set feedback off;
set linesize 1500;
set pagesize 50000;
set termout on;

--snapshot list
select SNAP_ID ,DBID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

--create report
spool /home/ora10g/test/report.html
select output from table(dbms_workload_repository.AWR_REPORT_HTML(3971050283,1,33690,33691));
spool off;

绑定变量查询

set linesize 200;
var dep_airport varchar2(3);
var arr_airport varchar2(3);
var dep_city varchar2(3);
var arr_city varchar2(3);
var stop number;
var dtFrom varchar2(8);
var dtTo varchar2(8);
var period varchar2(8);

exec :dep_city := 'BJS';
exec :dep_airport := 'PEK';
exec :arr_city := 'LAX';
exec :arr_airport := 'LAX';
exec :stop := 2;
exec :dtFrom := '20120213';
exec :dtTo := '20120213';
exec :period := '%1%';

SELECT * FROM ROUTING WHERE (OWNER='H' OR OWNER='M')
AND DEP_LOC=:dep_city AND ARR_LOC=:arr_city
AND DEP_AIRPORT=:dep_airport AND FROM_DATE<=to_date(:dtFrom, 'YYYYMMDD')
AND (TO_DATE IS NULL OR (TO_DATE >= TO_DATE(:dtTo,'YYYYMMDD'))) AND PERIOD LIKE :period;

执行 sql 脚本文件

@d:\mysql.sql

查询当前锁

SELECT s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, 
o.owner, o.object_name, o.object_type, s.sid, s.serial#, s.terminal, s.machine, s.program, s.osuser 
FROM v$session s, v$lock l, all_objects o 
WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is not null

删除锁

alter system kill session '[sid],[serial#]';

查询锁对应的sql

SELECT vs.SQL_TEXT, vsess.sid,vsess.SERIAL#, vsess.MACHINE, vsess.OSUSER,
vsess.TERMINAL, vsess.PROGRAM, vs.CPU_TIME, vs.DISK_READS 
FROM v$sql vs, v$session vsess 
WHERE vs.ADDRESS = vsess.SQL_ADDRESS AND vsess.sid= :sid

转载于:https://www.cnblogs.com/9sheng/archive/2012/09/14/2684468.html