sql - 如何在 Oracle 数据库 11g 中创建新模式/新用户？

Question

我申请了一家公司的实习机会，作为一个问题，他们要求我根据特定要求为他们的公司创建一个架构，并将 DDL 文件邮寄给他们。我已经安装了 Oracle 数据库 11g 快捷版，但如何在 Oracle 数据库 11g 中创建新模式？我在网上搜索了解决方案，但我不明白该怎么做。创建架构后，我应该邮寄哪个文件？

Answer 1

一般来说oracle中的schema就是一个用户。当您创建用户时，Oracle 数据库会自动创建一个模式。具有 DDL 文件扩展名的文件是 SQL 数据定义语言文件。

创建新用户(使用 SQL Plus)

基本的 SQL Plus 命令:

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

打开 SQL Plus 并记录:

/ as sysdba

sysdba 是一个角色，类似于 unix 上的“root”或 Windows 上的“Administrator”。它无所不知，无所不能。在内部，如果您以 sysdba 身份连接，您的架构名称将显示为 SYS。

创建用户:

SQL> create user johny identified by 1234;

查看所有用户，查看用户johny是否在:

SQL> select username from dba_users;

如果您现在尝试以 johny 身份登录，您会收到错误消息:

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

登录用户至少需要创建 session 权限，因此我们必须将此权限授予用户:

SQL> grant create session to johny;

现在您可以作为用户 johny 进行连接:

username: johny
password: 1234

要删除用户，您可以删除它:

SQL> drop user johny;

---

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

显示已经创建的表空间:

SQL> select tablespace_name from dba_tablespaces;

创建表空间:

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

创建临时表空间(临时表空间是数据库中的空间分配，可以包含仅在 session 期间持续存在的 transient 数据。进程或实例故障后无法恢复此 transient 数据。):

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 身份登录并检查他有哪些权限:

SQL> select * from session_privs;

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

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

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 的帮助下)

对于表格:

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"

更多信息:

动态链接

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

DBMS_元数据

• 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
• Difference between a user and a schema in 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 加命令

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