Go语言视频零基础入门到精通

luowei010101

SQL> conn sys/change_on_install;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

SQL> show user;
User is "SYS"

SQL> conn scott/luowei
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott
SQL> conn system/luowei;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL> conn system/luowei;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL> conn system/luowei;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL>
SQL> conn system/manager as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS
SQL> show user;
User is "SYS"

SQL> conn system/luowei;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL>
SQL> show user;
User is "system"

SQL> conn system/luowei as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

SQL> conn sys/luowei as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

SQL> conn sys/change_on_install as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

SQL>
SQL> conn sys/luowei as sysoper;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as PUBLIC

SQL> show user;
User is &quotUBLIC"

SQL> conn system/luowei as sysdba;
SQL> conn system/luowei as sysdba;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.

SQL> conn system/luowei;
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as system

SQL> --显示初始化参数
SQL> show parameter;

------------------------导出表----------------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp  userid=scott/luowei@orcl tables=(emp) fil
e=D:\oracle\emp.dmp

Export: Release 10.1.0.2.0 - Production on 星期日 3月 20 00:19:04 2011

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         16 rows exported
Export terminated successfully without warnings.

----------------------------导出多张表-----------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp  userid=scott/luowei@orcl tables=(emp,dept
) file=D:\oracle\emp.dmp

Export: Release 10.1.0.2.0 - Production on 星期日 3月 20 00:25:13 2011

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         16 rows exported
. . exporting table                           DEPT          4 rows exported
Export terminated successfully without warnings.

-------------------------导出其它方案的表------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp  userid=system/luowei@orcl tables=(scott.e
mp) file=D:\oracle\emp.dmp

Export: Release 10.1.0.2.0 - Production on 星期日 3月 20 00:31:27 2011

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            EMP         16 rows exported
Export terminated successfully without warnings.

---------------------------导出表的结构----------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp  userid=scott/luowei@orcl tables=(emp,dept
) file=D:\oracle\emp.dmp rows=n

Export: Release 10.1.0.2.0 - Production on 星期日 3月 20 00:34:17 2011

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                            EMP
. . exporting table                           DEPT
Export terminated successfully without warnings.

---------------------------使用直接导出方式------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp  userid=scott/luowei@orcl tables=(emp,dept
) file=D:\oracle\emp.dmp direct=y

Export: Release 10.1.0.2.0 - Production on 星期日 3月 20 00:37:09 2011

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table                            EMP         16 rows exported
. . exporting table                           DEPT          4 rows exported
Export terminated successfully without warnings.

------------------------导出自己的方案-------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp  userid=scott/luowei@orcl owner=scott file
=D:\oracle\emp.dmp

------------------------导出其它方案------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp userid=system/luowei@orcl owner=(system,sc
ott) file=d:\oracle\emp.dmp

-----------------------完全导出一个数据库---------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>exp userid=system/luowei@orcl full=y inctype=c
omplete file=d:\oracle\emp3.dmp

-----------------------导入一张表---------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=scott/luowei@orcl tables=(emp) file=d:\oracle\emp.dmp

-----------------------导入表到其它用户-------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=system/manager@orcl tables=(dept) file=d:\oracle\dept.dmp touser=scott

-----------------------导入表的结构---------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=scott/luowei@orcl tables=(emo) file=d:\oracle\emp.dmp rows=n

-----------------------导入数据------------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=scott/luowei@orcl tables=(emp) file=d:\oracle\emp.dmp ignore=y

-----------------------导入自身的方案------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=scott/luowei file=d:\oracle\emp.dmp

-----------------------导入其它方案--------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=system/luowei file=d:\oracle\emp.dmp fromuser=system touser=scott

-----------------------导入数据库-------------------------------------------------
D:\oracle\product\10.1.0\Db_1\BIN>imp userid=system/luowei full=y file=d:\oracle\db1.dmp

---------------------------------------------------------------

--数据字典，用户、角色、权限

