1.
SHOW USER(or SELECT USER FROM DUAL)
/
2.
SELECT USERNAME, USER_ID, PASSWORD FROM DBA_USERS ORDER BY USERNAME
/
3.
SELECT DEFAULT_TABLESPACE FROM USER_USERS
/
4.
SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES <----- only find the current user tables.
/
5.
ALTER TABLE FOO MOVE TABLESPACE TS_MY_TABLESPACE
/
6.
SELECT * FROM DBA_TABLESPACES
/
7.
CREATE TABLESPACE MY_TABLESPACE
LOGGING
DATAFILE 'C:\ORCL\TS\MY_DB_01.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 128M,
'C:\ORCL\TS\MY_DB_02.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 128M
EXTENT MANAGEMENT LOCAL
/
note: If contents exceed the tablespace max size, then [ORA-01654: unable to extend index ASRAN.SYS_C0024968 by 128 in tablespace] thrown.
-----------------------------
SELECT * FROM V$DATAFILE;
-----------------------------
Moving and Renaming Tablespaces:
1.Take the tablespace offline using an ALTER TABLESPACE statement with the OFFLINE clause:
ALTER TABLESPACE tbs_02 OFFLINE NORMAL;
2.Copy the file from 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\TS_DENGAS_01.DBF' to 'C:\oraclexe\oradata\TEST\TS_DENGAS_01.DBF' using your operating system commands.
3.Rename the datafile using an ALTER TABLESPACE statement with the RENAME DATAFILE clause:
ALTER TABLESPACE tbs_02
RENAME DATAFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\TS_DENGAS_01.DBF'
TO 'C:\oraclexe\oradata\TEST\TS_DENGAS_01.DBF';
4.Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE clause:
ALTER TABLESPACE tbs_02 ONLINE;
-----------------------------
Add/Drop datafiles:
alter tablespace ts_dengas
add datafile 'c:\oraclexe\oradata\test\ts_dengas_02.dbf' size 100k autoextend off
/
alter tablespace ts_dengas
drop datafile 'c:\oraclexe\oradata\test\ts_dengas_02.dbf'
/
-----------------------------
Drop tablespace:
drop tablespace <tablespace_name> [including contents] [and datafiles] [cascade constraints]
/
-----------------------------
Resize datafile:
alter database datafile '<datafile-path>'
resize 2M
/
note: If the target size is too small, then [ORA-03297: file contains used data beyond requested RESIZE value] thrown.
8.
VAR[IABLE] VARIABLE_NAME DATA_TYPE
PRINT VARIABLE_NAME
9.
SET SERVEROUTPUT ON ESCAPE OFF
DBMS_OUTPUT.PUT_LINE('MSG...');
10.
The values of all parts of the primary key must never be null.
1. First Normal Form means that the database doesn't contain any repeating attributes.
2. Violations of Second Normal Form occur when the table contains attributes that depend on a portion of the primary key.
3. Second Normal Form violations can exist only when you have a multi-column primary key.
4. Third Normal Form violations occur when a transitive dependency exists.
5. All attributes in entities (columns in tables) must be dependent upon the primary key or one of the candidate keys and not on other attributes.
11.
SELECT OBJECT_NAME, OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER = 'SYSTEM'
/
12.
SELECT TABLE_NAME.COLUMN_NAME FROM TABLE_NAME -- NO ALIAS
/
13.
SELECT LEVEL, empno, manager_id, ename
FROM employee
START WITH empno = 1
CONNECT BY PRIOR empno = manager_id
ORDER BY LEVEL
/
14. is null VS. =
SELECT m.* FROM SA_MENU_ITEM m
WHERE m.DISPLAY_IND= :displayInd
CONNECT BY PRIOR m.ID = m.PARENT_MENU_ID
START WITH NVL(m.PARENT_MENU_ID, NULL) || ' ' = NVL(:rootMenuId, NULL) || ' ';
15. set nls lange & characterset:
set NLS_LANG=AMERICAN_AMERICA.al32UTF8
set nls_nchar_characterset=american_america.al16utf16
16. full setting of creating user:
--------------------------------------------------------------------------------
-- Create the user
create user asran
identified by asran
default tablespace ASRAN_TS
temporary tablespace TEMP
profile DEFAULT
password expire
account lock
quota 1m on asran_ts;
-- Grant/Revoke object privileges
grant select, insert, update, delete on ALL_ALL_TABLES to asran with grant option;
-- Grant/Revoke role privileges
grant connect to asran with admin option;
grant resource to asran with admin option;
-- Grant/Revoke system privileges
grant update any table to asran with admin option;
17. synonym:
--------------------------------------------------------------------------------
create tablespace asran_ts
logging
datafile 'c:\usr\orcl\ts\asran_ts\asran_ts_01.dbf'
size 2M
autoextend off;
create user asran identified by asran
default tablespace asran_ts
temporary tablespace temp
quota 32k on asran_ts;
grant connect to asran;
create user ada identified by ada
default tablespace asran_ts
temporary tablespace temp
quota 16k on asran_ts;
alter user ada quota 1M on asran_ts;
create table ada.bl_profile(
id int not null,
name varchar2(200)
);
alter table ada.bl_profile add constraint ada_bl_profile_pk primary key(id);
create table ada.private_bl_profile(
id int not null,
name varchar(200)
) tablespace asran_ts;
alter table ada.private_bl_profile add constraint ada_private_bl_profile_pk primary key(id);
select * from ada.bl_profile;
grant select on ada.bl_profile to asran;
revoke select on ada.bl_profile from asran;
create synonym ada.bl_profile_alias for ada.bl_profile; -- create synonym in current schema
grant select on ada.bl_profile_alias to asran; -- grant priviledges of synonym to other users
sqlplus asran/asran
select * from ada.bl_profile; -- can't find table or view
select * from ada.bl_profile_alias; -- works well
note: all synonym and real object(table,view,...) share the same object, as a result, any grant/revoke on one of them should effect each other.
18. display oracle version:
SELECT * FROM V$VERSION;
19. customer defined object type(HashMap):
create or replace type <my_type_obj> as object(<property_name> <property_type>, ...)
/
usage:
declare: <variable_name> <my_type_obj>;
assign value: <variable_name> := <schema>.<my_type_obj>(value1, value2, ...);
get value: <variable_name>.<property_name>
note:
an object type is just as a key-value pair map.
20. customer defined table type(List<HashMap>):
create or replace type <my_type_tbl> as table of <my_type_object>
/
usage:
declare and initialize: <my_tbl> <schema>.<my_type_tbl> := <schema>.<my_type_tbl>(); -- size 0
extend the size of list: <my_tbl>.extend;
add obj: <my_tbl>(index start at 1) := <my_type_obj>(value1, value2, ...);
e.g.: <my_tbl>(<my_tbl>.count) := <my_type_obj>(value1, value2, ...)
21. cursor:
declare
v_id asran.t_student.id%type;
v_name asran.t_student.name%type;
cursor v_cursor is select id, name from asran.t_student;
begin
open v_cursor;
loop
fetch v_cursor into v_id, v_name;
exit when v_cursor%notfound;
dbms_output.put_line('id=' || v_id || ', name=' || v_name);
end loop;
close v_cursor;
end;
/
or use for...loop:
declare
cursor v_cursor is select id, name from asran.t_student;
begin
for c in v_cursor loop
dbms_output.put_line('id=' || c.id || ', name=' || c.name);
end loop;
end;
/
22. package:
--------------------define package
create or replace package asran.pkg_all
as
function add(p_a in int, p_b in int) return int;
end;
/
--------------------define package body
create or replace package body asran.pkg_all
as
function add(p_a in int, p_b in int)
return int
is
v_sum int := 0;
begin
v_sum := p_a + p_b;
return v_sum;
end;
end;
/
--------------------using package
declare
v_sum int;
begin
v_sum := asran.pkg_all.add(2,4);
dbms_output.put_line('sum:' || v_sum);
end;
/
23. trigger:
create or replace trigger tri_as_student_insert
after insert or delete on asran.as_student
for each row
declclsare
updating_key_fields exception;
v_count int := 0;
begin
if inserting then
select count(1) into v_count from asran.as_student;
dbms_output.put_line('The total records of asran.as_student is ' || v_count);
end if;
if updating then
raise updating_key_fields;
end if;
if deleting then
....
end if;
end;
/
alter trigger asran.tri_as_student_insert disable
/
alter trigger asran.tri_as_student_insert enable
/
24. arguments / return type declaration:
Never specify the size/(precision, scale) for char/varchar/number types in Oracle10g.
25. sequence:
create sequence [schema.]sequence_name increment by 1 start with 0 nomaxvalue nocycle nocache
/
note:
1. can't use [create or replace ...] for create sequence, saying that we can't replace an existing sequence.
2. can't alter sequence start with [ORA-02283: cannot alter starting sequence number].
26. pass parameter to plsql:
begin
dbms_output.put_line('&name is a good staff');
end;
/
note: [&****] is named parameter.
27. use function in index creation:
create index asran.t_student_idx on asran.t_student(upper(name))
/
28. retrieve the next day:
select next_day(sysdate, 'MON|Mondy,...') from dual
/
select add_months(sysdate, 2) from dual
/
29. dump sqlplus sreen output to a specified file:
sqlplus>define fil = 'c:\oral_spool.log'
sqlplus>spool &fil <--- open file output stream
sqlplus>prompt *** Spool to 'c:\oral_spool.log'
sqlplus>....
sqlplus>spool off <--- flush output buffer to file system
30. set SQLPATH environment variable:
set SQLPATH=c:\usr\orcl\sqlpath
sqlplus will find and save buffer file to this folder.(search in current folder, and then SQLPATH folder)
31. start sqlplus with silent mode and run a sql script file:
sqlplus -silent asran/asran @init_script.sql
32. start sqlplus without login:
sqlplus /nolog
33. clear buffer/screen
clear buffer
clear screen
33. read a script file to buffer:
get test.sql list|nolist
34. export/imprt db in Oracle10g:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp cs2_afw_owner/cs2_afw_owner@zhcs2qa2 file=cs2_afw_owner.dmp
-----------------------
sqlplus sys/sys as sysdba
create tablespace cs2_ts
logging
datafile 'c:\usr\orcl\ts\cs2_ts\cs2_data_01.dbf' size 512m autoextend on next 128m maxsize 1024m
extent
management local
create user cs2_afw_owner identified by cs2_afw_owner default tablespace cs2_ts;
imp file=cs2_afw_owner.dump fromuser=cs2_afw_owner touser=cs2_afw_owner
>username: sys/sys as sysdba
35. find the company which have the same personnel amount:
select c.* from company c where c.personnel_amt in (
select personnel_amt from company group by personnel_amt having count(1) > 1
);
36. utilites functions:
PL/SQL – String Manipulation:
rtrim, ltrim, trim, concat, rpad, lpad, upper, lower, length, replace(), initcap(), instr(), substr()
Generating random numbers and strings in Oracle:
Generating a random number (positive or negative)
select dbms_random.random from dual;
Generating a random number between 0 and 1
select dbms_random.value from dual;
Generating a random number from a range, between 1 to 1000
select dbms_random.value(1,1000) num from dual;
Generating a 12 digit random number
select dbms_random.value(100000000000, 999999999999) num from dual
Generating an upper case string of 20 characters
select dbms_random.string('U', 20) str from dual;
Generating a lower case string of 20 characters
select dbms_random.string('L', 20) str from dual;
Generating an alphanumeric string of 20 characters
select dbms_random.string('A', 20) str from dual;
Generating an upper case alphanumeric string of 20 characters
select dbms_random.string('X', 20) str from dual;
Generating a string of printable 20 characters
select dbms_random.string('P', 20) str from dual;
Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions
declare
l_num number;
begin
l_num := dbms_random.random;
dbms_output.put_line(l_num);
dbms_random.seed('amar testing 67890');
l_num := dbms_random.random;
dbms_output.put_line(l_num);
end;
/
37. export & import:
exp userid=scott/tigger full=y file=myfile.dmp
exp userid=scott/tigger owner=(scott,ali) file=exp_own.dmp
exp userid=scott/tigger tables=(scott.emp,scott.sales) file=exp_tab.dmp
imp scott/tigger ignore-y tables(emp,dept) full=n
or imp scott/tigger ignore-y tables(T1:P1,T2:P2) full=n, if T1 is partioned table
38. SQLPLUS:
COLUMN <col_name> FORMAT A<width>
相关推荐
Oracle 9i Client (Oracle 9i 客户端) 简化版 (不安装Oracle客户端,也可以使用PLSQL Developer 不用安装Oracle客户端也可以使用PLSQL Developer 绿色! 安全! 轻便! 可靠! 1、本软件可作为简单的Oracle9i客户端...
Dbmover for Mysql to Oracle 是高效的将Mysql导入到Oracle数据库的工具。 使用DBMover可以灵活定义Mysql和Oracle之间表和字段的对照关系,也可以在DBMover创建一个查询,把查询结果当作源表转入到Oracle中。 ...
《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf《Oracle...
python-oracledb的源码和使用示例代码, python-oracledb 1.0,适用于Python versions 3.6 through 3.10. Oracle Database; This directory contains samples for python-oracledb. 1. The schemas and SQL ...
Oracle Instant Client 11.2.0.1.0是轻量级Oracle客户端,用于连接访问Oracle 9i、10g、11g 11.2.0.1.0版本的Oracle数据库。 Oracle Instant Client11.2.0.1.0 安装程序包含OCI/ OCCI、JDBC-OCI SDK(软件开发工具...
5. MySQL与ORACLE区别 19 6. 可视化工具 38 三、 ORACLE介绍 38 1. ORACLE是什么? 38 2. ORACLE核心特点是什么? 38 3. ORACLE数据库类型有哪些? 39 4. ORACLE整体架构及工作原理? 39 5. 可视化工具 40
oracle 9i所有版本最新下载链接 直接迅雷下载 Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XP ...
ORACLE_HOME=$ORACLE_BASE/oracle ORACLE_SID=hsj PATH=$ORACLE_HOEM/bin:$PATH; LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH 3...
使用了,ODP.NET 方式链接数据库,只要把Oracle.ManagedDataAccess.dll引入取代以前的Oracle.DataAccess.dll即可。 这种方式也是oracle公司提供的,稳定性要比之前那种更好,而且也是免安装客户端的,目前还没测试...
oracle11g官方中文帮助 Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g 中的分区.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g...
Oracle 11g 官方中文文档 包括一下部分文档: Oracle 11g:ORACLE ACTIVE DATA GUARD.pdf Oracle Database 11g 高可用性.pdf Oracle 数据库 11g :真正应用测试与可管理性概述.pdf Oracle 数据库 11g 中的分区....
oracle.ManagedDataAccess.dll(oracle.ManagedDataAccess.Client)全托管驱动。 此驱动对32位和64位oracle数据库具有很好的连接兼容性。 可采用无客户端远程连接oracle,或在本机使用连接。 使用此驱动可以完全替换...
Oracle 10g 第 2 版 ODAC 和 Oracle Developer Tools for Visual Studio .NET 下载文件 ODTwithODAC1020221.exe 235 MB(247,296,458 字节) ODAC 安装说明 Oracle Developer Tools For Visual ...
Oracle 19C+13.4EMCC部署和配置 1 一、安装oracle19C数据库 1 1、 安装oracle19C软件 1 2、 创建PDB 1 3、 设置随机启动 1 4、 修改logfile大小 2 二、利用EM模板创建pdb 3 1. 上传dbca模板并设置解压 3 2. DBCA建库...
项目中有个功能需要从一台Oracle数据库获取数据,本以为是很简单的事情,直接将原来的SqlClient换成OracleClient调用,结果远没自己想的简单。有很多需要安装Oracle客户端,网上这方面搜索后,太多的文章,还要不停...
Convert Mysql to Oracle 最新版本:4.0 Convert Mysql to Oracle是一个免费的数据库转换工具,实现快速安全地将Mysql数据库导入为ORACLE数据库。 Convert Mysql to Oracle 功能特点 可以转换所有的Mysql字段类型 ...
解决的办法其实很简单,在注册表HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE这个位置新建一个字符串值ORACLE_HOME,把oracle的HOME值写进去就可以了!在10g以上的版本中一般ORACLE_HOME都存在HKEY_LOCAL_MACHINE\SOFTWARE\...
自己制作的这个小巧的客户端(文件全部源自Oracle官方网站),原理其实很简单,就是向注册表写几个键值,非常绿色和环保。 下载后,只需要将Oracle压缩文件解压,建议放在D:\Oracle目录下,运行OracleSetup32.exe...
Oracle 11g 安装包 Windows Linux 系统 32位 64位安装包。资源由本人辛苦整理而来,因在Oracle官网很难找到下载链接,所以特地拿出来分享,如需其他版本请私信,必回复。 资源包含以下版本: Oracle Database 11.2....
oracle 11.2.0.4.0-1.x86_64 rpm客户端安装包 文件列表: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-basiclite-11.2.0.4.0-1.x86_64.rpm oracle-instantclient11.2-devel-...