创建表空间、用户、授权

1.连接数据库

su - oracle 
sqlplus /nolog    或者   sqlplus / as sysdba

conn sys/orcl  as  sysdba    

2.创建用户和密码、授权
SQL> create user lfbdc identified by lfbdc;

User created.

SQL> alter user lfbdc default tablespace bdc;

User altered.

SQL> grant dba to lfbdc;

Grant succeeded.

SQL>

3.创建表空间

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/system01.dbf
/oradata/orcl/sysaux01.dbf
/oradata/orcl/undotbs01.dbf
/oradata/orcl/users01.dbf

SQL> create tablespace bdc logging  datafile '/oradata/orcl/bdc.dbf' size 50m autoextend on  next 5m maxsize 10240m extent management local;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/system01.dbf
/oradata/orcl/sysaux01.dbf
/oradata/orcl/undotbs01.dbf
/oradata/orcl/users01.dbf
/oradata/orcl/bdc.dbf

SQL>

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2252664 bytes
Variable Size             956301448 bytes
Database Buffers          218103808 bytes
Redo Buffers                9195520 bytes
Database mounted.
Database opened.
 

1.连接数据库

su - oracle 
sqlplus /nolog    或者   sqlplus / as sysdba

conn sys/orcl  as  sysdba    

2.创建用户和密码、授权
SQL> create user lfbdc identified by lfbdc;

User created.

SQL> alter user lfbdc default tablespace bdc;

User altered.

SQL> grant dba to lfbdc;

Grant succeeded.

SQL>

3.创建表空间

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/system01.dbf
/oradata/orcl/sysaux01.dbf
/oradata/orcl/undotbs01.dbf
/oradata/orcl/users01.dbf

SQL> create tablespace bdc logging  datafile '/oradata/orcl/bdc.dbf' size 50m autoextend on  next 5m maxsize 10240m extent management local;

Tablespace created.


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/system01.dbf
/oradata/orcl/sysaux01.dbf
/oradata/orcl/undotbs01.dbf
/oradata/orcl/users01.dbf
/oradata/orcl/bdc.dbf

4.删除用户表空间

--查看表空间文件
select name from v$datafile;
--停止表空间的在线使用
alter tablespace 表空间名称 offline;
--删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件
drop tablespace NOTIFYDB_TEMP including contents and datafiles; 

5.删除临时表空间

--查看临时表空间文件
select name from v$tempfile;
--查看用户和表空间的关系
select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS;
--如果有用户的默认临时表空间是NOTIFYDB_TEMP的话，建议进行更改
alter user xxx temporary tablespace tempdefault;
---设置tempdefault为默认临时表空间
alter database default temporary tablespace tempdefault;
--删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件
drop tablespace NOTIFYDB_TEMP including contents and datafiles;


--查看所有的用户
select * from all_users;
--查看当前用户信息
select * from user_users;
--查看当前用户的角色
select * from user_role_privs;
--查看当前用户的权限
select * from user_sys_privs;
--查看当前用户的表可操作权限
select * from user_tab_privs;

--查看某一个表的约束,注意表名要 大写
select * from user_constraints where table_name='TBL_XXX';
--查看某一个表的所有索引,注意表名要 大写
select index_name,index_type,status,blevel from user_indexes where table_name = 'TBL_XXX';
--查看索引的构成,注意表名要 大写
select table_name,index_name,column_name, column_position FROM user_ind_columns WHERE table_name='TBL_XXX';

--系统数据字典 DBA_TABLESPACES 中记录了关于表空间的详细信息
select * from sys.dba_tablespaces;

--查看用户序列
select * from user_sequences;
--查看数据库序列
select * from dba_sequences;



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2252664 bytes
Variable Size             956301448 bytes
Database Buffers          218103808 bytes
Redo Buffers                9195520 bytes
Database mounted.
Database opened.



SQL> conn lfbdc/lfbdc
Connected.

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);


INSERT INTO persons VALUES(1,'可可','WANG','BJ','HD');