oracle之schema以及其他记录

oracle数据库中不能新创建一个schema，
要想创建一个schema，只能通过创建一个用户的方法解决(oracle中虽然有create schema语句，但是它并不是用来创建一个schema的)，
在创建一个用户的同时为这个用户创建一个与用户名同名的schema并作为该用户的缺省schema




即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同，
所以我们可以称schema为user的别名，虽然这样说并不准确，但是更容易理解一些。




一个用户有一个缺省的schema，其schema名就等于用户名，
当然一个用户还可以使用其他的schema。
如果我们访问一个表时，没有指明该表属于哪一个schema中的，
系统就会自动给我们在表上加上缺省的sheman名。
比如我们在访问数据库时，访问scott用户下的emp表，通过select from emp 其实，这sql语句的完整写法为select from scott.emp。


sql> grant dba to scott
sql> create table test(name char(10))
table created.
sql> create table system.test(name char(10))
table created.
sql> insert into test values(' scott' )
1 row created.
sql> insert into system.test values(' system' )
1 row created.
sql> commit
commit complete.
sql> conn system/manager
connected.
sql> select from test
name
----------
system
sql> alter session set current_schema = scott --改变用户缺省schema名
session altered.
sql> select from test
name
----------
scott
sql> select owner table_name from dba_tables where table_name=upper(' test' )
owner table_name
------------------------------ ------------------------------
scott test
system test
--上面这个查询就是我说将schema作为user的别名的依据。
实际上在使用上，schema与user完全一样，没有什么区别，在出现schema名的地方也可以出现user名。






如以下SQL语句创建了两个物理Schema：


    create schema SCHEMA_A;
    create table SCHEMA_A.CUSTOMERS(ID int not null,……);


    create schema SCHEMA_B;
    create table SCHEMA_B.CUSTOMERS(ID int not null,……);




注：1、改变用户的缺省schema：alter session set current_schema = xyz


    2、增加用户对某个表空间的权限：alter user username quota unlimited on tablespname


创建用户的方法是这样的：
create user 用户名 identified by 密码 
default tablespace 表空间名 
temporary tablespace 表空间名 




在oracle中，数据库是由表空间来组成的，而表空间里面是具体的物理文件---数据文件


数据库-》表空间-》数据文件






ORACLE 下新建 SCHEMA
2014-02-05 12:44
CREATE TABLESPACE EDI


    NOLOGGING


    DATAFILE '/oracle/oradata/wmtst/data/edi01.dbf' SIZE 2048M AUTOEXTEND ON


    NEXT 200m maxsize 4096m


    EXTENT MANAGEMENT LOCAL


    ONLINE


    SEGMENT SPACE MANAGEMENT AUTO;


 


create user wmedi IDENTIFIED BY wmedi


default tablespace EDI


quota unlimited on EDI;


 


GRANT CONNECT,RESOURCE,IMP_FULL_DATABASE TO wmedi;




Oracle数据库中Schema和User的关系是一一对应的，也就是说一个Schema只对应一个User，一个User对应一个Schema


========================创建和设置权限======================
oracle创建用户并创建其他schemal的相关视图和同义词
1 创建一个用户


CREATE USER "XXX" IDENTIFIED BY XXX ACCOUNT UNLOCK PROFILE "DEFAULT";
grant connect, resource to XXX;
 


2 给这个用户查询其他schemal上的表的权限


 
GRANT SELECT ON wf_dataexport TO XXX;
GRANT SELECT ON dbmis2_sql_exe_his TO XXX;
 


3 在新用户上建立视图，用来查询其他schemal的表


 
CREATE VIEW "XXX"."v_XXX_wf_dataexport" AS select * from idb.wf_dataexport;
CREATE VIEW "XXX"."v_XXX_dbmis2_sql_exe_his" AS select * from idb.dbmis2_sql_exe_his;
 


4 建立一个同义词


create or replace synonym XXX.base_aone_app for base_aone_app;
 


以上操作都在原有scheaml执行，并使用dba账号。
========================创建和设置权限======================




Schema是基于用户的, 在数据库中创建一个用户，就创建了一个和用户名相同的Shema。


Namespace是基于数据库的.




create user test_vss identified by test_vss default tablespace test quota 100m on users; 
grant all privileges to test_vss;


select * from test_vss.test




===========创建vss的schema===================
select * from dba_data_files  这条语句是查询所有的表空间路径的


登录后
创建表空间：
create tablespace tvss datafile 'E:\APP\VSS.ORA' size 1000m


创建账号和密码 同时对应表空间:
create user VSS identified by VSS default tablespace tvss quota 500m on users; 


分配权限：
grant all privileges to VSS;


测试：用VSS来进行登录(密码也是VSS)
创建一张测试表：
create table test(name char(10))
为测试表插入语句：
insert into test values('vss_test' )


测试：用root来进行登录(密码也是root)
select * from VSS.Test
===========创建vss的schema===================




######################################可以省略################################
解决表空间很大，总显示空间不足的问题
ORA-01536:space   quota   exceeded   for   table   space   'AAA '
二、三个解决办法： 
        (1)   alter   user   USERNAME   quota   100M   on   TABLESPACENAME; 
        (2)   alter   user   USERNAME   quota   unlimited   on   TABLESPACENAME; 
        (3)   grant   unlimited   tablespace   to   USERNAME;
quota是为了限制用户对表空间的使用 
比如你限制用户a在tablespace   a中的quota为10m，当用户a在tablespace   a中的数据量达到10m后，无论你的tablespace   a中有多少空间，a都无法再使用tablespace   a   了。 
所以你需要 
alter   user   a   quota   1000M   on   TABLESPACENAME; 
alter   user   a   quota   unlimited   on   TABLESPACENAME; 
grant   unlimited   tablespace   to   a 
你可以查询dba_ts_quotas来获取相关信息


