常用sql语句

select TABLE_NAME,TABLESPACE_NAME from user_tables;
select TABLESPACE_NAME from user_tables group by TABLESPACE_NAME;
select username,default_tablespace from user_users;
select * from nls_database_parameters 


alter system set deferred_segment_creation=false scope=both;
alter system set sec_case_sensitive_logon=false scope=both;
ALTER  PROFILE  DEFAULT  LIMIT  PASSWORD_LIFE_TIME  UNLIMITED;


drop tablespace VCS_GL_TBS including contents and datafiles;




create  tablespace VCS_GL_TBS datafile 'D:\app\Administrator\oradata\orcl\VCS_GL_TBS.dbf' size 5G autoextend on  maxsize unlimited; 


create  tablespace VCS_GL_TBS datafile 'D:\app\Administrator\oradata\orcl\VCS_GL_TBS.dbf' size 6G autoextend on  maxsize unlimited; 


alter tablespace VCS_GL_TBS  add datafile 'D:\app\Administrator\oradata\orcl\VCS_GL_TBS02.dbf' size 5G autoextend on  maxsize unlimited;






create  tablespace VCS_TBS datafile 'D:\app\Administrator\oradata\orcl\VCS_TBS.dbf' size 100M autoextend on  maxsize unlimited; 
create  tablespace VCS_XM_TBS datafile 'D:\app\Administrator\oradata\orcl\VCS_XM_TBS.dbf' size 100M autoextend on  maxsize unlimited;


create user VCS_GL identified by VCS_GL account unlock default tablespace VCS_GL_TBS;


grant connect to VCS_GL;
grant RESOURCE to VCS_GL;
grant dba to VCS_GL;






exp VCS_GL/VCS_GL@orcl file=D:\vcsg.dmp log=D:\vcsg.log




imp VCS_GL/VCS_GL@orcl file=C:\vcsg.dmp  ignore=y full=y log=C:\vcsg.log  feedback=10000 buffer=10240000




imp VCS_GL/VCS_GL@orcl file=C:\vcsgxuni.dmp  ignore=y full=y log=C:\vcsgxuni.log  feedback=10000 buffer=10240000






rename ICC_SHARE_PUC_PDTSTATELOG to ICC_SHARE_PUC_PDTSTATELOG_PP






select count(*) from user_tables
select count(*) from user_objects where object_type='PROCEDURE';


select object_type,count(*) as sum from user_objects group by object_type order by sum desc;


select OBJECT_NAME,object_type,STATUS from user_objects where object_type = 'INDEX' order by OBJECT_NAME desc




ALTER DATABASE ADD LOGFILE GROUP 4 ('') SIZE 1024M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('') SIZE 1024M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('') SIZE 1024M;
ALTER SYSTEM SWITCH LOGFILE;
SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
ALTER SYSTEM CHECKPOINT;


ALTER DATABASE DROP LOGFILE GROUP 1;


sessions=1.1 * processes + 5




优化方法如下:


关闭数据库直接路径读取 direct path read
alter session set "_small_table_threshold"=9999999 scope=spfile;
ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER';


新增索引
AIA_T_ALARM_CASE表 case_time列 


alter system set db_keep_cache_size=230m; 修改 keep_pool内存区大小


将这三张表缓存入keep_pool内存区中
alter table AIA_T_ALARM_CASE storage (buffer_pool keep);
alter table AIA_T_DEPARTMENT storage (buffer_pool keep);
alter table AIA_T_CASE_TYPE storage (buffer_pool keep);


增加这三张表的并行度设置为10
alter table AIA_T_ALARM_CASE parallel 10;
alter table AIA_T_DEPARTMENT parallel 10;
alter table AIA_T_CASE_TYPE parallel 10;