复制表，包括表结构
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_cityalter 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 listselect SNAP_ID ,DBID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;--create reportspool /home/ora10g/test/report.htmlselect 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_cityAND 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