Oracle/PLSQL: Create a Schema

在Oracle中创建模式涉及到创建用户并赋予相应权限。首先使用CREATE USER语句创建用户,如smithj,并指定默认和临时表空间及配额。然后,通过GRANT语句赋予用户如创建会话、创建表等系统权限。最后,在用户权限设置完成后,可以在该模式下创建表、视图等对象。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Creating a schema in Oracle, can at first, appear to be a little confusing. You might think that the CREATE SCHEMA statement would create your schema, but that is not the case. The CREATE SCHEMA statement is used only to create objects (ie: tables, views, etc) in your schema in a single SQL statement, but does not actually create the schema itself.

To create a schema in Oracle, you need to do the following steps:

Step 1 - Create a new user in Oracle

In essence, a schema is created in Oracle when a user is created. (Learn the syntax for the CREATE USER statement).

We can create a new user with the CREATE USER statement as follows:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called smithj in the Oracle database whose password is pwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would be tbs_temp_01.

Step 2 - Assign privileges to new user in Oracle

The next step in setting up your schema is to assign "system privileges" to the new user smithj.

These "system privileges" will allow our new user to create a session in Oracle as well as create tables, views, triggers, procedures, sequences, and synonyms in the new schema. Here is an example of how we might grant those system privileges:

GRANT create session TO smithj;
GRANT create table TO smithj;
GRANT create view TO smithj;
GRANT create any trigger TO smithj;
GRANT create any procedure TO smithj;
GRANT create sequence TO smithj;
GRANT create synonym TO smithj;

These new privileges are now granted to the user called smithj.

Step 3 - Create objects in the schema

Now that the schema (called smithj) has been created with the necessary privileges, you can create objects in the schema. This can be done one of 2 ways:

  1. Executing individual SQL statements to create each object. This would be done through CREATE TABLE statements and CREATE VIEW statements.
  2. Executing a CREATE SCHEMA statement to create multiple objects in a single SQL statement.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值