create tablespace ybp_dev datafile 'G:\app\Administrator\oradata\health\ybp_dev1.dbf' size 10m autoextend on;create user 用户名 default tablespace 表空间名 identified by 用户密码;grant connect,resource to 用户;测试连接 conn mpi/1234删除表空间drop tablespace XX including contents and datafiles cascade constraints;//including contents 删除表空间的内容//cascade constraints同时删除tablespace中表的外键参照删除用户drop user USERNAME CASCADE;如提示ora-01940 无法删除已经连接会话的用户select username,sid,serial# from v$session where username like 'HIP4'找到要删除用户的sid,和serial，并删除alter system kill session'532,4562'-------------------------------------------su - oracle -切换oracle用户sqlplus / as sysdba -本地登录sqlplusselect * from dba_directories; -查询路径drop directory dp_dir; -删除目录create directory dp_dir as '/backup/dp_dir'; -创建目录grant read,write on directory dp_dir to public; -赋权目录expdp xx/xx directory=dp_dir dumpfile=xxx.dmp schemas=xxx --导出当有两个数据库时需要加上
用户名/密码@IP：port/SID 例： expdp xx/xx@127.0.0.1：1521/orcl directory=dp_dir dumpfile=xxx.dmp schemas=xxx
例 expdp '"/as sysdba"' directory=dp_dir dumpfile=xx1207.dmp schemas=xximpdp '"/as sysdba"' directory=dp_dir dumpfile=1112mdbyl.dmp --导入.注意此处不加封 封号！！！！impdp '"/as sysdba"' directory=dp_dir dumpfile=1112mdbyl.dmp remap_schema=mdbyl:mdbyl3 --导入改名select file_name,tablespace_name from dba_data_files; --查询数据文件路径select username,default_tablespace from dba_users where username like 'X%'; --模糊查询x开头的用户SELECT * FROM V$SESSION WHERE USERNAME='PINGTAI';alter system kill session '3406,1671';drop user PINGTAI cascade;---------------------------------------------------SELECT t.table_name,t.colUMN_NAME,t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',MENTSFROM User_Tab_Cols t, User_Col_Comments t1WHERE t.table_name = t1.table_nameAND t.column_name = t1.column_name(+);
-------------------------------------------------
select 'analyze table ' || t.table_name || ' compute statistics; ' from user_tables t where table_name not like 'TMP_%'--生成分析语句
select t.table_name,t.num_rows from user_tables t ORDER BY NUM_ROWS DESC;--统计数据量
