ORACLE创建、授权、导入、导出

1. sqlplus /nolog
2. sqlplus / as sysdba

1. shutdown immediate ;
2. startup

DROP USER XX CASCADE;

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

create tablespace FCVS
datafile'E:\app\Eric\oradata\orcl\FCVS.dbf' 
size 1024m  
autoextend on  
maxsize 2048m  
extent management local;

1. create user fcvs identified by fcvs
2. default tablespace FCVS
3. temporary tablespace temp;

1. grant resource to fcvs;
2. grant connect to fcvs;
3. grant dba to fcvs;

exp fcvs/fcvs@FCVS file=home/oracle/backup.dmp full=y;
exp fcvs/fcvs@127.0.0.1/FCVS file=D:\expdat.dmp full=y;
exp fcvs/fcvs@localhost/FCVS file=D:\expdat.dmp full=y;
imp fcvs/fcvs@FCVS file=home/oracle/backup.dmp full=y;
imp fcvs/fcvs@127.0.0.1/FCVS file=D:\expdat.dmp full=y;
imp fcvs/fcvs@localhost/FCVS file=D:\expdat.dmp full=y;

create directory dump_dir as '/data/oracle/my_dump_dir/';

select * from dba_directories;

grant read,write on directory dump_dir to fcvs;

--1)按用户导
expdp fcvs/fcvs@127.0.0.1:1521/orcl schemas=fcvs dumpfile=expdp.dmp DIRECTORY=dump_dir;
--2)并行进程parallel
expdp fcvs/fcvs@127.0.0.1:1521/orcl directory=dump_dir dumpfile=scott3.dmp parallel=40 job_name=scott3
--3)按表名导
expdp fcvs/fcvs@127.0.0.1:1521/orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dump_dir;
--4)按查询条件导
expdp fcvs/fcvs@127.0.0.1:1521/orcl directory=dump_dir dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';
--5)按表空间导
expdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
--6)导整个数据库
expdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

--1)导到指定用户下
impdp fcvs/fcvs DIRECTORY=dump_dir DUMPFILE=expdp.dmp SCHEMAS=scott;
--2)改变表的owner
impdp fcvs/fcvs DIRECTORY=dump_dir DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;
--3)导入表空间
impdp fcvs/fcvs DIRECTORY=dump_dir DUMPFILE=expdp.dmp TABLESPACES=example;
--4)导入数据库并忽略错误(常用)
impdp fcvs/fcvs DIRECTORY=dump_dir DUMPFILE=expdp.dmp FULL=y ignore=y;
--5)追加数据
impdp fcvs/fcvs DIRECTORY=dump_dir DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION

rem--------------------------------------------------------------------------------------
rem windows环境下Oracle数据库的自动备份脚本。
rem 可以将本批处理设成windows任务计划下自动执行。
@echo off
@echo ===================================================================================
@echo windows环境下Oracle数据库自动备份脚本
@echo 说明：启动备份时，需要配置以下变量
@echo  1.BACKUP_DIR   指定要备份到那个目录
@echo  2.ORACLE_USERNAME  指定备份所用的Oracle用户名
@echo  3.ORACLE_PASSWORD  指定备份所用的Oracle密码
@echo  4.ORACLE_DB  指定备份所用的Oracle连接名
@echo  5.BACK_OPTION  备份选项，可以为空，也可以为full=y,也可为owner=a用户，b用户 等等...
@echo  6.RAR_CMD  指定RAR命令行压缩工具所在目录
@echo ===================================================================================
rem 以下变量需要根据实际情况配置
set BACKUP_DIR=D:\backup
set ORACLE_USERNAME=root
set ORACLE_PASSWORD=aaaa
set ORACLE_IP=127.0.0.1
set ORACLE_DB=orcl
set BACKUP_OPTION=owner=root
set NOW=%date:~0,4%%date:~5,2%%date:~8,2%
rem 开始备份
exp %ORACLE_USERNAME%/%ORACLE_PASSWORD%@%ORACLE_IP%/%ORACLE_DB% %BACKUP_OPTION% file="%BACKUP_DIR%\%NOW%.dmp" log="%BACKUP_DIR%\%NOW%exp.log
rem 删除n天前的备份数据文件
forfiles /p "D:\backup" /d -n /c "cmd /c echo deleting @file ... && del /f @path"

select * from dba_users;//查看数据库里所有的用户，前提是你是有dba权限的账户；
select * from all_users;查看你能管理的所有用户；
select * from user_users;查看当前用户信息；

select table_name from user_tables;

select * from all_tables where owner='ROOT'；

SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM d

SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object

1. su - oracle
2. cd $ORACLE_HOME/bin

./lsnrctl status

./lsnrctl start