程序员必须掌握的Oracle的基本sql操作

 

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;