Shulou(Shulou.com)06/01 Report--

How to create oracle table space in linux, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

1. Log in to the server

2. Check whether the disk space is large enough df-h

-h more current disk space and usage are displayed in a more readable manner

[root@rac1] # df-h

Filesystem Size Used Avail Use% Mounted on

/ dev/sda1 2.9G 2.3G 521M 82% /

None 209m 0 209m 0% / dev/shm

/ dev/sda2 4.5G 1.8G 2.5G 42% / U01

/ dev/sde1 512M 80M 433M 16% / ocfs

The-h parameter above the-H root is the same, but when rooting, use 1000 instead of 1024 for capacity conversion

[root@rac1] # df-H

Filesystem Size Used Avail Use% Mounted on

/ dev/sda1 3.1G 2.4G 546m 82% /

None 219m 0 219m 0% / dev/shm

/ dev/sda2 4.8G 1.9G 2.7G 42% / U01

/ dev/sde1 537M 84M 454M 16% / ocfs

-k displays disk usage in units

[root@rac1] # df-k

Filesystem 1K-blocks Used Available Use% Mounted on

Su-oracle switches to oracle user (a user name of linux)

3. Create a new folder under the / home/oracle/oradata directory, which will be used to create the tablespace.

Cd / home/oracle/oradata

Mkdir abc

4. Sqlplus "/ as sysdba" (log in to the database as dba, the superuser of the system)

5. Create temporary tablespaces

Before creating a user, you must first create two table spaces, temporary table space and database table space, otherwise it is not good to use the default table space of the system.

# if the disk space is enough, the temporary tablespace is set to 5g, and the database tablespace is set to 5g, but it is best not to exceed 20g, depending on the specific disk situation.

# query the absolute path of temporary tablespace files. If necessary, you can write an absolute path through a query. Generally, ${ORACLE_HOME} is fine.

Select name from v$tempfile

# automatic Tablespace extension 1 ORACLE_HOME ${Tablespace}

Create temporary tablespace NOTIFYDB_TEMP tempfile'{ORACLE_HOME}\ oradata\ NOTIFYDB_TEMP.bdf' size 1024m reuse autoextend on next 20m maxsize unlimited

# automatic expansion of tablespace 2:

Create temporary tablespace abc_temp tempfile'/home/oracle/oradata/abc/abc_temp.dbf' size 5g autoextend on next 100m maxsize 10240m extent management local

# generally speaking, tablespaces should not be set to auto-expand:

Create temporary tablespace ngcclog_tmp01 tempfile'/data/oradata/hbstylog/ngcclog_tmp01.dbf' size 5g autoextend off

Description:

1) abc_temp temporary tablespace name

2) / home/oracle/oradata/abc where database files are stored is generally the folder where control files, data files and log files are installed after the database is installed, plus the name of the tablespace to be created + dbf (data file)

3) initial size of 1024m tablespace

4) automatic growth size of 100m tablespace

5) maximum size of 10240m tablespace

6. Create a data tablespace

-- query the absolute path of the user tablespace file:

Select name from v$datafile

# automatic tablespace expansion: ${ORACLE_HOME}

Create tablespace NOTIFYDB datafile'{ORACLE_HOME}\ oradata\ notifydb.dbf' size 100m reuse autoextend on next 40m maxsize unlimited default storage (initial 128k next 128k minextents 2 maxextents unlimited)

# automatic expansion of tablespace:

Create tablespace abc logging datafile'/home/oracle/oradata/abc/abc.dbf' size 1024m autoextend on next 100m maxsize 10240m extent management local

# generally speaking, tablespaces should not be set to auto-expand:

Create tablespace ngcc_log_dataspc_main datafile'/data/oradata/hbstylog/ngcc_log_dataspc_main01.dbf' size 5g autoextend off

7. Create a user and specify a tablespace

Create user abc identified by abc default tablespace abc temporary tablespace abc_temp

Create user ngcc_log identified by ngcc_log default tablespace ngcc_log_dataspc_main temporary tablespace ngcclog_tmp01

8. Grant permissions to users

Grant dba to abc; (all dba permissions are granted to the abc user)

Grant dba to ngcc_log

Grant connect,resource to ngcc_log; / / grant resource to ngcc_log this is an authorization for tablespaces

Grant select any table to ngcc_log

Grant delete any table to ngcc_log

Grant update any table to ngcc_log

Grant insert any table to ngcc_log

After doing this, we can log in to the specified instance using ngcc_log/ngcc_log; and create our own table.

Delete tablespaces:

1. View user permissions

-- check that the user needs to have the permission of drop tablespace. If not, use a more advanced user (such as sys) to authorize it first.

Select a2.username,a1.privilege from dba_sys_privs a1, user_role_privs a2

Where a1.privilege = 'DROP TABLESPACE'

And a1.grantee = a2.granted_role

2. Delete temporary tablespace

-- View temporary tablespace files

Select name from v$tempfile

-- View the relationship between the user and the tablespace

Select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS

-- if a user's default temporary tablespace is NOTIFYDB_TEMP, it is recommended to change it

Alter user xxx temporary tablespace tempdefault

-set tempdefault to the default temporary tablespace

Alter database default temporary tablespace tempdefault

Delete tablespace NOTIFYDB_TEMP and its containing data objects and data files

Drop tablespace NOTIFYDB_TEMP including contents and datafiles

3. Delete user tablespace

-- View tablespace files

Select name from v$datafile

Stop the online use of tablespaces

Alter tablespace tablespace name offline

Delete tablespace NOTIFYDB_TEMP and its containing data objects and data files

Drop tablespace NOTIFYDB_TEMP including contents and datafiles

Operations related to Oracle user rights query:

-- View all users

Select * from all_users

-- View current user information

Select * from user_users

-- View the role of the current user

Select * from user_role_privs

-- View the permissions of the current user

Select * from user_sys_privs

-- View the table actionable permissions of the current user

Select * from user_tab_privs

-- check the constraints of a table and note that the table name should be capitalized

Select * from user_constraints where table_name='TBL_XXX'

Look at all the indexes of a table and note that the table name should be capitalized

Select index_name,index_type,status,blevel from user_indexes where table_name = 'TBL_XXX'

Check the composition of the index and note that the table name should be capitalized

Select table_name,index_name,column_name, column_position FROM user_ind_columns WHERE table_name='TBL_XXX'

Detailed information about tablespaces is recorded in the system data dictionary DBA_TABLESPACES

Select * from sys.dba_tablespaces

-- View the user sequence

Select * from user_sequences

-- View the database sequence

Select * from dba_sequences

-- other ways to write a statement with permissions attached to a user:

Create user repl@'%' identified by '123'

Grant select ON *. * to repl@'%'

Grant permissions ON database. Table to user @ 'address'

This is the answer to the question about how to create oracle table space in linux. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.