==========修改表空间大小和用户使用表空间的大小==============
修改oracle的表空间的大小（用管理员用户登陆,表空间文件需要修改为正确路径）
alter database datafile 'g:\tablespace\pegnius01.dbf' resize 1500M;


修改用户在表空间中的使用大小：
alter   user   USERNAME   quota   1000M   on   TABLESPACENAME; 
==========修改表空间大小和用户使用表空间的大小==============
######################################可以省略################################


修改oracle的表空间的大小（用管理员用户登陆,表空间文件需要修改为正确路径）
alter database datafile 'g:\tablespace\pegnius01.dbf' resize 1500M;




若修改某一个用户密码， 修改用户口令 格式为：
alter user 用户名 identified by 新密码；
以 apps 为例，密码修改为 123456. 可输入
alter user apps identified by 123456;




分页：
select * from (
select rownum as no ,vss_submit_info.* from vss_submit_info) where no>0 and no<=10




1、 由于 char 是以固定长度的， 所以它的速度会比 varchar2 快得多!但程序处理起来要
麻烦一点，要用 trim 之类的函数把两边的空格去掉
2、  Varchar2 一般适用于英文和数字，Nvarchar2 适用中文和其他字符，其中 N 表示 Unicode
常量，可以解决多语言字符集之间的转换问题 
3、 Number(4,2) 指的是整数占 2 位，小数占 2 位(99.994 可以。99.995 不行，因为是
四舍五入)
4、 Number 默认为 38 位




第一范式：字段要设计的不可再分
Name 字段 可拆分成 FirstName+LastName


第二范式： 两个表的关系，在第三张关系表中体现
比如学生和课程表。为多对多的关系。这种关系需要在第三张表中体现


第三范式：多张表中，只存关系，不存具体信息） （具体开发中用的最多）(用主键值作为别的表的外键值)
比如： emp,dept
如果一对多用第三张表（关系表）来表示，则会出现问题。 （一个员工可能属于多个部门，
显然这是不符合现实逻辑的）




grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限




1、Oracle用户新增
create user lisi identified by lisi;


注：create user是创建数据库用户，后面跟用户名称，identified by是设置用户密码




2、把“连接”数据库权限授给新增用户lisi
grant connect to lisi;


注:grant：Oracle数据库授权的关键字
   connect：Oracle数据库的一个默认角色，只有连接上数据库的权限


3、把scott用户的表授权给lisi用户
grant select on emp to lisi;


4、回收用户权限
revoke select on emp from lisi;


5、用户密码修改
用lisi账号登录
alter  user lisi identified by 密码;


6、用户删除
drop user 用户名;
或者drop user 用户名 cascade;(这将会删除与用户相关联的表)




数据库系统权限（Database System Privilege）允许用户执行特定的命令集。
例如，CREATE TABLE权限允许用户创建表，GRANT ANY PRIVILEGE 权限允许用户授予任何系统权 限。 


数据库对象权限（Database Object Privilege）使得用户能够对各个对象进行某些操作。
例如DELETE权限允许用户删除表或视图的行，
SELECT权限允许用户通过select从表、 视图、序列（sequences）或快照 （snapshots）中查询信息。 


三、3种标准角色 


    Qracle为了兼容以前的版本，提供了三种标准的角色（role）：CONNECT、RESOURCE和DBA。 


    1. CONNECT Role(连接角色) 


    临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECTrole。CONNECT是使用Oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、 


insert、update和delete等，才会变得有意义。拥有CONNECT role的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词（synonym ）、会话（session）和与 


其他数据库的链（link）。 


    2. RESOURCE Role(资源角色) 


    更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index） 


和簇（cluster）。 


    3. DBA Role(数据库管理员角色) 


    DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。SYSTEM由DBA用户拥有。下面介绍一些DBA经常使用的典型权限。 


   （1）grant（授权）命令 


    下面对刚才创建的用户user01授权，命令如下： 


    grant connect, resource to user01; 


   （2）revoke（撤消）权限 


    已授予的权限可以撤消。例如撤消（1）中的授权，命令如下： 


    revoke connect, resource from user01; 


    一个具有DBA角色的用户可以撤消任何别的用户甚至别的DBA的CONNECT、RESOURCE 和DBA的其他权限。当然，这样是很危险的，因此，除非真正需要，DBA权限不应随便授予那 


些不是很重要的一般用户。 


    撤消一个用户的所有权限，并不意味着从Oracle中删除了这个用户，也不会破坏用户创建的任何表；只是简单禁止其对这些表的访问。其他要访问这些表的用户可以象以前那 


样地访问这些表。 




查看当前用户的缺省表空间


　　SQL>select username,default_tablespace from user_users;


　　查看当前用户的角色


　　SQL>select * from user_role_privs;


系统数据字典 DBA_TABLESPACES 中记录了关于表空间的详细信息：


select * from sys.dba_tablespaces;




查看Oracle用户占了哪几个表空间及大小 (2011-11-22 16:35:47)转载▼
标签： oracle 杂谈 分类： 程序开发
select *
  from (select owner || '.' || tablespace_name name, sum(b) g
          from (select owner,
                       t.segment_name,
                       t.partition_name,
                       round(bytes / 1024 / 1024 / 1024, 2) b,
                       tablespace_name
                  from dba_segments t)
         where owner not in
               ('SYS', 'OUTLN', 'SYSTEM', 'TSMSYS', 'DBSNMP', 'WMSYS')
         group by owner || '.' || tablespace_name)
 order by name




查看表空间使用情况：
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;