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

uiyujyht

连接超级用户:

1 2 3

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

查数据文件:

1 2 3 4 5 6 7 8 9

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.dbf 6 rows selected.

查看所有表空间

1 2 3 4 5 6 7 8 9 10

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              YES NO  YES      7 TS_SECKEY_ADMIN          YES NO  YES 7 rows selected.

查看表空间的数据文件

1 2 3 4 5 6 7 8 9

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_ADMIN 6 rows selected.

创建表空间

1 2 3 4 5 6 7 8 9 10 11 12

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;

删除表空间

1 2	SQL> DROP TABLESPACE ts_seckey_admin INCLUDING CONTENTS AND DATAFILES; SQL> DROP TABLESPACE ts_seckey_op INCLUDING CONTENTS AND DATAFILES;

查看表所在的表空间

1	select table_name ,tablespace_name from all_tables/dba_tables/user_tables;

删除用户

1	drop user  user_name cascade;

创建用户

1 2 3

CREATE USER user_name  PROFILE DEFAULT     IDENTIFIED BY SECMNG DEFAULT TABLESPACE USERS     ACCOUNT UNLOCK;

创建新用户方案 SECMNGUSER1

1 2 3 4 5 6

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;

授权:

1 2 3

GRANT DELETE ON SECMNG.SRVCFG TO "SECMNGADMIN"; GRANT INSERT ON SECMNG.SRVCFG TO "SECMNGADMIN"; GRANT UPDATE ON SECMNG.SRVCFG TO "SECMNGADMIN";