Free Download new
Last Update: 24/08/2007 10:15 PM SG
SCRIPT
COLLECTION -
How-to
find global name of your database?
ora9i$maran> select * from global_name;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_<USER>_<sysdate>.SQL’, modify as needed and run it
