Oracle冷备份和恢复-造数据

1、创建空间
create tablespace pionner_data
datafile 'C:\orcl1122\disk8\PIONEER_DATA.DBF'
size 100M
extent managerment local
uniform size 1M;

create tablespace pionner_indx
datafile 'C:\orcl1122\disk8\PIONEER_INDX.DBF'
size 100M
extent managerment local
uniform size 1M;



2、创建用户
SQL> create user nezha
  2  identified by nezha
  3  default tablespace pioneer_data
  4  temporary tablespace temp
  5  quota 100M on pioneer_data
  6  quota 100M on pioneer_indx;
User created


3、授权
SQL> grant connect ,resource,select any table to nezha;
Grant succeeded
4、解锁sh用户
SQL> alter user sh identified by sh account unlock;
User altered
5、查询sh用户下的客户表和销售表数据

SQL> select count(*) from customers;
  COUNT(*)
----------
     55500

SQL> select count(*) from sales;
  COUNT(*)
----------
    918843

6、在nezha用户下创建表 利用sh表的数据sh.sales;sh.customers;

SQL> create table sales as select * from sh.sales;
Table created

SQL> create table customers as select * from sh.customers;
Table created

7、为哪吒用户 销售表创建3个索引：

SQL> create index sales_prod_id on sales(prod_id)  tablespace pioneer_indx;
Index created

SQL> create index sales_cust_id on sales(cust_id) tablespace pioneer_indx;
Index created

SQL> create index sales_channel_id on sales(channel_id) tablespace pioneer_indx;
Index created
8、为nezha用户的客户表创建2个索引：
SQL> create index customers_gender_idx on customers(cust_gender) tablespace pioneer_indx;
Index created

SQL> create index customers_city_idx on customers(cust_city) tablespace pioneer_indx;
Index created

9、查看以上创建的信息
表信息
SQL> select table_name ,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
CUSTOMERS                      PIONEER_DATA
SALES                          PIONEER_DATA

索引信息
SQL> select index_name,table_name,tablespace_name,status
  2  from user_indexes;
INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
CUSTOMERS_GENDER_IDX           CUSTOMERS                      PIONEER_INDX                   VALID
CUSTOMERS_CITY_IDX             CUSTOMERS                      PIONEER_INDX                   VALID
SALES_PROD_ID                  SALES                          PIONEER_INDX                   VALID
SALES_CUST_ID                  SALES                          PIONEER_INDX                   VALID
SALES_CHANNEL_ID               SALES                          PIONEER_INDX                   VALID