Oracle Programming with PL/SQL Collections

-->

Oracle Programming with PL/SQL Collections

Will write details about it. 

What are the advantages of view over table in oracle

What is the differences between table, view , synonym and alias in Oracle.

What are advantages and limitations of one over the other.

==============================================================================

Table :
========

Relational Database is composed of tables that contain related data.
Storage Unit Contain Rows and Columns.

View :
========

A view is a virtual table. Every view has a query attached to it.
(The query is a SELECT statement that identifies the columns
and rows of the table(s) the view uses.)

advantages of views
-------------------

1. Views are created from one or more than one table by joins with selected columns.
Hide data complexity. Simplify commands for the user and Store complex queries.

2. Views acts as a layer between user and table.

3. Provide an additional level of table security, by restricting access
to a predetermined set of rows and columns of a table.
(that is not done in tables)

4. Views reduces the effort for writing queries to access specific columns every time.

5. Reports can be created on views.

6. View doesn't contain any data.

7. Modification through a view (e.g. insert update delete) generally
not permitted (complex view).

8. Logical Subset of Tables

9. A VIEW is only a mirror image of table which is used at places where
large access to a table is required.

10.Present the data in a different perspective from that of the base table.



synonyms
================
A synonym is an alias for a table, view, sequence or program unit.
Provide location transparency for tables,views ,sequencies
Provide public access to an object

There are two types of synonyms private and public.
private synonyms

Only its owner can access a private synonym.

public synonym?
Any database user can access a public synonym.
synonyms used
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.


alias
=============
it just like nick name
we can used it in quries when two tables have same column name
and also in big quries we can use alias name
for easy understand and readability.
always use alias name

windows commands list

CMD Shell command in Windows XP

