操作表空间sql

---创建表空间
 create tablespace ha_data2 NOLOGGING datafile 'E:\ORADATA\ORCL\ha_data2.ora' size 500M
     autoextend on next 50M maxsize unlimited extent management local
CREATE TEMPORARY TABLESPACE orcl_temp tempfile 'E:\ORADATA\ORCL\orcl_temp.ora' size 100M
     autoextend on next 10M maxsize unlimited extent management local
---修改默认临时表空间
alter database default temporary tablespace zior_temp
alter database default tablespace nnc_data01

select username,temporary_tablespace from dba_users

alter user liuyy0510 default temporary tablespace orcl_temp

---查看表空间大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
select file_name, bytes/1024/1024, tablespace_name, autoextensible, status from dba_data_files

--谁在使用临时段
SELECT se.Username,  Sid,  Serial#,   Sql_Address,  Machine,  Program,  Tablespace,  Segtype, Contents
      FROM V$session    Se,   V$sort_Usage Su WHERE Se.Saddr = Su.Session_Addr
---更换表的表空间
alter table tname move tablespace newtablespace_name

---缩小表空间文件占用大小
1、删除或是移出表空间中的内容；
2、对users表空间碎片整理：alter tablespace users coalesce;
3、缩小数据文件大小：alter database datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF' resize 100M;

 ---删除表空间
drop tablespace ha_index

---创建用户
create user foodmart identified by "1" default tablespace nnc_data01 users Temporary TABLESPACE zior_temp
grant connect,resource,dba to foodmart WITH ADMIN OPTION



---减少内存占用
ALTER SYSTEM SET sga_max_size = 250m SCOPE=BOTH;---SPFILE;---150m

ALTER SYSTEM SET sga_target = 100m SCOPE=BOTH;


---物化视图查询重写参数
alter system set query_rewrite_integrity=stale_tolerated scope=both; ---trusted  enforced
alter system set query_rewrite_enabled=true

 

---物化视图
 create  materialized view  mview_owner_sum
                build immediate   refresh fast on commit    enable query rewrite
                as 
                select count(*),owner from test
                 group by owner;
                 
DROP MATERIALIZED VIEW LOG ON rms_data_equip 

DROP MATERIALIZED VIEW  mv_cube_rms_data_equip 
      
CREATE MATERIALIZED VIEW LOG ON TIME_YEAR WITH ROWID, SEQUENCE(time_year)  INCLUDING NEW VALUES