|
|
CREATE OR REPLACE FUNCTION is_number(char_in VARCHAR2) RETURN NUMBER IS
BEGIN
FOR x IN 1 .. LENGTH(char_in) LOOP
— remove , & .
IF SUBSTR(char_in,x,1) in (’,’ , ‘.’ , ‘ ‘) THEN
RETURN 0;
END IF;
END LOOP;
IF [...]
Select to_char(to_date(’01-JAN-1970′) + 1257424753/86400,’DD-MM-YYYY HH:MI:SS’) from dual;
SELECT to_char(TO_DATE(’19700101000000′,’YYYYMMDDHH24MISS’)
+ NUMTODSINTERVAL(1257424753, ‘SECOND’),’DD_MM_YY HH24:MI:SS’) FROM DUAL
Control Files
A control file is a small binary file that is created during database creation. A control file tracks many of the internal database functions, such as the structure of the database, datafile locations, synchronization information, and backup and recovery information. Anytime there is a major change in the structure of the database, such as [...]
This is the simplest way to delete oracle trace files :
find -name \*.trc -exec rm {} \;
dh -sh .
REDO LOG
Redo logs are files used in recovery from an instance failure. The online redo log consists of two or more redo log files; each entry in these files records both committed and uncommitted transactions called redo entries. Each redo log is assigned a log sequence number. The redo entries are stored in the redo [...]
ANALYZE TABLE
There are several options for the ANALYZE TABLE command. You have the choice of analyzing a table with the COMPUTE or ESTIMATE options. The COMPUTE option will analyze the table based on all rows in the table, while the ESTIMATE option allows you to specify the number of rows or a percentage of all [...]
Advantage to locally managed tablespaces
Locally managed tablespaces are the default management option, but the DBA may still create dictionary-managed tablespaces. However, if the SYSTEM tablespace is created as a locally managed tablespace at database creation, then no tablespaces in that database can be dictionary managed. Furthermore, if the SYSTEM tablespace is a locally managed [...]
TABLESAPCE
The DBA can take tablespaces offline and bring them back online, to make a portion of the database temporarily unavailable to users. The DBA can also take a tablespace offline to move datafiles. Any tablespace can be taken offline except for the SYSTEM tablespace, which must remain online for the database to operate. Note that [...]
SQLPLUS* Load Data
DATA FILE FORMAT:
SQL*Loader can read two different types of datafile formats: fixed-width and delimited. In a fixed-width datafile, the columns of all rows in the file are set at a fixed width, much like a spreadsheet. In a delimited datafile, the data fields are separated by commas, tabs, semicolons, or some other delimiter. [...]
Hi, I wrote this notes after studying Oracle DBA Workshop1 then I figured out these notes below..
Data Dump
SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORY;
Create A directory
Create Directory dpump_dir1 as ‘c:\oracle\product\10.1.0\Oradata\my_expot’;
Grant a permission
GRANT read,write on directory dpump_dir1 to SCOTT;
Moving tablesapces
SELECT * FROM v$TRANSPOrtable_platform
In iSQL*Plus or SQL*Plus, take a tablespace offline using the ALTER TABLESPACE command,
using the syntax
ALTER TABLESPACE
OFFLINE
in [...]
|
|
