Oracle table file, table space, user, create and delete

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/C0/wKioL1fJOXjCNIiFAAAeVsMyoTA090.png "title=" Untitled. png "alt=" Wkiol1fjoxjcniifaaaevsmyota090.png "/>

" This article declined to reprint, Original from http://990487026.blog.51cto.com"

To connect to a super User:

[Email protected] ~]$ sqlplus/nologsql> conn/as sysdbaconnected.

Check the data file:

Sql> select * from V$dbfile; 4/OPT/ORACLE/APP/ORADATA/ORCL/USERS01.DBF 3/opt/oracle/app/oradata/orcl/undotbs01.dbf 2/opt/oracle/app/oradata/ ORCL/SYSAUX01.DBF 1/opt/oracle/app/oradata/orcl/system01.dbf 5/opt/oracle/app/oradata/orcl/example01.dbf 6/opt/ Oracle/app/oradata/orcl/ts_seckeymng_admin.dbf6 rows selected.

View all table spaces

Sql> select * from V$tablespace; 0 SYSTEM Yes No Yes 1 Sysaux Yes No Yes 2 UNDOTBS1 Yes No Yes 4 USERS Yes No Yes 3 TEMP no no Yes 6 EXAMPLE Y ES No Yes 7 ts_seckey_admin Yes NO YES7 rows selected.

View data files for tablespace

Sql> Select File_name,tablespace_name from dba_data_files;/opt/oracle/app/oradata/orcl/users01.dbf USERS/opt/ ORACLE/APP/ORADATA/ORCL/UNDOTBS01.DBF undotbs1/opt/oracle/app/oradata/orcl/sysaux01.dbf SYSAUX/opt/oracle/app/ ORADATA/ORCL/SYSTEM01.DBF system/opt/oracle/app/oradata/orcl/example01.dbf example/opt/oracle/app/oradata/orcl/ TS_SECKEYMNG_ADMIN.DBF ts_seckey_admin6 rows selected.

CREATE TABLE Space

create tablespace ts_seckey_admin      logging      datafile  '/opt/oracle/app/oradata/orcl/ts_seckeymng_admin.dbf '  SIZE 50M     EXTENT MANAGEMENT LOCAL;     Create tablespace ts_seckey_op    logging     datafile   '/OPT/ORACLE/APP/ORADATA/ORCL/TS_SECKEYMNG_OP.DBF '  size 200m    extent  MANAGEMENT LOCAL; alter database datafile  '/opt/oracle/app/oradata/orcl/ts_seckeymng_admin.dbf '  AUTOEXTEND  ON NEXT   50M MAXSIZE UNLIMITED; alter database datafile  '/opt/oracle/app/oradata/orcl/ts_seckeymng_op.dbf '  AUTOEXTEND  on next  200m maxsize unlimited; 

Delete Table space

Sql> DROP tablespace ts_seckey_admin including CONTENTS and datafiles; Sql> DROP tablespace ts_seckey_op including CONTENTS and datafiles;

To view the table space where the table resides

Select table_name, tablespace_name from All_tables/dba_tables/user_tables;

Delete User

Drop user user_name cascade;

Create user

CREATE USER user_name Profile default identified by SECMNG default tablespace USERS account UNLOCK;

Create a new user scenario SECMNGUSER1

CREATE USER "secmngadmin" profile "default" identified by "123456" Default Tablespace "USERS" account UNLOCK; GRANT "CONNECT" to "secmngadmin"; GRANT Select any TABLE to "secmngadmin"; GRANT resource to Secmngadmin; GRANT create session to Secmngadmin;

Authorized:

GRANT DELETE on Secmng. Srvcfg to "secmngadmin"; GRANT INSERT on Secmng. Srvcfg to "secmngadmin"; GRANT UPDATE on Secmng. Srvcfg to "secmngadmin";

This article from "Soul Bucket Luo" blog, declined reprint!

Oracle table file, table space, user, create and delete