oracle数据库创建及删除表空间、用户和增删改查

数据库

查看sql数据库中的所有表空间

select * from dba_data_files;

删除表空间

SQL> drop tablespace db_data including contents and datafiles;

创建临时表空间

SQL> create temporary tablespace db_temp tempfile '/opt/oracle/oradata/abc/abc_temp.dbf' size 100m autoextend on next 50m maxsize unlimited extent management local;

创建表空间

SQL> create tablespace db_data logging datafile '/opt/oracle/oradata/abc/db_data.dbf' size 100m autoextend on next 50m maxsize unlimited extent management local;

创建用户

SQL> create user lzh identified by lzh default tablespace db_data temporary tablespace db_temp;

授权

SQL> grant dba to lzh;
SQL> grant connect,resource to lzh;

查看定义路径

 select * from dba_directories;

创建路径

SQL> create directory dirdp as 'G:\eposp';

授予权限

SQL> grant read,write on directory dirdp to system;

扩展表空间大小

SQL> alter database datafile '/opt/oracle/oradata/abc/db_data.dbf' autoextend on next 100m maxsize unlimited;

删除所有表的语句

 select 'drop table '||table_name||';' from user_tables;

删除表

drop table T_C_ACC_TRAN_TYPE_LIMIT;

查询字符集

select userenv('language') from dual;

创建表

create table T_B_TRAN_TYPE(TRAN_TYPE_ID VARCHAR2(6) PRIMARY KEY,NAME VARCHAR2(64) NOT NULL,FEE_DIR CHAR(1));

若要求按照以下的格式进行结果输出，如 ：NO:7469,Name:SMITH,Job:CLERK

SQL>select 'NO:'||empno||',Name:'||ename||',Job:'||job from emp;

导入dmp文件

oracle@linux-dusv:~> imp lzh/lzh@ORCL file=/opt/oracle/space/eposp38_20130401.dmp log=/opt/oracle/space/imp.log full=y

将数据库中LZH用户的表导出

exp lzh/lzh@ORCL file=/opt/oracle/space/exp0809_01.dmp owner="(LZH)"

增删改查

增

insert into T_B_ACC_TYPE (ACC_TYPE_ID, NAME)
values ('11111111', '测试1');

删

delete from T_B_ACC_TYPE where ACC_TYPE_ID=11111111;

改

update T_B_ACC_TYPE set name='测试99999' where acc_type_id=11111111;

查

select * from T_C_ACC_TRAN_TYPE_LIMIT where acc_type_id='01049901';