Shulou(Shulou.com)06/02 Report--

Notes on ORACLE 10G OCA 042

* $1 oracle Database 10g component and architecture *

Component

Oracle Database 10g

Oracle Application Server 10g

Oracle Developer Suite

Oracle Applications 11i

Oracle Collaboration Suite

Oracle Services

Standard grammar should be used. ANSI SQL:1999 syntax using keywords such as JOIN,CROSS JOIN,NATURAL JOIN

A Segment is defined as any entity that consumes physical storage space in the database. Common segment types:

Table, Index, fallback, Partition. Each Segment consists of contiguous storage space blocks (extents) in the database.

Operating system block-- > database block-- > disk area-- > segment

PL/SQL:Oracle Procedural Language for SQL objects: anonymous code blocks, procedures, functions, packages, triggers

In addition to SQL,Web tools, PL/SQL and JAVA, Oracle provides the ability to integrate SQL commands and database connectivity into traditional programming languages. This integration is achieved by using the Oracle precompiler and Oracle Call Interface (OCI).

To be a successful Oracle database administrator (DBA), you need to thoroughly understand the infrastructure and mechanism of Oracle. It is important to understand the memory structure of Oracle and the relationship between background processes and Icano activities before you can learn how to manage these aspects.

Oracle architecture

Describe it in the following three categories

User-related process

Logic logic memory structure collectively known as Oracle Instance

Physical file structure collectively referred to as Database

User process

User Process: either on the user's own PC or on the middle-tier application server. The User Process then initiates a connection to the instance. Oracle calls the process that initiates and manages communication between the User Precess and the instance a Connection. Once the Connection is established, the user creates a Session in the instance. When a session is established, each user starts a Server Process on the host server itself (sharing server with multiple User Process sharing Server Process). The Server Process is then responsible for performing the tasks that actually allow the user to interact with the database.

PGA (Process Global Area): the global area of the program. The database creates a PGA auxiliary memory structure for each user. PGA stores user-specific session information, such as Bind variable and Session variable.

Orcle instance

An Oracle instance consists of Oracle's main memory structure and several Oracle background processes; the main memory structure is also called SGA (System Global Area). When the user accesses the data in the database, Server Process communicates with SGA.

SGA component

Oracle divides SGA memory into groups called Granule to achieve dynamic space allocation. 4MB, 8MB OR 16MB

Shared storage pool: cache the most commonly used SQL statements (LRU) that have been issued by database users

The database buffer tells the cache to cache data recently accessed by the database user (LRU)

Redo log buffer: stores transaction information for recovery purposes

Java storage pool: optional to cache recently used Java objects and application code when the JVM option of Oracle is used

Large storage pools: caching data for large operations and Shared Server artifacts such as RMAN backup and recovery

Streaming storage pool: caches data associated with queued information requests when the Advanced Queuing option of Oracle is used

Parameters: basic and advanced parameters

Such as: SGA_TARGET

SQL > select * from V$SGA

Fixed Size: the auxiliary space used to store information used by the background process of the instance

Variable Size:Shared Pool,Large Pool,Java Pool

Database Buffers Cache:

Redo Log Buffer

SQL > select component,current_size from v$sga_dynamic_components

Or use EM DatabaseControl to view the size of each SGA component.

Background process of Oracle

Required: SMON (System Monitor), PMON (Process Monitor), DBWn (Database Writer), CKPT (Checkpoint)

Optional: ARCn (Archive), RECO (Recover), CJQn (Job Queue Monitor), Jnnn (Job Queue), Qnnn (Parallel Queue Slave), Dnnn (Dispatcher), Snnn (Shared Server), MMAN (Memory Manager), MMON (Memory Monitor), MMNL (Memory Monitor Light), RVWR (Recover Writer), CTWR (Change Tracking Writer)

View background process $ps-ef under Unix | grep PROD

In the windows environment, there is also a Windows service called OracleServiceInstanceName associated with each instance. You must start this service before you can start the instance.

Oracle database

An example is a temporary memory structure, but the Oracle database is made up of a set of physical files that reside on the disk drive of the host server. These physical files are called Control File,Data File,Redo File. The additional physical files related to the Oracle database but not technically part of the Oracle database are Password File,PFILE,SPFILE and Archived redo log file.

Control File:

Data File:

Redo File:

Know when the Database Writer,Log Writer process works.

Install Oracle 10g

Check the technical documentation-> check the system requirements-> make the installation plan (OFA)-> read the documentation in detail using OUI

* $2 create and control database * *

Oracle enterprise management framework

Oracle Enterprise Management Framework provides a comprehensive set of integration tools so that DBA can use these tools to accomplish traditional tasks more easily and effectively, and to monitor various components in the enterprise more effectively. DBA can customize this management framework.

The communication between the targets of the Oracle enterprise manager framework is handled by Oracle Management Agent (Oracle management agent).

The Oracle enterprise management framework is divided into the following functional areas.

Managed goals: DBA uses Database Manager to manage managed goals. Includes databases, application servers, Web servers, application software, and Oracle agents such as Oracle Net listeners and Connection Manager.

Oracle Management Service: a Java-based Web component that is the actual interface that DBA uses to monitor and control managed objectives within the Oracle Enterprise Manager framework.

Oracle management repository: configuration and monitoring information collected and related to managed targets is stored in an Oracle management repository.

Oracle Enterprise Manager 10g Grid Control: a Web-based user interface that manages many databases, application servers, Web servers, and other artifacts

Oracle Enterprise Manager 10 g database control: allows DBA to monitor and manage a single Oracle database instance or a single RAC (Real Application Cluster) environment.

Oracle Application Control:

Start and close the Oracle management agent

An Oracle management agent is a background process that runs on each managed target server. The agent collects data related to managed goals and then communicates with the central management service.

Bin > emctl start agent within the centralized Grid Control framework

Bin > emctl start dbconsole manages the database as a separate entity (including apache startup)

Bin > emctl status dbconsole

Access the database using Database Control: http://hostname:portnumber/em

Use iSql*Plus:bin > isqlplus start (Unix) windows to have the corresponding program

Get a list of ports used by the Oracle widget: check the $ORACLE_HOME/install directory directory portlist.ini file

Many steps must be performed to set iSql*Plus to SYSOPER,SYSDBA privilege. Look at help.

Use Oracle Database Conifguration Assitant

Start the Oracle instance test using EM Database Control

View and understand the contents of Oracle alarm log. Google, read more.

* $3 Database Storage and Schema objects * *

Create large and small file tablespaces

CREATE BIGFILE TABLESPACE hist2004apr DATAFILE'/ ORADATA/PROD/HIST2004APR.DBF' SIZE 25G

