DBA « El-Shihabi

ORACLE IS_NUMBER FUNCTION

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 [...]

Create Oracle Tablespace SQL

CREATE SMALLFILE TABLESPACE “TBS_NAME” DATAFILE ‘$ORACLE_BASE/$SID/file.dbf’ SIZE 5120M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

ORACLE - Shared Pool

GET POOL INFORMATION
select name,bytes/(1024*1024) MB from v$sgastat where pool=’shared pool’ order by bytes desc;

Oracle Database link

Create Database link:

desc user_db_links

set linesize 121
col db_link format a20
col username format a20
col password format a20
col host format a20
SELECT * FROM user_db_links;
SELECT * FROM all_db_links;
SELECT table_name, tablespace_name FROM user_tables@conn_user;

oracle timestamp

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

Rebuild all indexes once

declare
index_name VARCHAR2(100);
CURSOR i_c is select index_name from user_indexes;
begin
open i_c;
loop
fetch i_c into index_name;
EXIT WHEN i_c%NOTFOUND;
EXECUTE IMMEDIATE (’alter index ‘ || index_name || ‘ rebuild’);
end loop;
end;

Alter tablespace for table(s)

In oracle they call changing TABLESPACE for a Table(s) is Move, thus, you can move table between tablespaces by using this query

ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLE_SPACE_NAME

Analysis query

explain plan for
SELECT * FROM (SELECT ROWNUM as nrow, USERNAME,NAME,to_char(STARTDATE,’YYYY-MM-DD’) as STARTDATE,to_char(EXPIRYDATE,’YYYY-MM-DD’) as EXPIRYDATE FROM USERS u) where nrow >= 100 and nrow

Oracel Grant

Grant scheme to another user
GRANT SELECT ON SCHEMA.TABLE TO USER;

Grant Package to user
grant execute on sys.dbms_sql to user;

10g, Control File

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 [...]

El-Shihabi

Pages

Categories