SQL> --显示当前用户所拥有的所有表,只返回用户所对应方案的所有表
SQL> select table_name from user_tables;
SQL>
SQL> --显示当前用户可以访问的所有表，它不仅返回自己方案中的表，还返回其它方案中可以访问的表
SQL> select table_name from all_tables;
SQL>
SQL> conn sys/luowei as sysdba;
SQL>
SQL> ---dba_tables显示数据库中所有方案中的所有表
SQL> select table_name from dba_tables;
SQL>
SQL> --显示数据库的用户信息
SQL> select username from dba_users;
SQL> select username,password from dba_users;
SQL>
SQL> --显示用户所具有的系统权限
SQL> desc dba_sys_privs;
SQL>
SQL> --显示用户所具有的对象权限
SQL> desc dba_tab_privs;
SQL>
SQL> --显示用户所具有的列权限
SQL> desc dba_col_privs;
SQL>
SQL> --显示用户所具有的角色
SQL> desc dba_role_privs;
SQL> select * from dba_role_privs where GRANTEE='SCOTT';

GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
SCOTT                          CONNECT                        NO           YES
SCOTT                          RESOURCE                       NO           YES

SQL> --一个用户可以拥有多个角色，一个角色也可以分配给多个用户，不同的角色包含了多个不同的角色。
SQL> -----------------------------------------------------------------------------------------
SQL>
SQL> --查询oracle中所有的系统权限
SQL> select * from system_privilege_map order by name;
SQL>
SQL> --查询oracle中所有的角色，一般是dba
SQL> select * from dba_roles;
SQL>
SQL> --查询oracle中所有的对象权限，一般是dba
SQL> select distinct privilege from dba_tab_privs;
SQL>
SQL> --查询数据库的表空间
SQL> select tablespace_name from dba_tablespaces;
SQL>
SQL> --查询一个角色包括的权限
SQL>--CONNECT角色包含的系统权限
SQL> select * from dba_sys_privs where grantee='CONNECT';
SQL>--CONNECT角色包含的对象权限
SQL> select * from dba_tab_privs where grantee='CONNECT';
SQL>--CONNECT角色包含的系统权限
SQL> select * from role_sys_privs where role='CONNECT';
SQL>
SQL> --查找有多少种角色
SQL> select * from dba_roles;
SQL>
SQL> --显示当前用户可以访问的所有数据字典视图
SQL> select * from dict where comments like '%grant%';
SQL>
SQL> --显示当前数据库的全称
SQL> select * from global_name;
SQL>
SQL>--------------------------------------------------------------------------
SQL>
SQL> --管理表空间和数据文件
SQL> create tablespace sp001 datafile 'd:\oracle\sp001.dbf' size 10m uniform size 64k;

Tablespace created

SQL> --使用数据表空间
SQL> conn scott/luowei;--这里用scott用户建一张表放在sp001表空间中
SQL> create table mypart(deptno number(2),dname varchar2(14),loc varchar2(13)) tablespace sp001;
SQL> conn sys/luowei as sysdba
SQL>
SQL> --改变表空间的状态
SQL> --使表空间脱机
SQL> alter tablespace sp001 offline;

Tablespace altered

SQL> --使表空间联机
SQL> alter tablespace sp001 online;

Tablespace altered

SQL> --使表空间只读
SQL> alter tablespace sp001 read only;

Tablespace altered

SQL> alter tablespace sp001 read write;--使表空间可读可写

Tablespace altered

SQL> --显示表空间包括的所有表
SQL> select * from all_tables where tablespace_name='SP001';--注意表空间名大写

SQL> --知道表名，查看该表属于哪个表空间
SQL> select tablespace_name,table_name from user_tables where table_name='EMP';--表名要大写

SQL>
SQL> SHOW USER
User is "SYS"

SQL> --扩展表空间
SQL> insert into scott.mypart select * from scott.mypart;

SQL> --1.增加数据文件
SQL>