Processing Oracle management file tablespace

ALTER SYSTEM SET db_create_file='d:\ oracle\ oradata\ omf' scope=BOTH

Select the disk area management method

CREATE TABLESPACE hist2004apr

DATAFILE'/ ORADATA/PROD/HIST2004APR.DBF' SIZE 25G

EXTENT MANAGEMENT LOCAL UNIFORM

CREATE TABLESPACE hist2004apr

DATAFILE'/ ORADATA/PROD/HIST2004APR.DBF' SIZE 25G

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

Select segment space management method MANUAL, AUTO

CREATE TABLESPACE hist2004apr

DATAFILE'/ ORADATA/PROD/HIST2004APR.DBF' SIZE 25G

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT MANUAL; (default MANUAL)

CREATE TABLESPACE hist2004apr

'/ ORADATA/PROD/HIST2004APR.DBF' SIZE 25G

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

The SEGMENT SPACE MANAGEMENT AUTO; database uses bitmaps instead of free columns to identify which blocks are available for insert operations, ignoring the PCT_FREE and PCT_USED parameters

Note: PCT_FREE and PCT_USED aim at the space management mode of MANUAL manual section. Free quick list.

My oracle10g defaults to extent management:local

Segment space mangement:auto

System tables, temporary tables, undo tablespaces must be MANUAL.

Create temporary tablespaces (my system initially allocates space, and some UNIX systems allocate space delayed)

CREATE TEMPORARY TABLESPACE temp

TEMPFILE'C:\ ORACLE\ ORADATA\ ORA10\ TEMP01.DBF' SIZE 2G

Create an undo tablespace

Set up UNDO_MANAGEMENT=AUTO

CREATE DATABASE TEST

...

UNDO TABLESPACE undo

DATAFILE'C:\ ORADATA\ TEST\ undo01.dbf' SIZE 500m

AUTOEXTENT ON NEXT 100M MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

...

CREATE UNDO TABLESPACE undo

DATAFILE'/ ORADATA/PROD/UNDO01.DBF' SIZE 2G

Delete tablespace

DROP TABLESPACE hr_data INCLUDING CONTENTS AND DATAFILES

Modify tablespace

ALTER TABLESPACE fin RENAME TO payables

Add data files to the tablespace

ALTER TABLESPACE receivables ADD DATAFILE

'/ u02Compact oradata 'ORA10Universe receivables01.dbf'

SIZE 2G

Offline, online receivables tablespace

ALTER TABLESPACE receivables OFFLINE

ALTER TABLESPACE receivables ONLINE

Read-only

ALTER TABLESPACE sales2003 READ ONLY

ALTER TABLESPACE sales2003 READ WRITE

Put in backup mode

ALTER TABLESPACE sales2003 BEGIN BACKUP

ALTER TABLESPACE sales2003 END BACKUP

Transfer file: COPY or UNIX cp command in windows

New location

ALTER TABLESPACE receivables RENAME DATAFILE'H:\ ORACLE\ ORADATA\ ORA10\ RECEIVABLES02.DBF'

TO'C:\ ORACLE\ ORADATA\ ORA10\ RECEIVABLES02.DBF'

Get tablespace information

DBA_TABLESPACE

DBA_DATA_FILES

DBA_TEMP_FILES

V$TABLESPACE.

Use Schema (schema) objects

A Schema is a set of database objects owned by a particular database user. The schema has the same name as the database user, so the two terms are synonymous.

Schema objects include segments (tables, indexes, etc.) that the reader has seen in the tablespace, and non-segmented database objects owned by a user. Such as constraints, views, synonyms, procedures, packages.

Oracle specifies the data type: slightly

Character

Figures

Date number

LOB

ROWID

Binary system

The database LOB datatypes are as follows:

CLOB Stores variable-length character data.

NCLOB Stores variable-length character data using the Unicode character set.

BLOB Stores binary variable-length data inside the database. BLOB data does not undergo character

Set conversion when passed between databases or between client and server processes.

BFILE Stores binary variable-length data outside the database. BFILEs are limited to a maximum

Of 4GB of data and even less in some operating systems.

Here is an example of the LOB datatypes in use:

CREATE TABLE lob_examples

(id NUMBER

Name VARCHAR2 (32)

, description VARCHAR2 (4000)

, definition CLOB

, mp3 BLOB

) TABLESPACE USERS

LOB (definition) STORE AS

(TABLESPACE user3_data)

In addition to the namespaces shared by tables and views, the database has separate namespaces for the following:

Indexes

Constraint

Clustering

Database trigger

Dedicated database link

Dimension

Role

Public database link

Tablespace

General situation

Parameter file (PFILE)

Create a tabl

CREATE TABLE change_log

NOLOGGING COMPRESS

TABLESPACE archive

(log_id NUMBER

Who VARCHAR2 (64)

, when TIMESTAMP

, what VARCHAR2 (200)

);

The NOLOGGING option tells the database not to record the contents of the changed table in the redo log and not to record subsequent direct path insertions in the redo log.

The COMPRESS option tells the database to use database compression to add data to the table, thus requiring less disk space.

Create temporary tables: two kinds of ON COMMIT DELETE ROWS, ON COMMIT PRESERVE ROWS

Add comments to a table or column

COMMENT ON TABLE change_log IS

'this table is where you record changes to the configuration of the DEMO system'

DBA_COL_COMMENTS

Rename table: ALTER TABLE CHANGE_LOG RENAME TO DEMO_CHANGE_LOG

Add and delete columns from a table

ALTER TABLE change_log ADD how VARCHAR2 (45)

ALTER TABLE CHANGE_LOG ADD

(HOW VARCHAR2 (45)

WHY VARCHAR2 (60)

);

Modify column

ALTER TABLE CHANGE_LOG MODIFY

(WHAT VARCHAR2 (250)

WHO VARCHAR2 (50) DEFAULT USER

);

If the following appears in 9i, slightly. Speed up

Indexes

B-tree index is the default index type, which is suitable for medium or large cardinality columns. B-tree index supports row-level locking, so it is suitable for multi-user transaction applications. The index that supports PRIMARY KEY or UNIQUE constraints is the B-tree index.

Bitmap indexes are best suited for multiple combinations of low to medium cardinality columns (you cannot create a unique bitmap index), and they do not support row-level locking. Bitmap indexes are best in environments where data modification is limited and controlled, such as many data warehouse applications. Because bitmap indexes cannot effectively modify the indexed data, they are deleted before data loading starts and rebuilt after data loading is completed.

Processing sequence

Create sequence employee_seg start with 100500 nomaxvalue nominvalue

