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

问题描述

我申请了一家公司的实习机会，作为一个问题，他们要求我为他们的公司创建一个具有特定要求的架构，并将 DDL 文件邮寄给他们.我已经安装了 Oracle 数据库 11g 快捷版，但如何在 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 中的 schema 就是一个用户.创建用户时，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 是一个角色，就像root"一样.在 unix 或管理员"上在 Windows 上.它什么都看，什么都能做.在内部，如果您以 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;

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

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

• http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_1001.htm

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/b14220/schema.htm

架构和用户之间的差异

• https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
• Oracle 中用户和模式之间的区别?

特权

• http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#TTSQL338

创建用户/模式

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

创建表空间

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

SQL Plus 命令

SQL Plus commands

• http://ss64.com/ora/syntax-sqlplus.html

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