--建表时立即分配空间alter system set deferred_segment_creation=FALSE;--设置最大连接数selectvaluefrom v$parameter where name ='processes';--数据库允许的最大连接数alter system set processes =1000 scope = spfile;--修改最大连接数--开启归档--开启闪回--参数调优
方法一:关闭它
alter system set event='10949 TRACE NAME CONTEXT FOREVER' scope=spfile;alter system set"_serial_direct_read"=false scope=both;ALTERSESSIONSET EVENTS '10949 TRACE NAME CONTEXT FOREVER';
方法二:调高下限
alter system set"_small_table_threshold"=2621440 scope=spfile sid='*';-- 约2G altersessionset"_small_table_threshold"=2621440
-- 一般开发使用create role LZ;-- Grant/Revoke object privileges grantselecton SYS.V_$SESSIONto LZ;grantselecton SYS.V_$SESSTAT to LZ;grantselecton SYS.V_$STATNAME to LZ;-- Grant/Revoke role privileges grant exp_full_database to LZ;--grant imp_full_database to LZ;-- Grant/Revoke system privileges grantcreate job to LZ;grantcreateprocedureto LZ;grantcreatesessionto LZ;grantcreate synonym to LZ;grantcreate sequence to LZ;grantcreatetableto LZ;grantcreatetriggerto LZ;grantcreatetypeto LZ;grantcreateviewto LZ;grant debug anyprocedureto LZ;grant debug connectsessionto LZ;grantcreatedatabase link to LZ with admin option;
4.授权
grant lz to dianqi;alteruser dianqi quota unlimited on users;alteruser dianqi quota unlimited on system;alteruser dianqi quota unlimited on dianqi;alteruser dianqi quota unlimited on dianqi_index;
-----------------------------查看表空间使用情况--------------------------------------------- 查看表空间使用率(包括临时表空间)select*from(Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024- b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1- b.bytes/a.bytes)*100,'99.99')||'%'usefrom(select tablespace_name,sum(bytes) bytes
from dba_data_files
groupby tablespace_name) a,(select tablespace_name,sum(bytes) bytes
from dba_free_space
groupby tablespace_name) b
where a.tablespace_name = b.tablespace_name
unionallselect c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char((c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99')||'%'usefrom(select tablespace_name,sum(bytes) bytes
from dba_temp_files groupby tablespace_name) c,(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool groupby tablespace_name) d
where c.tablespace_name = d.tablespace_name
)orderby tablespace_name;--2.查看文件是否自动扩展select d.file_name,d.tablespace_name,d.autoextensible,d.*from dba_data_files d;--如果想查看临时表空间文件是否自动扩展select d.file_name,d.tablespace_name,d.autoextensible,d.*from dba_temp_files d;
检查迁移对象是否都过来了
--检查迁移过来的对象数量select*from all_tab_comments t where upper(t.owner)='dianqi';select*from user_tables
五、其他
--创建闪回目录
cd d:\oracle\mkdir flashback (生成闪回目录)
---------------------------------开归档再开闪回--------------------------------------select name,current_scn,flashback_on from v$database;--查看是否开启闪回功能--创建闪回目录
cd d:\oracle\
mkdir flashback (生成闪回目录)--alter system set db_recovery_file_dest='d:\oracle\flashback' scope=both; --设置目录alter system set db_recovery_file_dest_size=80G scope=both;--设置大小alter system set db_flashback_retention_target=2880;--配置保留时间 1440为一天SQL>shutdown immediate
SQL> startup mount
SQL>alterdatabase archivelog;--开启归档SQL>alterdatabase flashback on;--开启闪回SQL>alterdatabaseopen;select name,current_scn,flashback_on from v$database;--查看是否开启闪回功能select name,log_mode,open_mode from v$database;--查看是否开了归档--归档及闪回空间使用情况select*from v$flash_recovery_area_usage;--查看空间占用率select*from v$recovery_file_dest;--查看归档日志的存放地址;