* $4 oracle Net service * *

Interprocess Communication interprocess communication (IPC) interprocess communication (IPC) is a set of technologies used by programs and processes running in multitasking operating systems or on networked computers.

One of the advantages of programs using IPC is that they can effectively take advantage of the processes of other programs or computers.

Although the network is becoming more and more complex, it is also becoming easier to use and manage.

Network responsibility of DBA

Understand the network configuration options available and know how to use them according to the needs of the organization.

Understand the organization's basic network architecture in order to make visionary design decisions.

Work closely with network engineers to ensure a consistent and reliable connection to the oracle server.

Learn about the various tools you can use to configure and manage your network

Diagnose and troubleshoot connection problems related to users, middle tiers, and servers.

Ensure a secure connection and, if necessary, use existing network configurations to achieve a high degree of security for sensitive data transmission

Keep up with the latest industry trends that may have an impact on network design decisions and the latest changes in Oracle architecture

Network configuration

The single-layer terminal is directly connected to the host computer, and there is no complexity of network protocol and multi-operating system.

Double layers are often called client / server computing. Scalability is not good.

The n-tier introduces middleware [MIDDLEWARE] components located between the client and the database server, such as the application server or Web server. The model is scalable and assigns tasks such as presentation, business logic and routing, and database processing to many computers. Many factors are driving n-tier computing, such as Internet and grid computing, which uses a large number of back-end processors to scale database services and connections.

Summary of the characteristics of Oracle

Connectivity: customers can interact with an oracle database in many ways (multi-protocol support, multi-operating system, JDBC)

Manageability: Web applications, location transparency (databases represent one or more database services), directory naming (Directory Naming)

Scalability: Oracle Shared Server,Oracle Connectiion Manager (multiplexing, network access, cross-protocol connectivity)

Curity: Oracle Advanced Security, firewall support

Accessibility: Heterogeneous Service (heterogeneous services), external processes

Directory naming: Directory Naming enables service names to be resolved through a centralized naming repository. The central repository takes the form of a LDAP (Lightweight Direcory Access Protocol) server. LDAP is a protocol and a language that defines a standard method for storing, identifying, and retrieving services, while providing a simplified way to manage a directory of information, whether the information is related to users in an organization or to Oracle services connected to a network.

Configure Oracle Net on the server

Learn about Oracle listeners, which respond to connection requests.

4 types of connection methods:

Dedicated connection: direct handshake method

Dedicated connections: redirection method

Oracle Shared Server: direct handshake method

Oracle Shared Server: redirect method

Manage Oracle listeners

Use Oracle Net Manager to manage listeners.

Managing listener tests with Oracle Enterprise Manager

Manage listeners with lsnrctl:

Launch: lsnrctl start

Reload the listener:: lsnrctl reload

Displays the status of the listener: lsnrctl status

Enumerating services: lsnrctl service

Register the service dynamically

The instance registers with the listener defined by the lock on the local computer. Dynamic service registration enables administrators to take advantage of other features, such as load balancing and automatic failover. The PMON process is responsible for registering this information with the listener.

When dynamic service registration is used, the administrator will not see the server listed in the lock in listener.ora. To view the services listed in the file, run the lsnrctl service command.

Configuration parameter: INSTANCE_NAME,SERVICE_NAME

Such as: Instance_name=DBA

Service_name=DBA.GR.COM

The listener must be configured as the default listener, otherwise the parameter LOCAL_LISTENER.

For example: local_listener= "(ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP) (HOST=WEISHAN) (PORT=1522)

Additional configuration when using multiple listeners

If you have a complex network environment with a large number of concurrent connection requests, or if you are using an advanced database design such as ORACLE RAC, you can configure multiple listeners to better manage connection loads. As a result, the reader increases the function. These features include:

Connect-Time Failover,Transparent Application Failover,Client Load Balancing,Connection Load Balancing.

Connect-Time Failover (connection time failover)

Multiple listener locations are specified in the customer tnsnames.ora. If connecting to the first listener fails, try to connect to the next listener in the listener list.

Transparent Application Failover (transparent application failover TAF)

For example, in RAC, TAF fails over and reestablishes the connection between the application and the service. It allows client applications to automatically reconnect to the database (if the connection fails) and optionally continue to execute a SELECT statement that is already running. Reconnection occurs automatically from the OCI library. test

Client Load Balancing (customer load balancing)

Allows customers to randomly pick from a listener list. Oracle Net traverses the listener list and balances the compliance of connection requests between existing listeners.

test

Connection Load Balancing (connection load balancing)

Allows better distributed connections between a set of schedulers within an Oracle Shared Server environment

Diagnose server segment connection problems

Server computer and database check

Check the server computer: ping

Check the database: sql*plus attempts a local connection

Check that the database is open to all users

Check user permissions

Server segment network check

Check the listener: lsnrctl status

Check GLOBAL_DBNAME

Check listener protocol: lsntctl service

Check server protocol: ping (tcp/ip)

Check the server protocol adapter: call the OUI program and check the list of installed protocols. On the Unix platform, you can use the Adapters utility to ensure that the appropriate protocol adapter is linked to Oracle.

[xxx]. / adapters oracle

Check connection timeout settings

INBOUND_CONNECT_TIMEOUT is set to a larger value to increase the waiting time for a valid response.

Oracle Net logs and traces on the server

Server log: listener log, on Unix system is $oracle_home/network/log,windows is% oracle_home%\ network\ log. test

Server tracking enable, view, test

Configure customer Oracle Net

Client name resolution: the customer must provide 3 pieces of information: user ID, password and network service name. The network service name provides the information necessary to find an oracle service in the network in the form of a connection descriptor.

Five parsing methods of network service.

Oracle Internet Directory,External Naming,Host Naming,Oracle easy connect, Local Naming.

Oracle Internet Directory: there are advantages when dealing with complex networks with many oracle servers. Administrators can configure and manage Net service Names and path descriptor information in a centralized location.

External Naming: use a non-oracle tool to manage and resolve Oracle service names, for example: NIS

The following three categories are mainly investigated.

Host Naming:

Oracle Easy Connect Naming:

10g newly introduced, specify host, port, service name

Such as: connect scott/tiger@jlsjls:1522/orcl.com

Local Naming: also known as the tnsnames.ora method. Network service name, network protocol, hostname and port, service name, test network service name connection

Use EM to configure local naming, testing

Diagnose and resolve client connection problems

Check customer / server contact: ping

Determine the network route that the customer is using to reach the server:

Xxx:/home/oracle > traceroute 10.15.9.11

Cmd > tracert 10.20.3.3

Check customer / listener contact

