查看表空间日期处理

--查看表空间使用

select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a, 
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b 
where   a.tablespace_name=b.tablespace_name 
order   by   ((a.bytes-b.bytes)/a.bytes)   desc

--增加表空间 数据文件
alter tablespace BQDX add datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\EMP\BQDX03.dbf' size 20G;

--修改
alter database datafile 'g:\tablespace\pegnius01.dbf' resize 1500M;

--日期处理



select  'partition part_'||to_char(monthday-1, 'yyyymmdd') ||' less than ('||to_char(monthday, 'yyyy-mm-dd') ||'),'
  from (select trunc(sysdate - 130, 'month') + rownum - 1 monthday
          from all_objects
         where rownum <= (select to_char(last_day(sysdate), 'dd') from dual))





--创建索引

create index SMS_SEARCH_index1 on SMS_SEARCH(c_Send_Num,d_send desc) local nologging parallel 8
---创建表空间

/*分为四步 */
/*第1步：创建临时表空间  */
create temporary tablespace user_temp 
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

/*第2步：创建数据表空间  */
create tablespace test_data 
logging 
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 

/*第3步：创建用户并指定表空间  */
create user username identified by password 
default tablespace user_data 
temporary tablespace user_temp; 

/*第4步：给用户授予权限  */
grant connect,resource,dba to username;