Oracle基本数据导入操作(完整步骤)
一、连接数据库
sqlpus sys/sys@orcl as sysdba
或者
sqlplus system/system/orcl
二、用sysdba建立表空间和用户
建立表空间
create tablespace TESTSPACE
logging
datafile 'D:\MYSPACE.dbf'
size 32m
autoextend on
next 32m
maxsize UNLIMITED
extent management local;
--附加表空间,防止表空间不够用
--alter tablespace TESTSPACE add datafile 'D:\MYSPACE01.DBF' size 20480m
建立用户
create user TESTSUSER --用户名
identified by 123 --密码
default tablespace TESTSPACE --建立的表空间
temporary tablespace temp; --默认的临时表空间
给新建的用户设置【基本用户】权限
grant
create session,create any table,create any index,create any sequence,create any view,
alter any table,alter any index ,alter any sequence,
drop any table,drop any index,drop any sequence,drop any view ,
select any table,select any sequence,
insert any table,update any table,delete any table,create trigger,create materialized view
to TESTSUSER;
下面的群贤设置难免 会把dba权限 赋给用户,基本不采用。
授予连接,恢复,导入,导出数据库 权限给 用户
grant connect,resource,imp_full_database,exp_full_database to TESTSUSER;
更改用户TESTSUSER 使用默认空间TESTSPACE 的权限为无限配额
alter user TESTSUSER default tablespace TESTSPACE quota unlimited on TESTSPACE;
三、用新账号TESTSUSER登录,导入数据
conn testuser/123@orcl
$imp testuser/123@orcl file = d:\数据库名.dmp fromuser=数据库原先导出时的用户名 touser=testuser ignore=y
-- file = d:\数据库名.dmp 备份的数据库文件全路劲
--ignore=y为不提示警告信息
--如果导入和导出用户名一样,可以省略fromuser和touser参数
Oracle基本数据导出操作(完整步骤)
conn testuser/123@orcl
导出用户testuser的所有数据
$exp testuser/123@orcl file = d:\数据库名.dmp owner=testuser
-- file = d:\数据库名.dmp 文件导出放入该目录的该名字数据文件中
-- owner=testuser 只导出用户testuser的所有数据
导出指定的表数据
$exp testuser/123@orcl file = d:\数据库名.dmp tables=(tableName1,tableName2..)
-- file = d:\数据库名.dmp 文件导出放入该目录的该名字数据文件中
-- tables=(tableName1,tableName2..) 要导出的表名以“,”分开
如果有dba权限的用户,可以用参数full=y导出所有该用户数据和关联系统数据
$exp testuser/123@orcl file = d:\数据库名.dmp full=y
-- file = d:\数据库名.dmp 文件导出放入该目录的该名字数据文件中
-- full=y 导出所有该用户数据和关联系统数据
用户权限的操作
撤销表空间无限限额的权限(针对所有用户)
revoke unlimited tablespace from 用户名;
系统权限回收:系统权限只能由DBA用户回收
Revoke connect, resource from user50;
更改用户user1 使用默认空间space1 的权限为无限配额
alter user user1 default tablespace space1 quota unlimited on space1;
给用户设置dba权限
GRANT DBA TO testuser WITH ADMIN OPTION;
完整的删除用户及用户数据
DROP USER testuser CASCADE;
1. oracle用户查看自己的权限和角色
select * from user_tab_privs;
select * from user_role_privs;
2. sys用户查看任一用户的权限和角色
select * from dba_tab_privs;
select * from dba_role_privs;
查看系统权限:
SQL> SELECT * FROM SYSTEM_PRIVILEGE_MAP ORDER BY NAME;
查看指定的用户:
SQL> select * from dba_sys_privs where grantee='RASWSJ';
没有空间设置,直接建立用户
CREATE USER testuser IDENTIFIED BY 123; --自动匹配分配在默认表空间
--解锁
SQL> alter user 用户名 account unlock;
表和索引的操作
表改名
ALTER TABLE tableName1 RENAME TO tableName2
去掉表主键
alter table 表名 drop constraint 主键名
建立索引
CREATE INDEX 索引名 ON 表名(字段1, 字段2)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "空间名";
1、查找表的所有索引(包括索引名,类型,构成列):
select t.*,i.index_type from user_ind_columns t,user_indexes i
where t.index_name = i.index_name and t.table_name = i.table_name
and t.table_name = '要查询的表名' ;
2、查找表的主键(包括名称,构成列):
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P'
and au.table_name = 要查询的表
3、查找表的唯一性约束(包括名称,构成列):
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U'
and au.table_name = '要查询的表'; --要查询的表
4、查找表的外键(包括名称,引用表的表名和对应的键名,下面是分成多步查询):
select * from user_constraints c where c.constraint_type = 'R'
and c.table_name = '要查询的表'; 要查询的表
查询外键约束的列名:
select * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名:
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
5、查询表的所有列及其属性
select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表
查看建表语句:
select dbms_metadata.get_ddl('TABLE','表名') from dual
程序员对SQLPUS基础操作
查看当前数据库
select name from V$DATABASE;
查看当前用户
show user
查看表用户
select owner,table_name from all_tables where table_name='表名'
查看用户表
slect table_name from all_table where owner='用户名'
建立 sequence
create sequence SEQ名 by 1 start with 1 maxvalue 99999999999999999;
或
create sequence SEQ名
minvalue 1
maxvalue 99999999999999999
start with 1
increment by 1
cache 10;
DLL操作:
1.得到一个表的ddl语句:
SET SERVEROUTPUT ON
SET LINESIZE 32767 ---(1-32767)
SET FEEDBACK OFF
set long 2000000000 ------显示不完整(1-2000000000)
SET PAGESIZE 50000 ----分页(0-50000)
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); ---去除storage等多余参数
SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_FRAME') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE','TCC_NE_SNAP') FROM DUAL;
2.得到一个用户下的所有表,索引,存储过程的ddl
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999 ------显示不完整
SET PAGESIZE 1000 ----分页
---去除storage等多余参数
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');
3.得到所有表空间的ddl语句
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999------显示不完整
SET PAGESIZE 1000----分页
---去除storage等多余参数
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
4.得到所有创建用户的ddl
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999------显示不完整
SET PAGESIZE 1000----分页
---去除storage等多余参数
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;