Cmd > tnsping orcl 2 (the tnsping tool attempts to connect to an oracle listener)

Check the local naming profile

Find multi-customer network profile (abbreviated)

Check the location of network files

Check NAMES.DIRECTORY_PATH

Check NAMES.DEFAULT_DOMAIN

Check the customer agreement adapter

Check the client error code

* $5 oracle shared server * *

Oracle Shared Server is suitable for "high thinking" applications. High-thinking applications consist of small transactions that use natural pauses in re-transaction mode. Many Web-based applications are suitable for this model. To determine whether an application is a candidate for Oracle Shared Server, check the application and consider the network traffic generated by a typical client interaction with the database server. Given the speed of the most popular network, and a typical customer interaction contains 16kb or less data, then it is a suitable candidate to use Oracle Shared Server.

Infrastructure of oracle Shared Server

Changes in PGA and SGA when using Oracle Shared Server

When Oracle Shared Server is configured, Oracle adds two new structures to SGA; the request queue drinks the response queue. These two structures do not exist in dedicated servers. For all schedulers, there is only one request queue, but each scheduler has its own response queue.

In a dedicated server environment, each server has a memory segment called PGA, which is the place to maintain information about each client session, including assignment variables, cursor information, and customer classification data. In the oracle shared server environment, this information is transferred to an area in the SGA called UGA. Readers can configure a special area of Large Pool in SGA to accommodate most of UGA.

Dedicated server PGA: cursor state, user session data, stack space

Shared server PGA: stack space

It can be seen from the above: dedicated server, UGA is allocated in the PGA. In SHARED SERVER, UGA is allocated in the LARGE POOL pool.

Virtual circuit: each connection being maintained by a scheduler is assigned to a shared memory segment and forms a virtual circuit. The scheduler uses this shared memory segment to manage communication between customer Oracle databases.

The listener receives a connection request, which checks the current connection compliance of each scheduler and redirects the customer connection request to the minimum load scheduler. Determine the minimum load scheduler for all nodes-if RAC is being used.

Configure Oracle Shared Server

Oracle 10g does not need to specify DISPATCHERS parameters to enable a shared server in a default network environment (simplified).

One of the advantages of oracle 10g is that all the parameters needed to manage Oracle Shared Server can be modified dynamically.

Use the DISPATCHERS parameter

Just specify ADDRESS,DESCRIPTION or PROTOCOL. The more data-intensive operations, the greater the number of concurrent connections, the fewer sessions each scheduler should handle. Generally speaking, the starting point is to reserve 50 concurrent sessions for each scheduler.

Readers can use the following formula to determine the number of schedulers to be configured initially:

Number of Dispatchers

= CEIL (maximum number of concurrent sessions/connections per dispatcher)

Example:DISPATCHERS= "(PRO=TCP) (DIS=3) (PRO=IPC) (DIS=2)"

The number of concurrent connections can be determined by the V$session view

V$LICENSE

SESSION_CURRENT: the current number of sessions

SESSION_HIGHWAITER: the maximum number of concurrent sessions since the instance was started

Modify: ALTER SYSTEM SET DISPATCHERS= "(PRO=TCP) (DIS=5)"

Configure connection collection characteristics using the DISPATCHERS parameter

Connection Pooling (connection hoard): Oracle Shared server is given the ability to connect a large number of connections by automatically disconnecting idle connections and using idle connections to service an input connection request.

Set the Pool,Tick property to read a book

DISPATCHERS= "(PROTOCOL=TCP) (DISPATCHERS=1) (POOL=ON) (TICK=1) (CONNECTIONS=500) (SESSIONS=1000)"

Use the MAX_DISPATCHERS parameter

Use the SHARED_SERVERS parameter: default 1 is 0 or is not set to disable. Generally speaking, for all kinds of high-thinking applications that will use shared server connections, 25 concurrent connections per shared server should be sufficient.

SHARED_SERVER_SESSIONS: use this parameter to limit the total number of shared server sessions.

MAX_SHARED_SERVER:

Manage shared servers

Lsnrctl service displays information about the scheduler process

Dynamic performance View

V$DISPATCHER: information about the scheduler, activity, the number of connections being processed, and the total number of connections processed since the instance was started.

Sql > select name,status,messages,idle,busy,bytes,breaks from v$dispatcher

New view of V$DISPATCHER_CONFIG about the configuration information of the scheduler.

Sql > select conf_indx,dispatchers,connections,sessions sess,service from v$dispatcher_config where network like'% TCP%'

V$DISPATCHER_RATE: statistics for the scheduler

Sql > select name,cur_event_rate,cur_msg_rate,cur_svr_byte_rate from v$dispatcher_rate

V$QUEUE: information about the request and response queues, such as how long the request is waiting in the queue

Sql > select * from v$queue

V$CIRCUIT: displaying information about oracle shared server virtual circuits

Sql > select circuit,dispatcher,server,waiter WTR,status,queue,bytes from v$circuit

V$SHARED_SERVER: displays information about shared server processes

Sql > select name,status,messages,bytes,idle,busy,requests from v$shared_server

Variety SHAREDSERVERTION MONITOR... Maximum number of concurrent connections, number of services started.. And other information is helpful to regulate oracle shared server.

Sql > select maximum_connections "MAX CONN", maximum_sessions "MAX SESS", servers_started "STARTED" from v$shared_server_monitor

V$SESSION: rich information about customer conversations.

Sql > select username,program,server from v$session

Request a private connection in a shared server environment

Add to the local name. Slightly

Adjust the shared server option test

Determine the Large Pool size: the Large Pool should be large enough to accommodate information about all shared server connections for the reader. Generally speaking, each connection requires space between 1MB and 3MB, but this depends on the type of activity of the customer. Objects that are doing a lot of sorting or opening many cursors will use more memory.

Sql > alter system set large_pool_size=10m

Sql > select * from v$sgastat where pool = 'large pool'

Determine if there are enough schedulers: if the scheduler's percentage of busy time is more than 50%, you need to consider starting more schedulers.

Sql > select name, (busy/ (busy+idle)) * 100 "Dispatcher% busy Rate" from v$dispatcher

Sql > alter system set dispatchers = "(PRO=TCP) (DIS=4)"

Measure how long the user waits for the scheduler: the value is too large and more schedulers need to be added.

