oracle 自己的学习
1、trunc
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41
8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
2、排序
rank 排序并列的相同 dense_rank的不同
select sc.s_id,sc.s_name,sub_name,sc.score,
rank() over (order by score desc) 名次 //desc 冲大到小 忽略重小倒打
from t_score sc
where sub_name='Oracle'
参考文档http://blog.csdn.net/cczz_11/article/details/6053539
3、表空间
/*分为四步 */ /*第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 user_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;
/* oracle 表空间的一些东西*/
--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)),
0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE
t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--6、查看表空间的使用情况
SELECT
SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM
dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) /
a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM
sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE
a.tablespace_name = b.tablespace_name
AND a.tablespace_name =
c.tablespace_name;
--7、查看数据库库对象
SELECT owner, object_type, status,
COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT
created, log_mode, log_mode FROM v$database;
4、oracle编码格式
--查看oracle数据库字符集：
select userenv('language') from dual;
查询结果：
SIMPLIFIED CHINESE_CHINA.AL32UTF8
--修改oracle数据库字符集：（在SQL Plus中）
sql> conn / as sysdba;
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 135337420 bytes
fixed size 452044 bytes
variable size 109051904 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
database mounted.
sql> alter system enable restricted session;
system altered.
sql> alter system set job_queue_processes=0;
system altered.
sql> alter system set aq_tm_processes=0;
system altered.
sql> alter database open;
database altered.
sql> alter database character set internal_use UTF8
sql> shutdown immediate;
sql> startup;
