1
2
3

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

--① 分析表的所有字段，所有索引，所有索引字段。其中：① = ②
analyze table student compute statistics;
--② 分析表的所有字段，所有索引，所有索引字段。其中：① = ②
analyze table student compute statistics for table for all indexes for all columns;
--③ 只分析所有有索引的字段
analyze table student compute statistics for table for all indexes for all indexed columns;

--④ 抽样估算法
analyze table student estimate statistics sample 20 percent;

--⑤ 删除分析数据
analyze table student delete statistics;
analyze table student delete statistics for table for all indexes for all indexed columns;

--查看表的统计信息
select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='student';

analyze table glaq_t compute statistics;

1
2
3

SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

1
2
3
4
5

SELECT /*+index(a)*/ * FROM sfb_file
LEFT JOIN sfa_file ON sfa01= sfb01
LEFT JOIN ima_file a ON a.ima01 = sfb05
LEFT JOIN ima_file b ON b.ima01 = sfa03
LEFT JOIN ima_file c ON c.ima01 = sfa27

1
2

select object_name,original_name,partition_name,type,ts_name,createtime,droptime
from recyclebin;

1
2

flashback table test_drop to before drop;或
flashback table "BIN$hgcd$0" to before drop;

1
2
3
4
5
6
7

set linesize 132;
set pagesize 0;
set timing off;
spool drop_tables.sql;
SELECT 'drop table '||OWNER||'.'||TABLE_name||' purge;' FROM dba_tables WHERE tablespace_name='TEMPTABS';
spool off;
@drop_tables.sql;

1
2

select * from sfb_file
as of timestamp to_timestamp('2021-09-22 15:30:00','yyyy-mm-dd hh24:mi:ss')

1
2
3
4
5
6
7
8

select * from (
  select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
  from dba_segments t
  where t.segment_type='TABLE'
	AND tablespace_name ='TEMPTABS'
  group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc;

1
2
3
4

sqlplus system/manager@topprod
   create user test1 identified by test1 default tablespace dbs1;
   grant connect,resource to test1;
   commit;

1
2

imp system/manager@topprod file=/u3/backup/Wed/exp_KT_20210203.dmp
 fromuser=kt touser=test1 tables=gen_file,eci_file

1

alter tablespace xx add datafile 'xxx.dbf' size 4G;