Sql > select decode (sum (totalq), 0Magnum No response',Sum (wait) / sum (totalq)) "Average ait time" from v$queue QMagneVery dispatcher d where q.typewritten dispatch AND q.paddr=d.paddr

Monitor for a period of time, and if it grows, you need to consider adding more schedulers.

Determine if there are enough shared servers:.

Sql > select decode (totalq,0,'No Requests') "Wati Time", Wait/totalq | | 'hundredths of seconds' "Average wait time per request" from v$queue where type='COMMON'

Monitor it, and if it grows, consider adding more shared servers.

Query the current sql > select name,status,requests,messages,bytes,breaks from v$shared_server

Most of the above mark tests are not tested and are in a hurry to read books.

* $6 user Management and Security * *

The terms "user account", "account", "user" and "schema" are all interchangeable and refer to a database user account with schema objects.

Configure authentication: password authentication, external authentication, global authentication

Password authenticated users: brief

Externally authenticated user: when an externally authenticated user connects to the database, the database verifies that the user name is a valid database account and that the operating system has completed authentication. These accounts are sometimes called OPS$ (pronounced ahps dollar) accounts, and they must be prefixed when they are introduced. (generally, OPS$, has parameters that can be set)

CREATE USER OPS$ORACLE IDENTIFIED EXTERNALLY

External validation is often used in administrative scripts so that passwords do not have to be embedded in a script that humans can understand.

Globally authenticated user: when a globally authenticated user tries to connect to a database, the database validates the user name as a valid database account and passes the connection information to the advanced security options for verification. Advanced security options support several mechanisms for authentication, including biometrics, X.509 certificates, Kerberos and RADIUS.

CREATE USER SPY_MASTER IDENTIFIED GLOBALLY AS 'CN=SPY_MASTER,OU=TIER2,O=SECURITY,C=US'

Allocate default tablespace

.default tablespace.

Allocate temporary tablespace

.... temporary tablespace....

Assign a profile to the user

... profile resource_profile

Grant and revoke privileges

Object privileges: privileges on schema objects, such as tables, views, sequences, process-level packages, etc.

System privileges: privileges on database-level operations, such as connecting to a database.

Role privileges: the object and system privileges that a user has as a role.

Grant object privileges

Take a closer look at all kinds of permissions later.

Grant....with grant option... With grant option cannot be used when it is a character

If you cancel the privilege after using.. with grant option, the cancellation will have a cascade effect.

A grantee can obtain a privilege from multiple grantees, and when such multiple authorizations of the same privilege occur, canceling one of these authorizations does not delete the privilege.

Grant system privileges

Oracle has more than 170 system privileges, all of which are listed in the SYSTEM_PRIVILEGE_MAP data catalog view.

Be familiar with the following groups of privileges.

Database:

ALTER DATABASE,ALTER SYSTEM,AUDIT SYSTEM (statement audit), AUDIT ANY (object audit on objects in any schema)

Debug:

DEBUG CONNECT SESSION (allows the grantee to connect the current session to a scheduler.)

DEBUG ANY PROCEDURE (allows the licensee to debug all PL/SQL and Java code in the database. This is equivalent to granting DEBUG object privileges to each applicable object.

Index:

CREATE ANY INDEX: allows the grantee to create an index in any schema.

ALTER ANY INDEX: allows the licensee to change the index in any schema

DROP ANY INDEX: allows the grantee to delete the index from any schema.

Job Scheduler:

CREATE JOB: allows grantees to create assignments, programs, and schedules in their own mode.

CREATE ANY JOB:...

EXECUTE ANY PROGRAM:

EXECUTE ANY CLASS:

MANAGE SCHEDULER:

Process

CREATE PROCEDURE

CREATE ANY PROCEDURE

ALTER ANY PROCEDURE

DROP ANY PROCEDURE

EXECUTE ANY PROCEDURE

General situation

CREATE PROFILE

USER statement (requires ALTER USER privileges)

ALTER PROFILE

DROP PROFILE

Role

CREATE ROLE

ALTER ANY ROLE

DROP ANY ROLE

GRANT ANY ROLE

Sequence

CREATE SEQUENCE

CREATE ANY SEQUENCE

ALTER ANY SEQUENCE

DROP ANY SEQUENCE

SELECT ANY SEQUENCE

Conversation

CREATE SESSION

ALTER SESSION

ALTER RESOURCE COST

RESTRICTED SESSION

Synonym

CREATE SYNONYM

CREATE ANY SYNONYM

CREATE PUBLIC SYNONYM

DROP ANY SYNONYM

DROP PUBLIC SYNONYM

Table

CREATE TABLE

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

COMMENT ANY TABLE

SELECT ANY TABLE

INSERT ANY TABLE

UPDATE ANY TABLE

DELETE ANY TABLE

LOCK ANY TABLE

FLASHBACK ANY TABLE

.... Slightly

.

Like object privileges, system privileges can be granted to a special user, PUBLIC. Granting privileges to PUBLIC allows anyone with a database account to use this privilege.

If you grant a system privilege using the WITH ADMIN OPTION keyword and then cancel that privilege, the grantee's privilege will not be revoked. Unlike object privileges, the cancellation of system privileges does not cascade.

Role privilege

Role privileges grant a set of system, object, and other role privileges to the grantee. Roles can be password protected, so users may have a role that has been assigned to them, but cannot be used in all database sessions.

Create and manage roles

CREATE ROLE appl_dba

SET ROLE appl_dba IDENTIFIED BY seekwrit

Grant privileges to roles

GRANT oem_monitor TO charlie

GRANT PLUSTRACE TO PUBLIC

GRANT create ANY TABLE TO appl_dba with admin option

When it comes to granting WITH ADMIN OPTION to a role, the role behaves like a system privilege, and subsequent cancellations do not cascade.

Enable roles

Set role hr_admin identified by "myopia seekrit", employee

Set role all except hr_admin

View the roles that are enabled for the current session

Select role from sessin_roles

View roles that have been assigned to a user or special user PUBLIC.

USER_ROLE_PRIVS describes the roles granted to the current user.

Select granted_role from user_role_privs where username in (USER,'PUBLIC')

To see the roles that have been enabled and assigned directly to the reader or PUBLIC in the reader's session, but not those inherited by the reader, execute the following statements.

SELECT role FROM session_roles INTERSECT

SELECT granted_role FROM user_role_privs

WHERE username IN (USER,'PUBLIC')

Disable roles

SET ROLE NONE

SET ROLE ALL EXCEPT role_list

There is no way to selectively disable a single role. You cannot disable a role that you inherit as another role unless the parent role is disabled.

Set the default role

... ALTER USER scott DEFAULT ROLE ALL EXCEPT plustrace

Because the creator of a role automatically owns the role and the role is configured as a default role, administrative users who have created many roles (such as SYS or SYSTEM) may need to modify their default role list.

Control resource usage by user

Allocate tablespace limits

.... quota 100M ON USERS

Use profiles to allocate resource limits

A database call is a parsing, an execution or a fetch of data. Typically, the database implicitly makes these calls for the reader. The reader can explicitly make these database calls from JAVA,PS/SQL or OCI programs. A logical read is a measure of the amount of work done by the database during the execution of the SQL statement. The logical read is calculated as the sum of the consistent acquisition and the current pattern acquisition.

Logical reads include blocks read from both memory and disk.

Logical reads: The sum of "db block gets" plus "consistent gets"

Db block gets: Number of times a CURRENT block was requested

Consistent gets:Number of times a consistent read was requested for a block

Db_block_gets + consistent_gets = LOGICAL IO

Physical_reads = PHYSICAL IO

Http://www.oracledba.com.cn/blog/?p=37 has a good post. Read it later.

Enabled (9iPermine 10g is not enabled by default)

ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH

The following words that qualify resources are supported

CONNECT_TIME sql > CREATE PROFILE agent LIMIT CONNECT_TIME 10

CPU_PER_CALL sql > CREATE PROFILE agent LIMIT CPU_PER_CALL 3000; (in milliseconds, that is, a percentage of 1 second)

Sql > ALTER PROFILE data_analyst LIMIT CPU_PER_CALL UNLIMITED

CPU_PER_SESSION sql > CREATE PROFILE agent LIMIT CPU_PER_SESSION 30000

IDLE_TIME sql > ALTER PROFILE agent LIMIT IDLE_TIME 10 (minutes, long-running SQL statements are not affected by this setting)

LOGICAL_READS_PER_CALL sql > ALTER PROFILE agent LIMIT LOGICAL_READS_PER_CALL 2500

LOGICAL_READS_PER_SESSIONsql > ALTER PROFILE agent LIMIT LOGICAL_READS_PER_SESSION 1000000

PRIVATE_SGA sql > limits, in bytes, the amount of SGA memory that a user connected to a shared server can allocate to a persistent region in PGA. (book explanation)

Sql > the private space unit bytes that users can use in SGA. Some areas (UGA) of PGA in shared server mode are added to SGA (network interpretation)

Sql > ALTER PROFILE agent LIMIT PRIVATE_SGA 2500

SESSION_PER_USER sql > ALTER PROFILE agent LIMIT SESSION_PER_USER 2

COMPOSITE_LIMIT: limit the number of service units that can be consumed during a user session. The service unit is calculated as the weighted sum of the CPU_PER_SESSION,LOGICAL_READS_PER_SESSION,CONNECT_TIME,PRIVATE_SGA. The weighting is set using the ALTER RESOURCE COST statement, for example: alter resource cost connect_time 0; and can be viewed from the RESOURCE_COST data catalog view.

Sql > ALTER PROFILE agent LIMIT COMPOSITE_LIMIT 1000000

Sql > select * from dictionary where instr (comments,'profile') > 0; DBA_PROFILES

Apply the principle of least privilege

DBA_TAB_PRIVS:DBA_TAB_PRIVS describes all object grants in the database

DBA_SYS_PRIVS:DBA_SYS_PRIVS describes system privileges granted to users and roles.

Example: lists packages owned by user sys that have granted execute privileges to public

Select table_name from dba_tab_privs precedence dbathing objects o where p.owner=o.owner and p.table_name=o.object_name and p.ownerships SYS' and p.privilegebacks EXECUTE' and p.granteeholders PUBLIC' AND o.objectfully typewritten PACKAGE'

List all users with SELECT ANY TABLE privileges

Select * from DBA_SYS_PRIVS WHERE PRIVILEGE='SELECT ANY TABLE'

Protect the data directory: ensure that users with select_any_table privileges cannot access the underlying tables of the data directory by setting: (the first letter is European) O7_dictionary_accessibility=false. This is the default setting.

Remove unnecessary privileges from PUBLIC:

REVOKE EXECUTE ON utl_tcp FROM PUBLIC

REVOKE EXECUTE ON utl_smtp FROM PUBLIC

REVOKE EXECUTE ON utl_http FROM PUBLIC

REVOKE EXECUTE ON utl_file FROM PUBLIC

REVOKE EXECUTE ON dbms_obfuscation_toolkit FROM PUBLIC

REVOKE EXECUTE ON dbms_crypto FROM PUBLIC

Limit users with administrative privileges

SYSDBA: the highest, there is no reason to grant it to users other than SYS

DBA: it is prudent to allow authorized users to assign privileges and manipulate data throughout the database.

ANY system permissions: select any table,grant any role,delete anyy table...

The default setting for not enabling the REMOTE_OS_AUTHENT initialization parameter REMOTE_OS_AUTHENT is FALSE.

Each user should only be granted the minimum privileges required to perform his or her work. This principle is a principle centered on the initial closure principle.

We should take several actions to set or lock the database at the same time

Protect the data directory: ensure that users with SELECT ANY TABLE privileges cannot access the underlying tables of the data directory by setting 07_DICTIONARY_ACCESSIBILITY=FALSE.

Remove unnecessary privileges from PUBLIC. Read a book specifically

Restrict users with administrative privileges:

Look at the difference between DBA and SYSDBA

Do not enable REMOTE_OS_AUTHENT

Manage default user accounts

The sys and system accounts are the data directory owner and an administrative account, respectively. The Sysman and dbsnmp accounts are used by Enterprise Manager.

Implement standard password security features: find the location of the script and execute it.

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

@% ORACLE_HOME%\ rdbms/admin\ utlpwdmg.sql

Show tablespace limits

Select tablespace_name,username,bytes,max_bytes from dba_ts_quotas

Display information for the profile

Select profile,resource_name,limit from dba_profiles where profile='DEFAUTL'

Audit database activities ()

Audit records can be saved in a database or stored in operating system files for higher security

Audit_trail: where to write the audit records? The default location is NONE.

Sql > alter system set AUDIT_TRAIL=DB scope=spfile

Write to the database: most audit items are recorded in the sys.aud$ table.

Write to OS: unix--audit_file_dest; windows--Event Viewer log file

AUDIT_TRAIL=OS (no testing)

....

Four audit levels: statements, privileges, objects, and granular access.

Management statement audit

Audit table

Audit table by cxy

Audit table by cxy whenever not successful

Audit insert table by cxy by access

Identify enabled statement audit options

Select audit_option,failure,success,user_name from dba_stmt_audit_opts order by audit_option,user_name

Disable statement audit

Noaudit session

Noaudit not exists

Noaudit table by cxy

Check audit trail

Select username,timestamp,action_name from dba_audit_trail where username='TEST01'

Management privilege audit

Enable

Audit create any table

Audit create any table by test01

Identify privileged audit options that have been enabled

Select privilege,user_name from dba_priv_audit_opts order by privilege,user_name

Forbidden

Noaudit alter profile

Noaudit delete any table by juanita

Noaudit alter user by juanita

Manage object auditing: monitor and record the execution of sql statements that require a specified object privilege, such as select,insert,update,delete or execute object privileges

Unlike statement or system privilege auditing, schema object auditing cannot be limited to specified users-it is enabled for all users or for no users.

Enable

Audit select on hr.EMP_DETAILS_VIEW BY ACCESS WHENEVER SUCCESSFUL

AUDIT SELECT ON HR.EMP_DETAILS_VIEW BY SESSION WHENEVER NOT SUCCESSFUL

Identify enabled object audit options

SELECT owner,object_name,object_type,ins,sel FROM dba_obj_audit_opts WHERE wner='HR' AND object_name='EMP_DETAILS_VIEW'

Disable object auditing

NOAUDIT SELECT ON HR.EMP_DETAILS_VIEW WHENEVER NOT SUCCESSFUL

Clear audit trail

DELETE FROM SYS.AUD$ WHERE TIMESTAMP#

Fine-grained auditing (FGA) allows readers to monitor and record data access based on data content.

You can use the PL/ SQL package DBMSs _ FGA to configure and manage FGA

Parameter interpretation: object_schema,object_name,policy_name,audit_condition,audit_column,handler_schema,handler_module,enable,audit_trail,audit_column_ops

Object_schema: owner of the object to be audited, default NULL

Object_name: name of the object to be monitored

Policy_name: a unique name for heart policy

Audit_condition:sql expression, when true, an audit record is created with some syntax restrictions, such as functions, subqueries or sequences.

Audit_column: a comma-separated list of columns that the database will access. If the audit_column is referenced and the audit_condition is true, the audit record is created.

Handler_schema: the owner of the event handler

Handler_module: the name of the event handler procedure.

Enable: enabled, default true

Statement_types: to monitor those types of sql statements. Default is select

Audit_trail: whether to record the sql statement and the assignment variables used to trigger sql in the audit trail. The default value DBMS_FGA.DB_EXTENDED records it, and the parameter should be set to DBMS_FGA.DB to save space.

Audit_column_ops: two valid values: DBMS_FGA.ALL_COLUMNS DBMS_FGA.ANY_COLUMNS

Create FGA policy (not enabled)

EXECUTE DBMS_FGA.ADD_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'COMPENSATION_AUD',audit_column= >' SALARY,COMMISSION_PCT',enable= > FALSE,statement_types= > 'SELECT')

Enable

EXECUTE DBMS_FGA.ENABLE_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'COMPENSATION_AUD')

