Oracle常用操作

1、查询用户：select * from dba_users；

2、查询表空间：Select * From Dba_Tablespaces;

3、创建表空间：create tablespace tablespace_Name DATAFILE 'D:\app\Administrator\oradata\xe\name.dbf' size 100m autoextend on;

4、创建用户：create user user_Name identified by 000000 default tablespace tablespace_Name;

5、授权用户：grant dba to user_Name;

6、删除表空间：DROP TABLESPACE tablespace_Name INCLUDING CONTENTS AND DATAFILES;

7、删除用户：drop user user_Name cascade;

8、oracle数据库exp导出：exp user_name/pwd@Orcl file = d:\20171010.dmp buffer  = 10000000

9、oracle数据库imp导入：imp user_name/pwd@Orcl full = y buffer = 10000000 log = d:\20170715.log file = D:\20171010.dmp

10、oracle数据库expdp导出，impdp导入：

        (1)、先创建路径：cmd->sqlplus /nolog;->conn / as sysdba;   

        (2)、create directory 名称 as '路径';  查询路径：Select * From dba_directoryes;

        (3)、expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp

        (4)、impdp user2/pass2 directory=dumpdir dumpfile=user1.dmp REMAP_SCHEMA=user1:user2 EXCLUDE=USER full=y;

11、数据库连接超时问题：select * from dba_profiles;

create profile connNoTimeout;
select * from dba_profiles where profile='CONNNOTIMEOUT';
alter profile CONNNOTIMEOUT limit COMPOSITE_LIMIT UNLIMITED;
alter profile CONNNOTIMEOUT limit SESSIONS_PER_USER UNLIMITED;
alter profile CONNNOTIMEOUT limit CPU_PER_SESSION UNLIMITED;
alter profile CONNNOTIMEOUT limit CPU_PER_CALL UNLIMITED;
alter profile CONNNOTIMEOUT limit LOGICAL_READS_PER_SESSION UNLIMITED;
alter profile CONNNOTIMEOUT limit LOGICAL_READS_PER_CALL UNLIMITED;
alter profile CONNNOTIMEOUT limit IDLE_TIME UNLIMITED;
alter profile CONNNOTIMEOUT limit CONNECT_TIME UNLIMITED;
alter profile CONNNOTIMEOUT limit PRIVATE_SGA UNLIMITED;
alter profile CONNNOTIMEOUT limit FAILED_LOGIN_ATTEMPTS 10;
alter profile CONNNOTIMEOUT limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile CONNNOTIMEOUT limit PASSWORD_REUSE_TIME UNLIMITED;
alter profile CONNNOTIMEOUT limit PASSWORD_REUSE_MAX UNLIMITED;
alter profile CONNNOTIMEOUT limit PASSWORD_VERIFY_FUNCTION NULL;
alter profile CONNNOTIMEOUT limit PASSWORD_LOCK_TIME 1;
alter profile CONNNOTIMEOUT limit PASSWORD_GRACE_TIME 7;

alter user user_name profile connNoTimeout;