orcle常用语句

--1.创建临时表空间
create temporary tablespace AUTOMONITORV5_temp
tempfile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5_temp.DBF'  
size 50m    
autoextend on   
next 50m maxsize 10240m   
extent management local; 

--2.创建表空间
create tablespace AUTOMONITORV5   
logging   
datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5.DBF'  
size 50m   
autoextend on   
next 50m maxsize 12720m   
extent management local;  

--3.创建用户
create user automonitorv5 identified by "123456"   
default tablespace AUTOMONITORV5   
temporary tablespace AUTOMONITORV5_temp;  

--4.授权
grant connect,resource,dba to automonitorv5; 

--5.删除用户
drop user automonitorv5 cascade;

--6.删除表空间
DROP TABLESPACE automonitorv5 INCLUDING CONTENTS AND DATAFILES;

--7.查询表空间位置及大小
select tablespace_name,
       file_id,
       file_name,
       round(bytes / (1024 * 1024), 0) total_space
  from dba_data_files
 order by tablespace_name;
 
--8.表空间使用率
SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       Round((total - free) / total, 4) * 100 "使用率   %"
  FROM (SELECT tablespace_name, Sum(bytes) free
          FROM DBA_FREE_SPACE
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, Sum(bytes) total
          FROM DBA_DATA_FILES
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name;
 
--9.增大表空间大小
alter database datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5.DBF' resize 4000m;

--10.增加文件个数
alter tablespace AUTOMONITORV5
     add datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV51.DBF' size 1000m;
     
--11.设置表空间自动增长
alter database datafile 'D:\ORACLE\KARL\ORADATA\ORCL\AUTOMONITORV5.DBF'
      autoextend on next 100m maxsize 10240m;

--12.获取10年前的日期
select to_char(sysdate-numtoyminterval(10,'year'),'yyyy-MM-dd HH24:mi:ss') from dual;

--13.小数转字符补零方法
1)将小数点前的第一位置为0即可(注意9的个数要大于数值的位数)
select to_char(.23,'fm999999990.999999999') from dual;
2)既然小于1的小数首位必然是'.'，那就判断首位是否为'.'，是则在前面加上'0'即可
select decode(substr(.23,1,1),'.','0'||.23,.23) from dual; 
<a target=_blank href="http://www.2cto.com/database/201304/205087.html">oracle小数转字符串</a>

--14.获取表名称
select table_name,comments
  from user_tab_comments
 where table_type='TABLE'
 order by table_name

--15.表结构查询
select x.column_name 列名称,
       x.data_type 数据类型,
       x.data_precision 精度,
       x.data_scale 小数位,
       case when z.table_name is not null then '√' else '' end 是否为主键,
       case when x.nullable='Y' then '√' else '' end 是否为空,
       func_data_longtovarchar2(x.table_name,x.column_name) 默认值, 
       y.comments 备注
  from user_tab_cols x
  left join user_col_comments y on x.table_name=y.table_name and x.column_name=y.column_name
  left join user_ind_columns z on x.table_name=z.table_name and x.column_name=z.column_name
 where x.table_name='TEMP_DATAANALYZE'
 order by x.column_id;

--16.func_data_longtovarchar2
create or replace function func_data_longtovarchar2(v_tablename varchar2,v_columnname varchar2)
return varchar2 as
  v_long long;
  v_varchar2 varchar2(4000); 
begin
  select data_default
    into v_long
    from user_tab_cols
   where table_name=v_tablename
     and column_name=v_columnname;
   
  v_varchar2:=substr(v_long,1,4);
  return v_varchar2;
end;