使用系统用户登录Oracle
系统用户
sys, system sysman scott scott 默认的密码是 tiger使用系统用户登录
使用 system用户登录 【username/password】 【@server】 【as sysdba|sysoper】 system/root @orcl as sysdba orcl 就是自己设置的服务名 SQL> connect sys/toor as sysdba查看登录用户
SQL> show user User is "SYSTEM" 查看登录用户 dba_users 数据字典 数据字典是数据库提供的表,用于查看数据库的信息。 SQL> desc dba_users
SQL> select username from dba_users;
启用scott用户
启用 scott用户 SQL> alter user scott account unlock; User altered 修改用户密码 SQL> alter user scott identified by 123456;表空间概述
表空间 表空间概述- 理解表空间
- 数据库与表空间
- 表空间与数据文件
- 表空间的分类
- 永久表空间
- 临时表空间
- UNDO表空间
查看用户表空间
dba_tablespaces、 user_tablespaces数据字典
SQL> desc dba_tablespaces; SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE 6 rows selected TMP 索引信息 user 数据库对象 ------------- SQL> desc user_tablespaces SQL> select tablespace_name from user_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE 6 rows selected SQL> Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as scott@WIN_ORCL SQL> select tablespace_name from dba_tablespaces; select tablespace_name from dba_tablespaces ORA-00942: table or view does not exist SQL> select tablespace_name from user_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE 6 rows selected 权限不一样,向下兼容dba_users、user_users数据字典
权限大的可以查看权限小的 SQL> desc dba_users; SQL> select default_tablespace,temporary_tablespace from dba_users where username='SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMP 每一个用户下面的默认表空间和临时表空间设置用户的默认或临时表空间
ALTER USER username DEFAULT|TEMPORARY TABLESPACE tablespace_name SQL> alter user system default tablespace users; User altered SQL> select default_tablespace, temporary_tablespace from dba_users where username= 'SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ USERS TEMP SQL> select default_tablespace, temporary_tablespace from dba_users where username= 'SYSTEM'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------------ ------------------------------ SYSTEM TEMP创建表空间
创建表空间 CREATE 【TEMPORARY】 TABLESPACE tablespace_name TEMPFILE|DATAFILE 'xx.dbf' SIZE XX SQL> create tablespace test1_tablespace datafile 'test1file.dbf' size 10m; Tablespace created SQL> create temporary tablespace temtest1_tablespace tempfile 'tempfile1.dbf' size 10m; Tablespace created SQL> desc dba_data_files SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPACE'; # 注意要大写,否则查不到 FILE_NAME -------------------------------------------------------------------------------- D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF SQL> select file_name from dba_temp_files where tablespace_name='TEMTEST1_TABLESPACE'; FILE_NAME -------------------------------------------------------------------------------- D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEMPFILE1.DBF修改表空间
修改表空间- 修改表空间的状态
设置联机或脱机状态
ALTER TABLESPACE tablespace_name ONLINE | OFFLINE; SQL> alter tablespace test1_tablespace offline; Tablespace altered SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE'; STATUS --------- OFFLINE SQL> alter tablespace test1_tablespace online; Tablespace altered SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE'; STATUS --------- ONLINE设置只读或可读写状态
ALTER TABLESPACE tablespace name READ ONLY | READ WRITE #设置为READ ONLY 以后就不能进行联机和脱机操作 SQL> alter tablespace test1_tablespace read only; Tablespace altered SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE'; STATUS --------- READ ONLY SQL> alter tablespace test1_tablespace read write; Tablespace altered SQL> select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE'; STATUS --------- ONLINE改数据文件
增加数据文件
ALTER TABLESPACE tablespace_name ADD DATAFILE xx.dbf size xx SQL> alter tablespace test1_tablespace add datafile 'test2_file.dbf' size 10m; Tablespace altered SQL> select file_name frOm dba_data_files where tablespace_name='TEST1_TABLESPACE'; FILE_NAME -------------------------------------------------------------------------------- D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF删除数据文件
ALTER TABLESPACE tablespace_name DROP DATAFILE 'filename.dbf 不能删除第一个表空间文件,如果要就需要删除整个表空间 SQL> alter tablespace test1_tablespace drop datafile 'test2_file.dbf'; Tablespace altered SQL> select file_name frOm dba_data_files where tablespace_name='TEST1_TABLESPACE'; FILE_NAME -------------------------------------------------------------------------------- D:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF 更改system用户默认表空间的语句是 alter user system default tablespace xxx删除表空间
删除表空间 DROP TABLESPACE tablespace_name 【INCLUDING CONTENTS】 SQL> drop tablespace temtest1_tablespace including contents; Tablespace dropped<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">