ADDUSERS Add or list users to/from a CSV file
ARP Address Resolution Protocol
ASSOC Change file extension associations•
ASSOCIAT One step file association
ATTRIB Change file attributes
b
BOOTCFG Edit Windows boot settings
BROWSTAT Get domain, browser and PDC info
c
CACLS Change file permissions
CALL Call one batch program from another•
CD Change Directory - move to a specific Folder•
CHANGE Change Terminal Server Session properties
CHKDSK Check Disk - check and repair disk problems
CHKNTFS Check the NTFS file system
CHOICE Accept keyboard input to a batch file
CIPHER Encrypt or Decrypt files/folders
CleanMgr Automated cleanup of Temp files, recycle bin
CLEARMEM Clear memory leaks
CLIP Copy STDIN to the Windows clipboard.
CLS Clear the screen•
CLUSTER Windows Clustering
CMD Start a new CMD shell
COLOR Change colors of the CMD window•
COMP Compare the contents of two files or sets of files
COMPACT Compress files or folders on an NTFS partition
COMPRESS Compress individual files on an NTFS partition
CON2PRT Connect or disconnect a Printer
CONVERT Convert a FAT drive to NTFS.
COPY Copy one or more files to another location•
CSCcmd Client-side caching (Offline Files)
CSVDE Import or Export Active Directory data
d
DATE Display or set the date•
DEFRAG Defragment hard drive
DEL Delete one or more files•
DELPROF Delete NT user profiles
DELTREE Delete a folder and all subfolders
DevCon Device Manager Command Line Utility
DIR Display a list of files and folders•
DIRUSE Display disk usage
DISKCOMP Compare the contents of two floppy disks
DISKCOPY Copy the contents of one floppy disk to another
DISKPART Disk Administration
DNSSTAT DNS Statistics
DOSKEY Edit command line, recall commands, and create macros
DSACLs Active Directory ACLs
DSAdd Add items to active directory (user group computer)
DSGet View items in active directory (user group computer)
DSQuery Search for items in active directory (user group computer)
DSMod Modify items in active directory (user group computer)
DSMove Move an Active directory Object
DSRM Remove items from Active Directory
e
ECHO Display message on screen•
ENDLOCAL End localisation of environment changes in a batch file•
ERASE Delete one or more files•
EVENTCREATE Add a message to the Windows event log
EXIT Quit the current script/routine and set an errorlevel•
EXPAND Uncompress files
EXTRACT Uncompress CAB files
f
FC Compare two files
FIND Search for a text string in a file
FINDSTR Search for strings in files
FOR /F Loop command: against a set of files•
FOR /F Loop command: against the results of another command•
FOR Loop command: all options Files, Directory, List•
FORFILES Batch process multiple files
FORMAT Format a disk
FREEDISK Check free disk space (in bytes)
FSUTIL File and Volume utilities
FTP File Transfer Protocol
FTYPE Display or modify file types used in file extension associations•
g
GLOBAL Display membership of global groups
GOTO Direct a batch program to jump to a labelled line•
GPUPDATE Update Group Policy settings
h
HELP Online Help
i
iCACLS Change file and folder permissions
IF Conditionally perform a command•
IFMEMBER Is the current user in an NT Workgroup
IPCONFIG Configure IP
k
KILL Remove a program from memory
l
LABEL Edit a disk label
LOCAL Display membership of local groups
LOGEVENT Write text to the NT event viewer
LOGOFF Log a user off
LOGTIME Log the date and time in a file
m
MAPISEND Send email from the command line
MBSAcli Baseline Security Analyzer.
MEM Display memory usage
MD Create new folders•
MKLINK Create a symbolic link (linkd)
MODE Configure a system device
MORE Display output, one screen at a time
MOUNTVOL Manage a volume mount point
MOVE Move files from one folder to another•
MOVEUSER Move a user from one domain to another
MSG Send a message
MSIEXEC Microsoft Windows Installer
MSINFO Windows NT diagnostics
MSTSC Terminal Server Connection (Remote Desktop Protocol)
MUNGE Find and Replace text within file(s)
MV Copy in-use files
n
NET Manage network resources
NETDOM Domain Manager
NETSH Configure Network Interfaces, Windows Firewall & Remote access
NETSVC Command-line Service Controller
NBTSTAT Display networking statistics (NetBIOS over TCP/IP)
NETSTAT Display networking statistics (TCP/IP)
NOW Display the current Date and Time
NSLOOKUP Name server lookup
NTBACKUP Backup folders to tape
NTRIGHTS Edit user account rights
p
PATH Display or set a search path for executable files•
PATHPING Trace route plus network latency and packet loss
PAUSE Suspend processing of a batch file and display a message•
PERMS Show permissions for a user
PERFMON Performance Monitor
PING Test a network connection
POPD Restore the previous value of the current directory saved by PUSHD•
PORTQRY Display the status of ports and services
POWERCFG Configure power settings
PRINT Print a text file
PRNCNFG Display, configure or rename a printer
PRNMNGR Add, delete, list printers set the default printer
PROMPT Change the command prompt•
PsExec Execute process remotely
PsFile Show files opened remotely
PsGetSid Display the SID of a computer or a user
PsInfo List information about a system
PsKill Kill processes by name or process ID
PsList List detailed information about processes
PsLoggedOn Who's logged on (locally or via resource sharing)
PsLogList Event log records
PsPasswd Change account password
PsService View and control services
PsShutdown Shutdown or reboot a computer
PsSuspend Suspend processes
PUSHD Save and then change the current directory•
q
QGREP Search file(s) for lines that match a given pattern.
r
RASDIAL Manage RAS connections
RASPHONE Manage RAS connections
RECOVER Recover a damaged file from a defective disk.
REG Registry: Read, Set, Export, Delete keys and values
REGEDIT Import or export registry settings
REGSVR32 Register or unregister a DLL
REGINI Change Registry Permissions
REM Record comments (remarks) in a batch file•
REN Rename a file or files•
REPLACE Replace or update one file with another
RD Delete folder(s)•
RMTSHARE Share a folder or a printer
ROBOCOPY Robust File and Folder Copy
ROUTE Manipulate network routing tables
RUNAS Execute a program under a different user account
RUNDLL32 Run a DLL command (add/remove print connections)
s
SC Service Control
SCHTASKS Schedule a command to run at a specific time
SCLIST Display NT Services
SET Display, set, or remove environment variables•
SETLOCAL Control the visibility of environment variables•
SETX Set environment variables permanently
SFC System File Checker
SHARE List or edit a file share or print share
SHIFT Shift the position of replaceable parameters in a batch file•
SHORTCUT Create a windows shortcut (.LNK file)
SHOWGRPS List the NT Workgroups a user has joined
SHOWMBRS List the Users who are members of a Workgroup
SHUTDOWN Shutdown the computer
SLEEP Wait for x seconds
SLMGR Software Licensing Management (Vista/2008)
SOON Schedule a command to run in the near future
SORT Sort input
START Start a program or command in a separate window•
SU Switch User
SUBINACL Edit file and folder Permissions, Ownership and Domain
SUBST Associate a path with a drive letter
SYSTEMINFO List system configuration
t
TASKLIST List running applications and services
TASKKILL Remove a running process from memory
TIME Display or set the system time•
TIMEOUT Delay processing of a batch file
TITLE Set the window title for a CMD.EXE session•
TLIST Task list with full path
TOUCH Change file timestamps
TRACERT Trace route to a remote host
TREE Graphical display of folder structure
TYPE Display the contents of a text file•
u
USRSTAT List domain usernames and last login
v
VER Display version information•
VERIFY Verify that files have been saved•
VOL Display a disk label•
w
WHERE Locate and display files in a directory tree
WHOAMI Output the current UserName and domain
WINDIFF Compare the contents of two files or sets of files
WINMSD Windows system diagnostics
WINMSDP Windows system diagnostics II
WMIC WMI Commands
x
XCACLS Change file and folder permissions
XCOPY Copy files and folders
:: Comment / Remark•

