How to create tablespaces and users in Oracle database

How to create table space and user for oracle database

I just answered this question two days ago, so I’ll answer it again:

Before creating a user, you need to create a table space first to prevent the new user from overflowing the default table space due to excessive data volume and to facilitate management.

The creation statement of the table space is similar to the general creation statement and is used to specify the location where the data is saved. Usually, the location of the table space is located under %ORACLE_HOME%/oradata/orcl. The specific creation statement is:

1.Temporary table space

create temporary tablespace tablespace name --Create temporary tablespace

tempfile 'path\filename.dbf' --the path of the temporary table space and the name of the temporary table space

size 50m autoextend on next 50m --The table space space size is 50M, automatically increased, the step size is 50M, and can be followed by the maximum value, such as maxsize 20480m

extent management local； --Table space management method

2. Create a table space, where data is stored

create tablespace tablespace name

datafile 'path\filename.dbf'

size 50m autoextend on next 50m maxsize 20480m

extent management local;

3. Create a user and specify a default table space

create user username identified by password

default tablespace data tablespace name (the name of the tablespace created in the second step)

temporary tablespace Temporary tablespace name (the name of the tablespace created in the first step)

4.Empowerment

Generally, if there is no special requirement, you only need to grant two permissions

grant connect,resource to username;

If you want to give dba permissions

grant DBA to username

Revoke dba permissions:

revoke dba from username;

How to create a new table space in oracle

Create a new Command Window, enter the command at the prompt, and press Enter to execute it

create tablespace dbspace datafile 'D:\oracle\product\10.2.0\oradata\orcl\dbspace.dbf' size 400M autoextend on next 10m maxsize unlimited; --Create tablespace

DROP TABLESPACE dbspace INCLUDING CONTENTS AND DATAFILES;--delete tablespace

in

1) DATAFILE: Table space data file storage path

2) SIZE: Initially set to 200M

3) UNIFORM: The specified area size is 128k. If not specified, the area size defaults to 64k

4) The space name histdb and the data file name histdb.dbf should not be the same, you can name them arbitrarily.

5) AUTOEXTEND ON/OFF means starting/stopping automatic expansion of table space

6) alter database datafile ' D:\oracle\product\10.2.0\oradata\orcl\histdb.dbf ' resize 500m; //Manually modify the data file size to

The following commands are used to create a user for the table space and set the user's default table space to the newly created table space

create user test1 identified by test1 default tablespace dbspace;

alter database default tablespace dbspace;

create user test identified by test;

select username, default_tablespace defspace from dba_users where;

Please tell me how to use PLSQL to build oracle tables, table space settings, management, import and export data

Create the table space first and then the table:

Step one: Create table space

CREATE TABLESPACE S2SHDWZ_DATA DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\S2SHDWZDATA01.dbf' SIZE 256M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

Explanation: S2SHDWZ-> is the space name, 636f7079e79fa5e9819331333264663665. You need to know which disk your Oracle is installed on.

Step 2: Create temporary table space

CREATE TEMPORARY TABLESPACE S2SHDWZ_TEMP TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\S2SHDWZTEMP01.dbf' SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

Step 3: Create a connection

CREATE USER S2SHDWZ PROFILE DEFAULT IDENTIFIED BY "s2shdwz123" DEFAULT TABLESPACE INVEST_DATA TEMPORARY TABLESPACE INVEST_TEMP ACCOUNT UNLOCK

GRANT CONNECT TO S2SHDWZ

Step 4: Authorization and submission

GRANT DBA TO S2SHDWZ

COMMIT

Then you create a database in sql developer and create tables in the library. You can create tables and export data manually, or you can write commands to do it. After using the above steps, I was able to succeed!

Hope it’s helpful to you, haha!

The above is the detailed content of How to create tablespaces and users in Oracle database. For more information, please follow other related articles on the PHP Chinese website!