如何在Oracle Database 11g中创建新的架构/新用户? [英] How to create a new schema/new user in Oracle Database 11g?

查看：106
本文介绍了如何在Oracle Database 11g中创建新的架构/新用户?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已申请在一家公司实习，作为一个问题，他们要求我为他们的公司创建具有一定要求的架构，并向其发送 DDL 文件.我已经安装了Oracle数据库11g Express版，但是如何在Oracle数据库11g中创建新的架构?我已经在网上搜索了解决方案，但是我不知道该怎么办.创建架构之后，我应该将它们邮寄到哪个文件?

I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express edition, but how do I create a new schema in Oracle database 11g? I have searched in the net for a solution but I could not understand what to do. And after creating a schema, which file should I mail them?

推荐答案

通常来说，oracle中的模式与用户相同.创建用户时，Oracle数据库会自动创建一个架构.具有DDL文件扩展名的文件是SQL数据定义语言文件.

Generally speaking a schema in oracle is the same as an user. Oracle Database automatically creates a schema when you create a user. A file with the DDL file extension is an SQL Data Definition Language file.

创建新用户(使用SQL Plus)

基本SQL Plus命令:

Basic SQL Plus commands:

  - connect: connects to a database
  - disconnect: logs off but does not exit
  - exit: exists

打开SQL Plus并记录:

Open SQL Plus and log:

/ as sysdba

sysdba是一个角色，类似于Unix上的"root"或Windows上的"Administrator".它看到了所有，可以做所有.在内部，如果您以sysdba身份连接，则架构名称将显示为SYS.

The sysdba is a role and is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.

创建用户:

SQL> create user johny identified by 1234;

查看所有用户，并检查用户johny是否在那里:

View all users and check if the user johny is there:

SQL> select username from dba_users;

如果您现在尝试以johny身份登录，则会出现错误:

If you try to login as johny now you would get an error:

ERROR:
ORA-01045: user JOHNY lacks CREATE SESSION privilege; logon denied

要登录的用户至少需要创建会话特权，因此我们必须向用户授予此特权:

The user to login needs at least create session priviledge so we have to grant this privileges to the user:

SQL> grant create session to johny;

现在，您可以以用户johny的身份进行连接:

Now you are able to connect as the user johny:

username: johny
password: 1234

要摆脱用户，您可以将其删除:

To get rid of the user you can drop it:

SQL> drop user johny;

那是显示如何创建用户的基本示例.它可能更复杂.上面我们创建了一个用户，其对象存储在数据库默认表空间中.为了使数据库整洁，我们应该将用户对象放在自己的空间中(表空间是数据库中可以包含架构对象的空间分配).

That was basic example to show how to create an user. It might be more complex. Above we created an user whose objects are stored in the database default tablespace. To have database tidy we should place users objects to his own space (tablespace is an allocation of space in the database that can contain schema objects).

显示已创建的表空间:

SQL> select tablespace_name from dba_tablespaces;

创建表空间:

SQL> create tablespace johny_tabspace
  2  datafile 'johny_tabspace.dat'
  3  size 10M autoextend on;

创建临时表空间(Temporaty表空间是数据库中的空间分配，其中可以包含仅在会话期间持续存在的瞬态数据.在进程或实例失败后无法恢复此瞬态数据.)

Create temporary tablespace (Temporaty tablespace is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.):

SQL> create temporary tablespace johny_tabspace_temp
  2  tempfile 'johny_tabspace_temp.dat'
  3  size 5M autoextend on;

创建用户:

SQL> create user johny
  2  identified by 1234
  3  default tablespace johny_tabspace
  4  temporary tablespace johny_tabspace_temp;

授予一些特权:

SQL> grant create session to johny;
SQL> grant create table to johny;
SQL> grant unlimited tablespace to johny;

以johny身份登录并检查他具有哪些特权:

Login as johny and check what privileges he has:

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE

具有创建表特权的用户可以创建表:

With create table privilege the user can create tables:

SQL> create table johny_table
  2  (
  3     id int not null,
  4     text varchar2(1000),
  5     primary key (id)
  6  );

插入数据:

SQL> insert into johny_table (id, text)
  2  values (1, 'This is some text.');

选择:

SQL> select * from johny_table;

ID  TEXT
--------------------------
1   This is some text.

要获取DDL数据，可以使用DBMS_METADATA包，该包为您提供了一种从数据库字典中以XML或创建DDL检索元数据并提交XML以重新创建对象的方式." (在 http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm 的帮助下)

To get DDL data you can use DBMS_METADATA package that "provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.". (with help from http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm)

对于表格:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;

结果:

  CREATE TABLE "JOHNY"."JOHNY_TABLE"
   (    "ID" NUMBER(*,0) NOT NULL ENABLE,
        "TEXT" VARCHAR2(1000),
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"

对于索引:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;

结果:

  CREATE UNIQUE INDEX "JOHNY"."SYS_C0013353" ON "JOHNY"."JOHNY_TABLE" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "JOHNY_TABSPACE"

更多信息:

DDL

DBMS_METADATA

DBMS_METADATA

  • http://www.dba-oracle.com/t_1_dbms_metadata.htm
  • http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS026
  • http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm#ADMIN11562

架构对象

架构与用户之间的差异

特权

创建用户/架构

  • http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
  • http://www.techonthenet.com/oracle/schemas/create_schema.php

创建表空间

SQL Plus命令

SQL Plus commands

这篇关于如何在Oracle Database 11g中创建新的架构/新用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