oracle 基本操作

1，创建表空间

create tablespace data_test datafile '/data1/oracle/oradata/orcl/data_1.dbf' size 2000M;

2.建用户

create user study identified by study default tablespace data_test;

3.给用户授权

grant dba to study;

 

4.用新用户登陆测试

sqlplus study/study@orcl

5.建表

create table student

(

       id number(11) not null primary key,

       stu_name varchar(16) not null,

       gender number(11) default null,

       age number(11) default null,

       address varchar(128) default null

);

插入一条数据

insert into student values('3','王三军','1','17','北京市和平里七区30号楼7门102');

commit;

插入完需要commit 一下,否则其他程序查不到

 

6，确认下是否创建成功

select table_name from user_tables;

7.查看数据

select * from  student；;(乱码)

 

8.由于在dbca 建库时未设置系统字符集，所以采用了默认的WE8MSWIN1252，现在更改

 

查看相关字符集

SQL> select userenv('language') from dual;

SQL> select * from nls_database_parameters where parameter in ('NLS_CHARCTERSET','NLS_NCHAR_CHARACTERSET');

SQL> select* from v$nls_parameters where parameter='NLS_CHARACTERSET';

 

进行修改

SQL> shutdown immediate

SQL> select* from v$nls_parameters where parameter='NLS_CHARACTERSET';

SQL> alter session set sql_trace=true;

SQL> alter system enable restricted session;

SQL> show parameter job_queue_processes;

SQL> alter system set job_queue_processes=0;

SQL> alter system set aq_tm_processes=0;

SQL> alter database open;

SQL> alter database character set INTERNAL_USE AL32UTF8;

SQL> update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';

维护完以后需要

SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;

 

9.查看表属于哪个表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES;

 

10.spark 读写oracle

val spark = SparkSession

      .builder()

      .master("local")

      .appName("oracle")

      .getOrCreate()

    val df = spark.read.format("jdbc")

      .option("driver", "oracle.jdbc.OracleDriver")

      .option("url", "jdbc:oracle:thin:@192.168.1.151:1521:orcl") //thin是driver，orcl是sid(数据库名字)

      .option("dbtable", "study.student2") //study 是用户名

      .option("user", "study")

      .option("password", "study")

      .load()

    df.show(20)

    import spark.implicits._

 

    val df2 = (1 to 100).map(x => (x, s"${x}jj")).toDF("id", "name")

    df2.write.format("jdbc")

      .option("driver", "oracle.jdbc.OracleDriver")

      .option("url", "jdbc:oracle:thin:@192.168.1.151:1521:orcl") //thin是driver，orcl是sid(数据库名字)

      .option("dbtable", "student2")

      .option("user", "study")

      .option("password", "study")

      .save()

    df.printSchema()

    spark.stop()