Useful Scripts Collection Part 1

Do you have your own Blog or Website? Try PuppyURL - Free and Automated Directory Submission.


SCRIPT COLLECTION - How-to find global name of your database?
ora9i$maran> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------
tifa.prod.com.sg

find Primary key constraints for a given table name?
ora9i$maran> Select
2 c.CONSTRAINT_NAME as PCN , c.TABLE_NAME PTN, c.COLUMN_NAME PCOL
3 from
4 USER_CONS_COLUMNS C ,user_constraints T
5 where
6 t. CONSTRAINT_NAME= c.CONSTRAINT_NAME AND
7 t.TABLE_NAME =c.TABLE_NAME AND
8 T.CONSTRAINT_TYPE='P' AND
9 c.table_name = '&TNAM'
10 /
Enter value for tnam: RIGHTS
PCN ; PTN ; PCOL
----------------------------------------------------------------------
SYS_C001420 RIGHTS &n bsp; RIGHTS_ID

find available indexes for a given table name?
ora9i$maran> select table_name||' - '|| COLUMN_NAME||' - '|| INDEX_NAME
2 from user_ind_columns where table_name like UPPER('%&TN%')
3 /
TABLE_NAME||'-'||COLUMN_NAME||'-'||INDEX_NAME
------------------------------------------------- -------
ACCT_CODE_MAS$ - ACCT_ID - ACCT_ID$_PK
ACCT_TRANS_MAS$ - ACCT_TRANS_ID - ACCT_TRANS_ID$_PK

find Primary key and Unique key for a given table?
SQL> SELECT B.TABLE_NAME||' - '||B.COLUMN_NAME||' -'||B.CONSTRAINT_NAME||' '|| DECODE(A.CONSTRAINT_TYPE, 'P', ' Primary Key ','U', ' Unique')
3 FROM
4 USER_CONSTRAINTS A, USER_CONS_COLUMNS B
5 WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND
6 A.TABLE_NAME = B.TABLE_NAME AND
7 A.CONSTRAINT_TYPE in ('U','P') and
8 A.TABLE_NAME LIKE upper('%&tn')
9 /
Enter value for tn: ROLE
old 8: A.TABLE_NAME LIKE upper('%&tn')
new 8: A.TABLE_NAME LIKE upper('%ROLE')
B.TABLE_NAME||'-'||B.COLUMN_NAME||'-'||B.CONSTRAINT_NAME||''||DECODE(A.CONSTRAIN
-------------- ------------------------------------------------------------------
ROLE_MAS$ - ROLE_ID – ROLL_MAS_PK Primary Key
ROLE_MAS$ - DESCRIPTION - ROLL_MAS_DESC_U Unique

find the object created date?
ora9iAS$maran@TIFA> select OBJECT_NAME||' - '||OBJECT_TYPE||' - '||CREATED from
2 user_objects where OBJECT_NAME like upper('%&objname%')
3 /
Enter value for objname: COUNTER_MAS$
old 2: user_objects where OBJECT_NAME like upper('%&objname%')
new 2: user_objects where OBJECT_NAME like upper('%COUNTER%')
OBJECT_NAME||'-'||OBJECT_TYPE||'-'||CREATED
--------------------------------------------------------------------------------
COUNTER_MAS$ - TABLE - 12-APR-07
COUNTER__MAS$_BCK - TABLE - 12-APR-07

find all schemas and its created date?
ora9iAS$maran@TIFA> select username,CREATED from all_users order by 2,1;
USERNAME CREATED
------------------------------ ---------
SYS 23-NOV-04
SYSTEM 23-NOV-04
TIFA 23-NOV-04
find reference tables of a given primary table?

ora9iAS$maran@TIFA> Select c.TABLE_NAME FTN
2 from
3 USER_CONS_COLUMNS C ,user_constraints T
4 where
5 t. CONSTRAINT_NAME= c.CONSTRAINT_NAME AND
6 t.TABLE_NAME = c.TABLE_NAME AND
7 T.CONSTRAINT_TYPE='R' AND
8 t.R_CONSTRAINT_NAME = (SELECT c.CONSTRAINT_NAME as PCNfrom
9 USER_CONS_COLUMNS C ,user_constraints T
10 where
11 t. CONSTRAINT_NAME= c.CONSTRAINT_NAME AND
12 t.TABLE_NAME =c.TABLE_NAME AND
13 T.CONSTRAINT_TYPE='P' AND
14 c.table_name = '&TNAM')
15 /
Enter value for tnam: COUNTER_MAS$'
old 14: c.table_name = '&TNAM')
new 14: c.table_name = 'COUNTER_MAS$')
FTN
------------------------------
DAILYTRANSACTION_DET$
LOGIN_LOG_DET$