How to return multiple rows with oracle function

There are three basic ways to return multiple rows by oracle function:
======================================================================

A) use a refcursor as the return type
B) use an object table as the return type
C) use a mulit-delimited string as a return type (clob most likely)

A) use a refcursor as the return type
======================================

1.

CREATE OR REPLACE PACKAGE TYPES
AS
TYPE ref_c IS REF CURSOR;
END;

2.

CREATE OR REPLACE FUNCTION multiple_return
RETURN TYPES.ref_c
AS
l_cursor TYPES.ref_c;
BEGIN
OPEN l_cursor FOR
SELECT *
FROM emp;
RETURN l_cursor;
END;

3.

SELECT multiple_return
FROM DUAL




B)use an object table as the return type
==========================================

1.

CREATE OR REPLACE TYPE num_tab IS TABLE OF NUMBER;


2.

CREATE OR REPLACE PACKAGE pipeline
AS
FUNCTION get_num_row
RETURN num_tab PIPELINED;
END;


3.

CREATE OR REPLACE PACKAGE BODY pipeline
AS
FUNCTION get_num_row
RETURN num_tab PIPELINED
IS
BEGIN
FOR i IN 1 .. 20
LOOP
PIPE ROW (i);
END LOOP;

RETURN;
END get_num_row;
END;



4.

SELECT * FROM TABLE (pipeline.get_num_row);

why Perl.exe consume most of CPU% in oracle

Perl.exe consume most of CPU% in oracle

Some Questions:


Questions 1:-
=====================

when i open the oracle this perl.exe occupay all my cpu .
and then my PC is going to very very very slow .
plz i want know what to do now ..


Questions 2:-
=====================

After starting dbconsole (emctl start dbconsole) (Latest Oracle on a
Win2KSP4 box) and waiting a while two perl.exe processes come up and
start eating all the cpu and memory on the machine. I searched Metalink
and found that one possible cause may be the lack of a C: drive on the
host. This is true for me, so I mapped C: to a network drive (Metalink
suggested to install an USB drive) but the problem did not go away.

Any ideas?

Question 3 :-
====================

I installed 11g 64 bit on another win2003 64 bit machine,
did not patch this time. so the it's a clean plain vanilla version of 11g.
Perl.exe processes are still appearing and ramping up to G of memory
when the dbconsole is started.
It seems the perl.exe process hangs on this script
sysman/admin/scripts/lsnrresp.pl

I tested the listener and it is up and works perfectly,
I can connect to the machine remotely etc.

any ideas to help me progress on this?


===========================
Why run perl.exe in oracle
=========================

These scripts are part of the Enterprise Manager monitoring,
they are invoked by default. Dbresp.pl checks for user response time.
These perl scripts are internals .


Check metalink Note:419668.1,
it is one of the most common reason EM consume 100% CPU
and resolution/workaround is given in above ML note.



=======================================
Solutions
=======================================

solution:- 1
==================
I have stopped the enterprise manager and the perl script is
not running anymore

cmd> emctl stop dbconsole ;


solution:- 2
===================
I searched Metalink
and found that one possible cause may be the lack of a C: drive on the
host. This is true for me, so I mapped C: to a network drive (Metalink
suggested to install an USB drive) but the problem did not go away.
Came to work today, installed a USB drive to get a proper C: drive (the
mapped one does not suffice) and it solved the problem.


solution:- 3
====================
Turns out the machine had no C: drive, and some perl scripts
refer specifically to c:, had to edit a few perl files changing any
reference from c: to e:
all ok now.


solution:- 4
====================
I have had good luck with similar problems by reducing the DB_FILE_MULTIBLOCK_READ_COUNT.
Oracle used to say in its docs that MULTIBLOCK for OLTP should be between 4 and 16.




you must identified if there are other operating system proceess that
consuming hight porcent of resources, if you enviroment is Unix, check this command

$ top

$ ps -aef -o pcpu= -o pid= -o time= -o vsz= -o user= -o args=

If you enviroment is Windows,
you must check the Task Manager.



=============================================================

How to Recreate Enterprise Manager EM in oralce

How to Drop Enterprise Manager (EM).
=======================================

CMD> emca -deconfig dbcontrol db -repos drop

CMD> emctl status agent

CMD> emctl start dbconsole

CMD> NET HELPMSG 2185


How to Recreate Enterprise Manager (EM).
========================================

CMD> emca -repos create

CMD> emca -config dbcontrol db

Then log in:

http://localhost:5500/em