---创建表空间
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