Oracle 表文件,表空间,用户,的创建 和删除

【本文谢绝转载,原文来自http://990487026.blog.51cto.com】

连接超级用户:

[oracle@oracle11~]$sqlplus/nolog
SQL>conn/assysdba
Connected.

查数据文件:

SQL>select*fromv$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.dbf

6rowsselected.

查看所有表空间

SQL>select*fromv$tablespace;
	0SYSTEM			YESNOYES
	1SYSAUX			YESNOYES
	2UNDOTBS1			YESNOYES
	4USERS			YESNOYES
	3TEMP			NONOYES
	6EXAMPLE			YESNOYES
	7TS_SECKEY_ADMIN		YESNOYES

7rowsselected.

查看表空间的数据文件

SQL>selectfile_name,tablespace_namefromdba_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_ADMIN

6rowsselected.

创建表空间

CREATETABLESPACEts_seckey_admin
LOGGING
DATAFILE'/opt/oracle/app/oradata/orcl/ts_seckeymng_admin.dbf'SIZE50M
EXTENTMANAGEMENTLOCAL;


CREATETABLESPACEts_seckey_op
LOGGING
DATAFILE'/opt/oracle/app/oradata/orcl/ts_seckeymng_op.dbf'SIZE200M
EXTENTMANAGEMENTLOCAL;
ALTERDATABASEDATAFILE'/opt/oracle/app/oradata/orcl/ts_seckeymng_admin.dbf'AUTOEXTENDONNEXT50MMAXSIZEUNLIMITED;
ALTERDATABASEDATAFILE'/opt/oracle/app/oradata/orcl/ts_seckeymng_op.dbf'AUTOEXTENDONNEXT200MMAXSIZEUNLIMITED;

删除表空间

SQL>DROPTABLESPACEts_seckey_adminINCLUDINGCONTENTSANDDATAFILES;
SQL>DROPTABLESPACEts_seckey_opINCLUDINGCONTENTSANDDATAFILES;

查看表所在的表空间

selecttable_name,tablespace_namefromall_tables/dba_tables/user_tables;

删除用户

dropuseruser_namecascade;

创建用户

CREATEUSERuser_namePROFILEDEFAULT
IDENTIFIEDBYSECMNGDEFAULTTABLESPACEUSERS
ACCOUNTUNLOCK;

创建新用户方案SECMNGUSER1

CREATEUSER"SECMNGADMIN"PROFILE"DEFAULT"IDENTIFIEDBY"123456"DEFAULTTABLESPACE"USERS"ACCOUNTUNLOCK;
GRANT"CONNECT"TO"SECMNGADMIN";
GRANTSELECTANYTABLETO"SECMNGADMIN";

GRANTresourceTOSECMNGADMIN;
GRANTcreatesessionTOSECMNGADMIN;

授权:

GRANTDELETEONSECMNG.SRVCFGTO"SECMNGADMIN";
GRANTINSERTONSECMNG.SRVCFGTO"SECMNGADMIN";
GRANTUPDATEONSECMNG.SRVCFGTO"SECMNGADMIN";

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!