Forbidden

DBMS_FGA.DISABLE_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'COMPENSATION_AUD')

Delete FGA Policy

EXECUTE DBMS_FGA.DROP_POLICY (object_schema= > 'HR',object_name= >' EMPLOYEES',policy_name= > 'COMPENSATION_AUD')

Identify FGA policies in the database

SELECT policy_name,object_name | |'. | | object_name object_name,policy_column,enabled,audit_trail from dba_audit_policies

Make a report of FGA audit tracking data items

SELECT db_user,timestamp,userhost from dba_fga_audit_trail where policy_name='COMPENSATION_AUD'

* $7 use SQL,PL/SQL and utilities to manage data *

A large number of database features are implemented as PL/SQL programs, and knowing how to identify and deal with these PL/SQL programs is very important to the efficiency of database administrators.

There are five types of named PL/SQL programs: Function,Procedure,Package,Package body,Trigger, which are usually stored in a database. For each stored PL/SQL program

The name and source code are available from the DBA_SOURCE and DBA_TRIGGERS data catalog views, although some supplied packages are "wrapped" and binary.

Look at the sql Development Guide.

Processing function

DECLARE today DATE DEFAULT SYSDATE

Today:=SYSDATE

IF TO_Char (SYSDATE,'Day') = 'Monday'

