Oracle First Wave

Tag:field   lex   cron    Execution    ram     Authorization grant    common    type   otf   

Create a table table space

CREATE TABLESPACE waterboss DATAFILE ‘E:\oracle\beckDb\waterboss.dbf‘ -- 物理文件存放在磁盘的具体路径 SIZE 10m -- 初始物理文件的大小 AUTOEXTEND ON -- 文件存储不足时，自动扩容 NEXT 5m -- 每次扩容的大小

Delete Table space

DROP TABLESPACE waterboss; -- 删除表空间（不会删除表空间的物理文件）-- 删除表空间并且删除表空间的物理文件 DROP TABLESPACE waterboss INCLUDING CONTENTS AND DATAFILES;

Create a user and set the table space to which it belongs

-- 创建用户并且设置用户所属的表空间 CREATE USER wateruser IDENTIFIED BY root123 DEFAULT TABLESPACE waterboss;

Authorization to the user

-- 给用户授权 GRANT DBA TO wateruser;

Create a table

-- 创建表 CREATE TABLE t_user( ID NUMBER PRIMARY KEY, NAME VARCHAR2(50), SEX CHAR(1), ADDDATE DATE );

Add columns

-- 增加列 ALTER TABLE t_user add ( PHONE CHAR(11) ); ALTER TABLE t_user add ( MOBILE CHAR(20), JOB VARCHAR(60) );

Modify a table field

-- 修改字段 ALTER TABLE t_user MODIFY ( MOBILE NUMBER );

Modify the name of a field

-- 修改字段名 ALTER TABLE t_user RENAME COLUMN PHONE TO TELEPHONE;

Delete a field

-- 删除一个字段 ALTER TABLE t_user DROP COLUMN JOB; -- 删除多个字段 ALTER TABLE t_user DROP (MOBILE,TELEPHONE);

New data

-- 插入数据 INSERT INTO t_user (ID,NAME,SEX,ADDDATE) VALUES (1,‘老司机‘,‘1‘,sysdate); INSERT INTO t_user (ID,NAME,SEX,ADDDATE) VALUES (2,‘大美女‘,‘0‘,sysdate);

modifying data

-- 修改数据 UPDATE t_user SET ADDDATE = ADDDATE - 3 WHERE ID=1;

Delete data

-- 删除数据 DELETE FROM t_user WHERE ID=2;

JDBC Connect Oracle

Jdbcutils.java ' package com.xiaoshitou.utils;

Import java.sql.Connection; Import Java.sql.DriverManager; Import Java.sql.ResultSet; Import java.sql.SQLException; Import java.sql.Statement;

/** * Connect Oracle Database * * @author Beck * */public class Jdbcutils {static {try {Class.forName ("Oracle.jdbc.driver.OracleDr Iver "); } catch (ClassNotFoundException e) {e.printstacktrace ();}}

public static Connection getConnection() throws SQLException {    String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";    return DriverManager.getConnection(url, "wateruser", "root123");}public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {    try {        if (rs != null) {            rs.close();        }    } catch (SQLException e) {        e.printStackTrace();    }    try {        if (stmt != null) {            stmt.close();        }    } catch (SQLException e) {        e.printStackTrace();    }    try {        if (conn != null) {            conn.close();        }    } catch (SQLException e) {        e.printStackTrace();    }}

} package 测试程序:TestOracle.java com.xiaoshitou.test;

Import java.sql.Connection; Import java.sql.PreparedStatement; Import java.sql.SQLException;

Import Org.junit.Test;

Import Com.xiaoshitou.utils.JDBCUtils;

public class Testoracle {

@Testpublic void test01() throws SQLException{    Connection conn = null;    PreparedStatement stmt = null;    try {        conn = JDBCUtils.getConnection();        String sql = "INSERT INTO t_user (ID,NAME,SEX,ADDDATE) VALUES (?,?,?,?)";         stmt = conn.prepareStatement(sql );         stmt.setLong(1, 4);         stmt.setString(2, "小妹咩");         stmt.setLong(3, 0);         stmt.setDate(4, new java.sql.Date(System.currentTimeMillis()));        int update = stmt.executeUpdate();         System.out.println(update);    } catch (Exception e) {        e.printStackTrace();    }finally {        JDBCUtils.closeAll(null, stmt, conn);    }}

} ```

Data export and import (Backup and restore) full-Library export:

exp system/123456 full=y exp system/123456 file=allData.dmp full=y

Full Library Import

imp system/123456 full=y imp system/123456 full=y file=allData.dmp

Export by user

exp system/123456 owner=wateruser file=wateruser.dmp

Import by user

imp system/123456 file=wateruser.dmp fromuser=wateruser

Export by Table

exp wateruser/root123 file=a.dmp tables=t_user

Import by Table

imp wateruser/root123 file=a.dmp tables=t_user

Common permissions

System permissions: Allows users to perform specific database actions, such as creating tables, creating indexes, connecting to instances (for users) object permissions: Allowing users to manipulate specific objects, such as reading views, updating certain columns, executing stored procedures, etc. (for tables or views)

1. System permissions more than 100 valid permissions (SELECT * from SystemPRIVILEGEmap) The database administrator has advanced permissions to complete administrative tasks, such as: – Create new user – Delete user – Delete table – Backup table

System Permissions Classification: DBA: Full privilege, highest system privilege, only DBA can create database structure. RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure. Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.

For normal users: Grant Connect, resource permissions. For DBA administration users: Grant Connect,resource, dba authority.

Frequently used system permissions: Create session creates a SEQUENCE create a sequence created synonym create a table created with the same name as object create tables in user mode creates the table in any mode drop table in user mode delete tables drop any table delete tables in any mode create PROCEDURE creates a stored procedure execute any PROCEDURE the stored procedure to execute any pattern create user D ROP User Delete users create view creating views

2. Object permissions different objects have different object permission objects owner owner of all permission objects can assign permissions to Oracle altogether there is a kind of object Permission object permission table view sequence process modification (alter) √√ Delete (delete) √√ execute (Execute) √ Indexing (index) √ Insert (insert) √√ Association (references) √√ Select (select) √√√ Update (update) √√

Remove the difference between delete and truncat? Interview

Compare Truncat with Delete to implement data deletion? 1) Delete deleted data can be rollback;truncate is not rolled back 2) Delete Delete can be fragmented, and does not free space, truncate will not produce debris, will free up space 3) delete is deleted; truncate is the first to destroy the table, Re-construct the table

Oracle First wave