如何在oracle 11g中创建一个新模式/新用户?[英] how to create a new schema/new user in oracle 11g?

本文是小编为大家收集整理的关于如何在oracle 11g中创建一个新模式/新用户?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我已经申请了一家公司的实习,作为一个问题,他们要求我为他们的公司创建一个具有某些要求的架构并将 ddl 文件邮寄给他们.我已经安装了 oracle 11g express edition,但是我如何创建oracle 11g 中的新架构?我在网上搜索了解决方案,但可以理解该怎么做.创建架构后,我应该邮寄哪个文件?

推荐答案

一般来说,oracle 中的 schema 和用户是一样的.创建用户时,Oracle 数据库会自动创建模式.具有 DDL 文件扩展名的文件是 SQL 数据定义语言文件.

创建新用户(使用 SQL Plus)

基本 SQL Plus 命令:

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

打开 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

要登录的用户至少需要创建会话权限,所以我们必须将此权限授予用户:

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;

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

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"

更多信息:

DDL

DBMS_METADATA

架构对象

架构和用户之间的区别

特权

创建用户/模式

创建表空间

SQL Plus 命令

本文地址:https://www.itbaoku.cn/post/597381.html

问题描述

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 11g express edition,but how do i create a new schema in oracle 11g?i have searched in the net for a solution but could understand what to do.and after creating a schema ,which file should i mail them?

推荐答案

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.

Creating new user (using SQL Plus)

Basic SQL Plus commands:

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

Open SQL Plus and log:

/ as sysdba

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.

Create an user:

SQL> create user johny identified by 1234;

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

SQL> select username from dba_users;

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;

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 who's 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).

Show already created tablespaces:

SQL> select tablespace_name from dba_tablespaces;

Create tablespace:

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;

Create the user:

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

Grant some privileges:

SQL> grant create session to johny;
SQL> grant create table to johny;
SQL> grant unlimited tablespace to 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  );

Insert data:

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

Select:

SQL> select * from johny_table;

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

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)

For table:

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;

Result:

  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"

For index:

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;

Result:

  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"

More information:

DDL

DBMS_METADATA

Schema objects

Differences between schema and user

Privileges

Creating user/schema

Creating tablespace

SQL Plus commands