SELECT COUNT (*) FROM hr.employees WHERE hire_date > SYSDATE-30

SELECT TRUNC (SYSDATE)

_ _ _

CREATE OR REPLACE FUNCTION is_weekend (

Check_date IN DATE DEFAULT SYSDATE)

RETURN VARCHAR2 AS

BEGIN

CASE TO_CHAR (check_date,'DY')

WHEN 'SAT' THEN

RETURN 'YES'

WHEN 'SUN' THEN

RETURN 'YES'

ELSE

RETURN 'NO'

END CASE

END

-

Treatment process

CREATE OR REPLACE PROCEDURE archive_orders

(cust_id IN NUMBER

, retention IN NUMBER) IS

BEGIN

DELETE orders

WHERE customer = cust_id

AND order_date

< SYSDATE - retention; INSERT INTO maint_log (action,action_date,who) VALUES ('archive orders '|| retention || ' for '|| cust_id ,SYSDATE,USER); END; --------------------------------------------------------------------- set serverout on EXEC DBMS_OUTPUT.PUT_LINE('HELLO WROLD!'); -------------------------------------------------- CALL DBMS_OUTPUT.PUT_LINE('HELLO WROLD!'); --------------------------------------------------- 处理程序包 一个程序包就是一个用于函数,过程和数据结构(比如记录,游标,变量,和约束)的容器.一个程序包拥有一个叫做规约(简写为spec)的公用部分和 一个叫做程序包体的私有部分. 程序包规约:PACKAGE类型 程序包体:PACKAGE BODY类型 ----------------------------------------------------- CREATE OR REPLACE PACKAGE table_util IS FUNCTION version RETURN VARCHAR2; PROCEDURE truncate (table_name IN VARCAHR2); END table_util; -------------------------------------------------- CREATE OR REPLACE PACKAGE BODY table_util IS version_string VARCHAR2(8) := '1.0.0'; FUNCTION version RETURN VARCHAR2 IS BEGIN RETURN version_string; END; PROCEDURE truncate(table_name IN VARCHAR2) IS BEGIN IF UPPER(table_name) = 'ORDER_STAGE' OR UPPER(table_name) = 'SALES_ROLLUP' THEN EXECUTE IMMEDIATE 'trunate table ' || UPPER(table_name); ELSE RAISE_APPLICATION_ERROR(-20010,Invalid table for truncate: '|| table_name); END IF; END; END table_util; ------------------------------------------------------------------------------------ 处理触发时间和管理触发器 DML,DDL,数据库事件 具体见书 -------------------------------------------------------------- CREATE OR REPLACE TRIGGER employee_trg BEFORE INSERT OR UPDATE OF hire_date ON employee FOR EACH ROW BEGIN log_update(USER,SYSTIMESTAMP); --这个函数查不到？？？ IF INSERTING THEN -- if fired due to insert :NEW.create_user := User; :NEW.create_ts :=SYSTIMESTAMP; ELSIF UPDATING THEN -- if fired due to update IF :OLD.hird_date :NEW.hire_date THEN RAISE_APPLICATION_ERROR(-20013,'update of hire_date not allowed'); END IF; END IF; END; ----------------------------------------------------------------------------------- 事件:INSERT,UPDATE,DELETE 顺序:在语句触发器之前-->

Before row trigger-- > after row trigger-- > after statement trigger.

DDL event triggers: brief

Database event triggers: brief

Use and manage PL/SQL programs

Desc package

Whenever a dependent object is compiled through an alter statement, a pl/sql program becomes invalid. The next call is to recompile automatically. But we can choose to compile the invalid PL/SQL manually.

Program, for example:

ALTER PROCEDURE archive_orders COMPILE

ALTER FUNCTION is_weekend COMPILE

Alter package table_util compile body

Configure PL/SQL for better performance

Newly introduced by PLSQL_WARNING:10G, identify potential problems and help developers create better programs. Disabled in production, alter system set plsql_warning='DISABLE:ALL'

Scope=both

PLSQL_DEBUG: forces subsequent pl/sql compilations to be translated and contain auxiliary debugging information. It is prohibited in production.

Alter system set plsql_debug = false scope=both

PLSQL_OPTIMIZE_MODE: an optimized compiler, enable it.

Alter system set plsql_optimize_level=2

PLSQL_CODE_TYPE: specifies whether the plsql code will be compiled to the default translated bytecode or this level code. Native code has longer compilation time, greater administrative overhead, and faster running time.

Yes.

Enable native compilation: C compiler, set the parameter PLSQL_NATIVE_LIBRARY_DIR, and ensure that the directory exists. Set the parameter PLSQL_CODE_TYPE='NATIVE'... Later testing

To create a directory object, you need to have create any directory system privileges, and be careful. This database uses the operating system credentials of the database instance owner.

Create directory dump_dir as'/ home/oracle/data_pump/dumps'; (create a directory with operating system commands to make sure it exists)

Create directory log_dir as'/ home/oracle/data_pump/logs'; (create a directory with operating system commands to make sure it exists)

Data dump

The Data Pump [data dump] tool is a feature introduced by 10g. It is a high-speed mechanism for transferring data or metadata between databases or between databases and operating system files. Data

Pump uses directory path upload and direct path loading technology. Unlike exp and imp, the Data Pump tool runs on the server. Therefore, the database administrator must use a database directory to specify the location of dump files and directory files, and give the user permission to manipulate the directory. (grant read,write on directory XXXXXX to public | XXXXX)

Export data using Data Pump

Run expdp:

Database export method: expdp system/cxx full=y dumpfile=chap7a:fulla%U.dmp,chap7b:fullb%U.dmp filesize 2G parallel=2 logfile=chap7:full.log

Object mode export

Expdp hr/hr dumpfile=chap7:hr.dmp logfile=chap7:hr.out

Table export mode

Expdp hr/hr dumpfile=chap7:job_tabs.dmp nologfile=y content=metadata_only tables=jobs,job_history

Tablespace export mode

Expdp system/cxy110 dumpfile=chap7:users_ts.dmp logfile=chap7:users_ts.out tablespaces=users (ora-39139 error, data pump does not support xml.... Solution: using traditional exp)

Expdp test07/cxy110 dumpfile=chap7:tbs07_ts.dmp logfile=chap7:tbs_ts.out tablespaces=tbs07

DBMS_DTAPUMP

Setting up a DATA PUMP with DBMS_DTAPUMP is more troublesome than using a stand-alone program, but provides higher functionality and control.

Example:

DECLARE

H2 NUMBER;-HANDLE FOR THE dATE pUMP SESSION

BEGIN

-- Obtain a handle to an export Data pump session

H2: = dbms_datapump.open (

Operation = > 'EXPORT'-- export not import

, job_mode = > 'SCHEMA');-- schema mode

-, job_mode = > 'FULL');-- database mode