find server ip address?
ora9iAS$maran@TIFA> SELECT UTL_INADDR.get_host_address from dual;
GET_HOST_ADDRESS
------------------------------------------------------------------------------ --
192.168.1.200

find last number of a given sequence?
ora9iAS$maran@TIFA> select SEQUENCE_NAME, LAST_NUMBER from user_sequences where upper(SEQUENCE_NAME) like upper('%&sn%');
Enter value for sn: BANK
old 2: where upper(SEQUENCE_NAME) like upper('%&sn%')
new 2: where upper(SEQUENCE_NAME) like upper('%BANK%')
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
BANK_ID_SEQ 1

SQLLOADER:
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads.
Can I load more than one input file at the same time?
Yes.
LOAD DATA
INFILE 'C:empcsv1.csv'
INFILE 'C:empcsv2.csv'
INFILE 'C:empcsv3.csv'
TRUNCATE INTO TABLE EMP
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
SAL
)

You need to DBA privilege to run the following queries:

find out the objects created in SYSTEM tablespace other than SYSTEM, SYS users?
ora9i$maran> SELECT TABLE_NAME, OWNER from dba_tables
2 where TABLESPACE_NAME= 'SYSTEM' and
3 OWNER NOT IN ('SYSTEM','SYS');
TABLE_NAME OWNER
------------------------------------------------------------
COUNTER_MAS$ TIFA
RIGHTS_DET TIFA
2 rows selected.
ora9i$maran>

find size of the database?
ora9i$maran> select sum(bytes)/1024/1024 "Total DB size in Meg" from sys.v_$datafile;
Total DB size in Meg
--------------------
2543.4375

find segment size greater then 1 MB?
ora9i$maran> select OWNER||' - '||SEGMENT_NAME||' - '||(BYTES/1024)/1024||' MB'
2 from dba_segments where (BYTES/1024)/1024 >=1 AND
3 OWNER like UPPER('%&OWNER%')
4 /
Enter value for owner: UCB
OWNER||'-'||SEGMENT_NAME||'-'||(BYTES/1024)/1024||'MB'
---------------------------------------- ------------------------------------------
UCB - TERM1_DB - 6.515625 MB
UCB - TERM1_XL - 6.4609375 MB

find all schema size?
ora9i$maran> select owner , sum(bytes/1024/1024) MB
2 from dba_segments
3 group by owner order by 2
4 /
OWNER MB
----------------------------------------
KENT .0625
SYSTEM 3.34375
VIP 57.96875
CVB 263.25
ER_CVB 344.460938
SYS 391.492188

find users those who have DBA privilege?
ora9i$maran> select grantee "DBA Privilege"
2 from dba_role_privs
3 where granted_role='DBA';
DBA Privilege
------------------------------
SYS
SYSTEM

move objects from current tablespace to another
REM create a file name with movetsall.sql using this content
SET FEEDBACK OFF;
SET HEADING OFF;
SET ECHO OFF;
SET PAGESIZE 50000;
SET LINESIZE 100;
CLEAR SCREEN;
column movets new_value movets noprint
select 'c:'||'Move_Tablespace_Script_'||USER||'_'||to_char(sysdate,'DDMMYYYY')||'.SQL' movets from DUAL;
spool && movets
select decode( segment_type, 'TABLE',
segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || LOWER(segment_type) || ' ' || segment_name ||chr(10) ||
decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
' tablespace &Taget_Tablespace_Name ' ||chr(10) ||
' storage '||chr(10) ||
' ('||chr(10) ||
' initial ' || initial_extent || ' next ' ||next_extent ||chr(10) ||
' minextents ' || min_extents || ' maxextents ' ||max_extents ||chr(10) ||
' pctincrease ' || pct_increase || ' freelists ' ||
freelists ||chr(10) ||
' );'
from user_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
order by 1, 2
REM edit 'c:Move_Tablespace_Script__.SQL’, modify as needed and run it.

People who read this post also read :