854   Oracle RedHat

1,创建临时表空间:
create temporary tablespace uatdb_temp tempfile ‘/newdata/uatdb/uatdb_temp01.dbf’ size 1024m autoextend on next 1024m maxsize 4096m extent management local;

2,创建表空间:
create tablespace uatdb_data datafile ‘/newdata/uatdb/uatdb_data01.dbf’ size 5000m autoextend on next 5000m maxsize 10000m extent management local;

3,创建用户并指定表空间:
create user uatdb identified by uatdb default tablespace uatdb_data temporary tablespace uatdb_temp;

4,删除用户以及用户对象等
drop user ×× cascade
说明: 删除了user,只是删除了该user下的schema objects,是不会删除相应的tablespace的。

5,删除tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

6,导出指定用户数据
exp cwotest/cwotest file=/newdata/test_dba/cwotest_20141218.dmp log=/newdata/test_dba/cwotest_20141218.log owner=cwotest

7,导入一个用户导入另一个用户数据
imp uatdba/uatdba file=/newdata/test_dba/cwotest_20141218.dmp log=/newdata/test_dba/cwotest_to_uatdba_20141218.log fromuser=cwotest touser=uatdba ignore=y constraints=y grants=y

8,查询用户的连接状态
Select username,sid,serial# from v$session where username=’XY1027′;

USERNAME SID SERIAL#
—————————— ———- ———-
XY1027 22 1
XY1027 142 1

9,删除用户连接
Alter system kill session’22,1′;

10,查看有dba权限的用户
select * from sys.dba_role_privs where granted_role=’DBA’

11,查看创建的文件目录
SQL>select * from all_directories;

12,备份表格
expdp cwomall/12345678 directory=BACKUP dumpfile=cwo_table_201412281548.dmp logfile=cwo_table_201412281548.log tables=shopinfo,shopcolunm,goodcolumn

13,清空表格
delete from tablename

14,导入表格
impdp cwomall/12345678 directory=BACKUP dumpfile=cwo_table_20141228.dmp logfile=cwo_table_20141228_to_201412291439.log tables=SHOPINFO,shopcolunm,goodcolumn TABLE_EXISTS_ACTION=APPEND

 

15,增加表空间大小

ALTER TABLESPACE app_data ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF’ SIZE 50M;




Leave a Reply

Your email address will not be published. Required fields are marked *