-, job_mdoe = > 'TABLE');-- table mode

-, job_mode = > 'TABLESPACE');-- tablespace mode

-- define the log file

Dbms_datapump.add_file (

Handle = > H2-- from the open call

, filename = > 'hr.out'-- filename

, directory = > 'CHAP7'-- database directory object

, filetype = > dbms_datapump.ku$_file_type_log_file)

-- define the dump file

Dbms_datapump.add_file (

Handle = > H2-- from the open call

, filename = > 'hr.dmp'-- filename

, directory = > 'CHAP7'-- database directory object

, filetype = > dbms_datapump.ku$_file_type_dump_file)

-- define schemas to exprot

Dbms_datapump.metadata_filter (

Handle = > H2-- from the OPEN call

, name = > 'SCHEMA_EXPR'-- schema name filter

-, name = > 'INCLUDE_NAME_EXPR'-- TABLE NAME FILTER

-, name = > 'EXCLUDE_NAME_EXPR'-- table name filter

-, name = > 'TABLESPACE_EXPR'-- tablespace name filter

, value = >'IN ('hr'')');-- name list

-- invoke data pump

Dbms_datapump.start_job (handle = > H2);-- from the open call

-- run the job in the background

Dbms_datapump.detach (handle = > H2)

End

EM Database Control (briefly, you can generate PL/SQL code from it and be familiar with DATAPUMP)

Import data using Data Pump

Impdp system/paasword full=y dumpfile=chap7:FULL.DMP nologfile=y sqlfile=chap7:FULL.SQL (only generate DDL statements, not import data)

Impdp system/password network_link=prod schema= "HR" remap_schema= "HR:HR_TEST" content=metadata_only logfile=chap7:HR_TEST.LOG (first create the DB_LINK, is it not supported to connect to the remote ORACLE 9Icards?)

Impdp system/password full=y dumpfile=chap7:HR.DMP nologfile=y sqlfile=chap7:HR_proc_give.SQL include=PROCEDURE: "LIKE 'GIVE%'"

Impdp system/password network_link=prod schema= "HR" remap_schema= "HR:HR_TEST" content=data_only include=TABLE: "= 'DEPARTMENTS'" logfile=chap7:HR_TEST.LOG

Impdp system/cxyxxxx tablespaces=TBS07 dumpfile=chap7:tbs07_ts.dmp nologfile=y (import tablespaces, first create tablespaces and users in the target database, dumpfile is an export file for tablespaces)

Using packages and using OEM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.