Oracle Statements
--create tablespace
CREATE TABLESPACE "TABLESPACE_NAME" LOGGING
DATAFILE 'F:\oracle\product\10.2.0\oradata\orcl\TABLESPACE_DATA_NAME.dbf' SIZE 200 M
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
-- Create an user
create user USER_NAME
identified by "password"
default tablespace SPACE_NAME
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to USER_NAME;
grant dba to USER_NAME;
-- Grant/Revoke system privileges
grant unlimited tablespace to USER_NAME;
数据库DB Link:
select * from dba_db_links;
create public database link link名称 connect to 远端用户名 identified by "远端"
using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 远端IP)(PORT = 1521)) )
(CONNECT_DATA = (SERVICE_NAME = 远端实例名)))';
select 'create table '||table_name ||' as select * from '||table_name||'@link名称;' from user_tables;
select 'drop table '|| table_name ||';' as aa from user_tables;
drop public database link dblink_name;
导出数据文件
exp 用户名/密码@实例名 file=将要保存的数据文件名
导入数据库
imp 用户名/密码@实例名 file=要导入的数据文件名 full=y
导入/导出指定表
imp user/password@实例名 file=D:\sampleDB.dmp log=D:\imp.log fromuser=userName tables=(table1,table2)
exp user/password@实例名 file=d:/sampleDB.dmp tables=(table1,table2)
用户管理:进入sqlplus / as sysdba
建用户及授权
create user 用户名 identified by 密码 default tablespace users temporary tablespace temp;
grant connect,resource,dba to 用户名;
删除用户
drop user 用户名 cascade;
配置sqlplus:
export ORACLE_SID=实例名
export ORACLE_BASE=/oracle/
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/Db_1
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
sqlplus /nolog
conn / as sysdba
select sysdate from dual;
cd $ORACLE_HOME/network/admin
cp tnsnames.ora tnsnames20160101.ora
vi tnsnames.ora
添加:
TEST = 登陆的SID
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.1)(PORT = 1521)) IP地址和端口号
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl) 要链接数据库名
)
)
:wq
设置一下配置文件权限,以便其它用户使用
chmod 755 tnsnames.ora
sqlplus 用户名/密码@登陆的SID
相关推荐
Oracle的SQL语句执行效率问题查找与解决方法。
Which two statements are true about space - saving features in an Oracle Database? A.An index created with the UNUSABLE attribute has no segment. B.Private Temporary Tables(PTTs)store metadata in ...
6 Oracle9i Extensions to DML and DDL Statements Objectives 6-2 Review of the INSERT Statement 6-3 Review of the UPDATE Statement 6-4 Overview of Multitable INSERT Statements 6-5 Types of Multitable ...
Written in an easygoing and example-based style, Beginning Oracle SQL is the book that will get you started down the path to successfully writing SQL statements and getting results from Oracle ...
Explore datatypes, conditional and sequential control statements, loops, exception handling, security features, globalization and localization issues, and the PL/SQL architecture Understand and use ...
Filled with detailed examples and expert strategies from an Oracle ACE, Oracle Database 12c PL/SQL Programming explains how to retrieve and process data, write PL/SQL statements, execute effective ...
Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the ...
Explore datatypes, conditional and sequential control statements, loops, exception handling, security features, globalization and localization issues, and the PL/SQL architecture Understand and use ...
The Oracle SQL*Plus Pocket Reference is a quick-reference guide to SQL*Plus and to commonly used SQL query and data manipulation statements. The purpose of this book is to help you find the syntax of ...
capabilities built into the Oracle database kernel. With this capability, ASM simplifies storage management tasks, such as creating/laying out databases and diskspace management. Since ASM allows disk...
Readers should already know the basic four SQL statements, and be ready to learn deeply about Oracle’s specific implementation of the language, including Oracle-specific features and syntax....
1 Introduction and Upgrading 2 OCI Programming Basics 3 Datatypes 4 Using SQL Statements in OCI 5 Binding and Defining
The author freely shares his experience while explaining the underlying foundations of how SQL statements are executed by the Oracle database engine. You'll be able to draw a solid foundation of ...
Oracle Database. Chapter 3, “Analysis of Reproducible Problems,” describes how to identify performance problems with the help of SQL trace and PL/SQL profilers. Chapter 4, “Real-time Analysis of ...
Explore datatypes, conditional and sequential control statements, loops, exception handling, security features, globalization and localization issues, and the PL/SQL architecture Understand and use ...
The Structure of This Book ...and executing SQL statements and how to instrument application code and database calls. It also introduces some important terms that are frequently used in the book.
This book is a quick-reference guide for tuning ... When a new index is added, we have to be certain that it will not be used inappropriately by existing SQL statements. This book addresses these issues.
Objectives After completing this lesson, you ...• Write SQL statements to take advantage of shared SQL areas • Understand how to use the CURSOR_SHARING parameter • Use automatic PGA memory management
How To Generate GUID In Databases - A brief explanation wtih sample statements - Covers MSSQL, DB2 and ORACLE
PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL...