SQL> select distinct substr(FILE_NAME,1,instr(FILE_NAME,'/',2)-1) as mount from dba_data_files union
select distinct substr(FILE_NAME,1,instr(FILE_NAME,'/',2)-1) as mount from dba_temp_files union
select distinct substr(MEMBER,1,instr(MEMBER,'/',2)-1) as mount from v$logfile union
select distinct substr(NAME,1,instr(NAME,'/',2)-1) as mount from v$controlfile order by 1;
MOUNT
---------------------------------------
/control1
/control2
/data1
/data2
/index1
/index2
/redologs1
/redologs2
/system
/temp
/undo
11 rows selected.
Monday, July 25, 2011
Getting datafile's mount points
Lets say that you have a policy for naming your filesystem mount points of your Oracle database, like mounting your filesystems in directories at the top of root, and you have a lot of filesystems for your database files. Then if you want to know the filesystem mount points you can extract them querying dba and v$ views with substr and instr functions like this:
Friday, July 22, 2011
Getting basic Solaris server information
If you need to know basic software, hardware and load information about a Solaris server then you can use the uname, prstat, psrinfo and prtdiag commands as shown below:
myserver% uname -a
SunOS myserver 5.10 Generic_127127-11 sun4v sparc SUNW,SPARC-Enterprise-T5220
myserver% prstat
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
710 root 5504K 2944K sleep 50 0 441:06:31 1.6% inetd/4
1 root 2360K 1168K sleep 59 0 12:17:41 0.7% init/1
26523 oracle 1721M 1700M sleep 59 0 1:37:00 0.4% oracle/1
155 root 6592K 4712K sleep 59 0 88:18:11 0.3% nscd/44
7721 oracle 573M 496M sleep 59 0 0:45:36 0.2% java/98
7110 oracle 1715M 1693M sleep 59 0 0:34:06 0.2% oracle/1
26289 oracle 491M 300M sleep 59 0 0:42:44 0.1% java/44
1500 myuser 1717M 1701M sleep 59 0 0:11:17 0.1% oracle/1
9 root 11M 7992K sleep 59 0 25:23:49 0.1% svc.configd/16
715 root 4424K 2152K sleep 59 0 22:31:24 0.1% syslogd/19
7140 oracle 1770M 1749M sleep 59 0 0:08:52 0.1% oracle/1
8654 myuser 48M 43M sleep 59 0 2:07:15 0.1% uv_scan/1
5707 myuser 1718M 1698M sleep 59 0 1:46:25 0.1% oracle/2
28243 oracle 1715M 1699M sleep 59 0 0:00:04 0.1% oracle/1
8672 myuser 19M 16M sleep 59 0 1:12:42 0.1% uvkd/1
7150 oracle 1714M 1694M sleep 59 0 0:12:46 0.1% oracle/1
8214 root 1416K 936K sleep 53 0 9:09:35 0.1% init.cssd/1
4483 oracle 161M 117M sleep 59 0 0:15:32 0.0% java/21
17881 root 5864K 3704K sleep 50 0 0:00:00 0.0% in.ftpd/1
17880 oracle 2088K 1936K cpu2 58 0 0:00:00 0.0% prstat/1
7092 oracle 1712M 1690M sleep 101 - 0:07:44 0.0% oracle/1
Total: 172 processes, 774 lwps, load averages: 3.92, 2.48, 2.17
myserver% psrinfo -pv
The physical processor has 64 virtual processors (0-63)
UltraSPARC-T2 (cpuid 0 clock 1165 MHz)
myserver% prtdiag
System Configuration: Sun Microsystems sun4v SPARC Enterprise T5220
Memory size: 65408 Megabytes
================================ Virtual CPUs ================================
CPU ID Frequency Implementation Status
------ --------- ---------------------- -------
0 1165 MHz SUNW,UltraSPARC-T2 on-line
1 1165 MHz SUNW,UltraSPARC-T2 on-line
2 1165 MHz SUNW,UltraSPARC-T2 on-line
3 1165 MHz SUNW,UltraSPARC-T2 on-line
4 1165 MHz SUNW,UltraSPARC-T2 on-line
5 1165 MHz SUNW,UltraSPARC-T2 on-line
6 1165 MHz SUNW,UltraSPARC-T2 on-line
7 1165 MHz SUNW,UltraSPARC-T2 on-line
8 1165 MHz SUNW,UltraSPARC-T2 on-line
9 1165 MHz SUNW,UltraSPARC-T2 on-line
10 1165 MHz SUNW,UltraSPARC-T2 on-line
11 1165 MHz SUNW,UltraSPARC-T2 on-line
12 1165 MHz SUNW,UltraSPARC-T2 on-line
13 1165 MHz SUNW,UltraSPARC-T2 on-line
14 1165 MHz SUNW,UltraSPARC-T2 on-line
15 1165 MHz SUNW,UltraSPARC-T2 on-line
16 1165 MHz SUNW,UltraSPARC-T2 on-line
17 1165 MHz SUNW,UltraSPARC-T2 on-line
18 1165 MHz SUNW,UltraSPARC-T2 on-line
19 1165 MHz SUNW,UltraSPARC-T2 on-line
20 1165 MHz SUNW,UltraSPARC-T2 on-line
21 1165 MHz SUNW,UltraSPARC-T2 on-line
22 1165 MHz SUNW,UltraSPARC-T2 on-line
23 1165 MHz SUNW,UltraSPARC-T2 on-line
24 1165 MHz SUNW,UltraSPARC-T2 on-line
25 1165 MHz SUNW,UltraSPARC-T2 on-line
26 1165 MHz SUNW,UltraSPARC-T2 on-line
27 1165 MHz SUNW,UltraSPARC-T2 on-line
28 1165 MHz SUNW,UltraSPARC-T2 on-line
29 1165 MHz SUNW,UltraSPARC-T2 on-line
30 1165 MHz SUNW,UltraSPARC-T2 on-line
31 1165 MHz SUNW,UltraSPARC-T2 on-line
32 1165 MHz SUNW,UltraSPARC-T2 on-line
33 1165 MHz SUNW,UltraSPARC-T2 on-line
34 1165 MHz SUNW,UltraSPARC-T2 on-line
35 1165 MHz SUNW,UltraSPARC-T2 on-line
36 1165 MHz SUNW,UltraSPARC-T2 on-line
37 1165 MHz SUNW,UltraSPARC-T2 on-line
38 1165 MHz SUNW,UltraSPARC-T2 on-line
39 1165 MHz SUNW,UltraSPARC-T2 on-line
40 1165 MHz SUNW,UltraSPARC-T2 on-line
41 1165 MHz SUNW,UltraSPARC-T2 on-line
42 1165 MHz SUNW,UltraSPARC-T2 on-line
43 1165 MHz SUNW,UltraSPARC-T2 on-line
44 1165 MHz SUNW,UltraSPARC-T2 on-line
45 1165 MHz SUNW,UltraSPARC-T2 on-line
46 1165 MHz SUNW,UltraSPARC-T2 on-line
47 1165 MHz SUNW,UltraSPARC-T2 on-line
48 1165 MHz SUNW,UltraSPARC-T2 on-line
49 1165 MHz SUNW,UltraSPARC-T2 on-line
50 1165 MHz SUNW,UltraSPARC-T2 on-line
51 1165 MHz SUNW,UltraSPARC-T2 on-line
52 1165 MHz SUNW,UltraSPARC-T2 on-line
53 1165 MHz SUNW,UltraSPARC-T2 on-line
54 1165 MHz SUNW,UltraSPARC-T2 on-line
55 1165 MHz SUNW,UltraSPARC-T2 on-line
56 1165 MHz SUNW,UltraSPARC-T2 on-line
57 1165 MHz SUNW,UltraSPARC-T2 on-line
58 1165 MHz SUNW,UltraSPARC-T2 on-line
59 1165 MHz SUNW,UltraSPARC-T2 on-line
60 1165 MHz SUNW,UltraSPARC-T2 on-line
61 1165 MHz SUNW,UltraSPARC-T2 on-line
62 1165 MHz SUNW,UltraSPARC-T2 on-line
63 1165 MHz SUNW,UltraSPARC-T2 on-line
======================= Physical Memory Configuration ========================
Segment Table:
--------------------------------------------------------------
Base Segment Interleave Bank Contains
Address Size Factor Size Modules
--------------------------------------------------------------
0x0 64 GB 8 8 GB MB/CMP0/BR0/CH0/D0
MB/CMP0/BR0/CH1/D0
8 GB MB/CMP0/BR0/CH0/D1
MB/CMP0/BR0/CH1/D1
8 GB MB/CMP0/BR1/CH0/D0
MB/CMP0/BR1/CH1/D0
8 GB MB/CMP0/BR1/CH0/D1
MB/CMP0/BR1/CH1/D1
8 GB MB/CMP0/BR2/CH0/D0
MB/CMP0/BR2/CH1/D0
8 GB MB/CMP0/BR2/CH0/D1
MB/CMP0/BR2/CH1/D1
8 GB MB/CMP0/BR3/CH0/D0
MB/CMP0/BR3/CH1/D0
8 GB MB/CMP0/BR3/CH0/D1
MB/CMP0/BR3/CH1/D1
========================= IO Configuration =========================
IO
Location Type Slot Path Name Model
----------- ----- ---- --------------------------------------------- ------------------------- ---------
MB/PCIE PCIE MB /pci@0/pci@0/pci@1/pci@0/pci@1/pci@0 pci-pciexclass,060400
MB/NET0 PCIE MB /pci@0/pci@0/pci@1/pci@0/pci@2/network@0 network-pciex8086,105e
MB/NET1 PCIE MB /pci@0/pci@0/pci@1/pci@0/pci@2/network@0,1 network-pciex8086,105e
MB/NET2 PCIE MB /pci@0/pci@0/pci@1/pci@0/pci@3/network@0 network-pciex8086,105e
MB/NET3 PCIE MB /pci@0/pci@0/pci@1/pci@0/pci@3/network@0,1 network-pciex8086,105e
MB/PCIE PCIE MB /pci@0/pci@0/pci@2/scsi@0 scsi-pciex1000,58 LSI,1068E
MB/PCIE1 PCIE 1 /pci@0/pci@0/pci@8/pci@0/pci@1/SUNW,qlc@0 SUNW,qlc-pciex1077,2432 QLE2460
MB/PCIE4 PCIE 4 /pci@0/pci@0/pci@8/pci@0/pci@2/network@0 network-pciex108e,abcd SUNW,pcie+
MB/PCIE4 PCIE 4 /pci@0/pci@0/pci@8/pci@0/pci@2/network@0,1 network-pciex108e,abcd SUNW,pcie+
MB/PCIE4 PCIE 4 /pci@0/pci@0/pci@8/pci@0/pci@2/network@0,2 network-pciex108e,abcd SUNW,pcie+
MB/PCIE4 PCIE 4 /pci@0/pci@0/pci@8/pci@0/pci@2/network@0,3 network-pciex108e,abcd SUNW,pcie+
MB/PCIE5 PCIE 5 /pci@0/pci@0/pci@8/pci@0/pci@8/pci@0 pci-pciex10b5,8114
MB/PCIE2 PCIE 2 /pci@0/pci@0/pci@9/SUNW,qlc@0 SUNW,qlc-pciex1077,2432 QLE2460
========================= HW Revisions =======================================
System PROM revisions:
----------------------
OBP 4.30.6 2009/12/01 12:40
IO ASIC revisions:
------------------
Location Path Device Revision
-------------------- --------------------------------------------- ------------------------------ ---------
MB/PCI-SWITCH0 /pci@0/pci@0 pciex10b5,8533.aa 170
MB/PCI-SWITCH2 /pci@0/pci@0/pci@1/pci@0 pciex10b5,8517.ac 172
MB/PCI-SWITCH1 /pci@0/pci@0/pci@8/pci@0 pciex10b5,8533.aa 170
MB/GBE0 /pci@0/pci@0/pci@1/pci@0/pci@2/network@0 pciex8086,105e.108e.105e.6 6
MB/GBE0 /pci@0/pci@0/pci@1/pci@0/pci@2/network@0,1 pciex8086,105e.108e.105e.6 6
MB/GBE1 /pci@0/pci@0/pci@1/pci@0/pci@3/network@0 pciex8086,105e.108e.105e.6 6
MB/GBE1 /pci@0/pci@0/pci@1/pci@0/pci@3/network@0,1 pciex8086,105e.108e.105e.6 6
MB/SAS-SATA-HBA /pci@0/pci@0/pci@2/scsi@0 pciex1000,58.1000.1000.4 4
============================ Environmental Status ============================
Fan sensors:
------------------------------------------------------------
Location Sensor Status
------------------------------------------------------------
SYS/FANBD0/FM0/F0 TACH ok
SYS/FANBD0/FM0/F1 TACH ok
SYS/FANBD0/FM1/F0 TACH ok
SYS/FANBD0/FM1/F1 TACH ok
SYS/FANBD0/FM2/F0 TACH ok
SYS/FANBD0/FM2/F1 TACH ok
Fan indicators:
------------------------------------------------------------
Location Sensor Condition
------------------------------------------------------------
SYS/PS0 FAN_FAULT ok
SYS/PS1 FAN_FAULT ok
Temperature sensors:
------------------------------------------------------------
Location Sensor Status
------------------------------------------------------------
SYS/MB T_AMB ok
SYS/MB/CMP0/BR0/CH0/D0 T_AMB ok
SYS/MB/CMP0/BR0/CH0/D1 T_AMB ok
SYS/MB/CMP0/BR0/CH1/D0 T_AMB ok
SYS/MB/CMP0/BR0/CH1/D1 T_AMB ok
SYS/MB/CMP0/BR1/CH0/D0 T_AMB ok
SYS/MB/CMP0/BR1/CH0/D1 T_AMB ok
SYS/MB/CMP0/BR1/CH1/D0 T_AMB ok
SYS/MB/CMP0/BR1/CH1/D1 T_AMB ok
SYS/MB/CMP0/BR2/CH0/D0 T_AMB ok
SYS/MB/CMP0/BR2/CH0/D1 T_AMB ok
SYS/MB/CMP0/BR2/CH1/D0 T_AMB ok
SYS/MB/CMP0/BR2/CH1/D1 T_AMB ok
SYS/MB/CMP0/BR3/CH0/D0 T_AMB ok
SYS/MB/CMP0/BR3/CH0/D1 T_AMB ok
SYS/MB/CMP0/BR3/CH1/D0 T_AMB ok
SYS/MB/CMP0/BR3/CH1/D1 T_AMB ok
SYS/MB/CMP0 T_TCORE ok
SYS/MB/CMP0 T_BCORE ok
Temperature indicators:
------------------------------------------------------------
Location Indicator Condition
------------------------------------------------------------
SYS/PS0 TEMP_FAULT ok
SYS/PS1 TEMP_FAULT ok
Current sensors:
------------------------------------------------------------
Location Sensor Status
------------------------------------------------------------
SYS/PS0 I_IN_MAIN ok
SYS/PS0 I_IN_LIMIT ok
SYS/PS0 I_OUT_MAIN ok
SYS/PS0 I_OUT_LIMIT ok
SYS/PS1 I_IN_MAIN ok
SYS/PS1 I_IN_LIMIT ok
SYS/PS1 I_OUT_MAIN ok
SYS/PS1 I_OUT_LIMIT ok
Current indicators:
------------------------------------------------------------
Location Indicator Condition
------------------------------------------------------------
SYS/MB I_USB0 ok
SYS/MB I_USB1 ok
SYS/PS0 CUR_FAULT ok
SYS/PS1 CUR_FAULT ok
Voltage sensors:
------------------------------------------------------------
Location Sensor Status
------------------------------------------------------------
SYS/MB V_VMEML ok
SYS/MB V_VMEMR ok
SYS/MB V_+3V3_STBY ok
SYS/MB V_VCORE ok
SYS/MB V_+3V3_MAIN ok
SYS/MB V_VDDIO ok
SYS/MB V_+12V0_MAIN ok
SYS/MB V_VBAT ok
SYS/PS0 V_IN_MAIN ok
SYS/PS0 V_OUT_MAIN ok
SYS/PS1 V_IN_MAIN ok
SYS/PS1 V_OUT_MAIN ok
Voltage indicators:
------------------------------------------------------------
Location Indicator Condition
------------------------------------------------------------
SYS/MB VCORE_POK ok
SYS/MB VMEML_POK ok
SYS/MB VMEMR_POK ok
SYS/PS0 AC_POK ok
SYS/PS0 DC_POK ok
SYS/PS0 VOLT_FAULT ok
SYS/PS1 AC_POK ok
SYS/PS1 DC_POK ok
SYS/PS1 VOLT_FAULT ok
LEDs:
------------------------------------------------------------
Location LED State
------------------------------------------------------------
SYS SERVICE off
SYS LOCATE off
SYS ACT steady
SYS PS_FAULT off
SYS TEMP_FAULT off
SYS FAN_FAULT off
SYS/MB/CMP0/BR0/CH0/D0 SERVICE off
SYS/MB/CMP0/BR0/CH0/D1 SERVICE off
SYS/MB/CMP0/BR0/CH1/D0 SERVICE off
SYS/MB/CMP0/BR0/CH1/D1 SERVICE off
SYS/MB/CMP0/BR1/CH0/D0 SERVICE off
SYS/MB/CMP0/BR1/CH0/D1 SERVICE off
SYS/MB/CMP0/BR1/CH1/D0 SERVICE off
SYS/MB/CMP0/BR1/CH1/D1 SERVICE off
SYS/MB/CMP0/BR2/CH0/D0 SERVICE off
SYS/MB/CMP0/BR2/CH0/D1 SERVICE off
SYS/MB/CMP0/BR2/CH1/D0 SERVICE off
SYS/MB/CMP0/BR2/CH1/D1 SERVICE off
SYS/MB/CMP0/BR3/CH0/D0 SERVICE off
SYS/MB/CMP0/BR3/CH0/D1 SERVICE off
SYS/MB/CMP0/BR3/CH1/D0 SERVICE off
SYS/MB/CMP0/BR3/CH1/D1 SERVICE off
SYS/HDD0 SERVICE off
SYS/HDD0 OK2RM off
SYS/HDD1 SERVICE off
SYS/HDD1 OK2RM off
SYS/HDD2 SERVICE off
SYS/HDD2 OK2RM off
SYS/HDD3 SERVICE off
SYS/HDD3 OK2RM off
SYS/HDD4 SERVICE off
SYS/HDD4 OK2RM off
SYS/HDD5 SERVICE off
SYS/HDD5 OK2RM off
SYS/HDD6 SERVICE off
SYS/HDD6 OK2RM off
SYS/HDD7 SERVICE off
SYS/HDD7 OK2RM off
SYS/FANBD0/FM0 SERVICE off
SYS/FANBD0/FM1 SERVICE off
SYS/FANBD0/FM2 SERVICE off
============================ FRU Status ============================
Location Name Status
------------------------------------------------------
SYS MB enabled
SYS/MB RISER0 enabled
SYS/MB RISER1 enabled
SYS/MB RISER2 enabled
SYS/MB SCC_NVRAM enabled
SYS/MB/CMP0/BR0/CH0 D0 enabled
SYS/MB/CMP0/BR0/CH0 D1 enabled
SYS/MB/CMP0/BR0/CH1 D0 enabled
SYS/MB/CMP0/BR0/CH1 D1 enabled
SYS/MB/CMP0/BR1/CH0 D0 enabled
SYS/MB/CMP0/BR1/CH0 D1 enabled
SYS/MB/CMP0/BR1/CH1 D0 enabled
SYS/MB/CMP0/BR1/CH1 D1 enabled
SYS/MB/CMP0/BR2/CH0 D0 enabled
SYS/MB/CMP0/BR2/CH0 D1 enabled
SYS/MB/CMP0/BR2/CH1 D0 enabled
SYS/MB/CMP0/BR2/CH1 D1 enabled
SYS/MB/CMP0/BR3/CH0 D0 enabled
SYS/MB/CMP0/BR3/CH0 D1 enabled
SYS/MB/CMP0/BR3/CH1 D0 enabled
SYS/MB/CMP0/BR3/CH1 D1 enabled
SYS HDD0 enabled
SYS HDD1 enabled
SYS HDD2 enabled
SYS HDD3 enabled
SYS HDD4 enabled
SYS HDD5 enabled
SYS HDD6 enabled
SYS HDD7 enabled
SYS PDB enabled
SYS SASBP enabled
SYS DVD enabled
SYS USBBD enabled
SYS FANBD0 enabled
SYS/FANBD0 FM0 enabled
SYS/FANBD0 FM1 enabled
SYS/FANBD0 FM2 enabled
SYS FANBD1 enabled
SYS PS0 enabled
SYS PS1 enabled
============================ FW Version ============================
Version
------------------------------------------------------------
Sun System Firmware 7.2.7.d 2010/02/02 17:25
====================== System PROM revisions =======================
Version
------------------------------------------------------------
OBP 4.30.6 2009/12/01 12:40
Chassis Serial Number
---------------------
ABC01234JN
Thursday, July 21, 2011
Moving tables and indexes in Oracle
If you need to move some tables or indexes to other tablespace in order to reorganize your database storage, or you just want to rebuild them to compact and recover space, you can use sentences like these:
You can move tables and indexes with the database opened, but these objects will be locked while being moved so this might be a problem with big objects heavily used. Also, after moving a table you have to rebuild all its indexes because they become invalid after the table movement.
You can also move partitioned tables and indexes but you have to move the partitions (or subpartitions) because the partitioned tables and indexes are placed in no tablespace:
As long as you cannot move one whole schema (or even a single partitioned table or index) with just one SQL sentence, you can generate the SQL sentences needed to move objects this way:
And if you need to know information about tablespaces, indexes and datafiles you can get it like this:
SQL> alter table SCOTT.DEPT move tablespace users;
Table altered.
SQL> alter index SCOTT.PK_DEPT rebuild tablespace users;
Index altered.
You can move tables and indexes with the database opened, but these objects will be locked while being moved so this might be a problem with big objects heavily used. Also, after moving a table you have to rebuild all its indexes because they become invalid after the table movement.
You can also move partitioned tables and indexes but you have to move the partitions (or subpartitions) because the partitioned tables and indexes are placed in no tablespace:
SQL> select distinct TABLE_NAME from DBA_TAB_PARTITIONS where TABLE_OWNER='SH';
TABLE_NAME
------------------------------------------------------------------------------------------
COSTS
SALES
SQL> select distinct SUBPARTITION_COUNT from DBA_TAB_PARTITIONS where TABLE_OWNER='SH';
SUBPARTITION_COUNT
------------------
0
SQL> column TABLE_OWNER format a30
SQL> column TABLE_NAME format a30
SQL> select TABLE_OWNER, TABLE_NAME, PARTITION_NAME from DBA_TAB_PARTITIONS where
TABLE_OWNER='SH' and TABLE_NAME='SALES' order by TABLE_OWNER, TABLE_NAME, PARTITION_POSITION;
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SH SALES SALES_1995
SH SALES SALES_1996
SH SALES SALES_H1_1997
SH SALES SALES_H2_1997
...
SQL> alter table SH.SALES move partition SALES_1995 tablespace example;
Table altered.
SQL> select distinct INDEX_NAME from DBA_IND_PARTITIONS where INDEX_OWNER='SH';
INDEX_NAME
------------------------------------------------------------------------------------------
SALES_CHANNEL_BIX
SALES_CUST_BIX
SALES_PROMO_BIX
SALES_PROD_BIX
COSTS_PROD_BIX
SALES_TIME_BIX
COSTS_TIME_BIX
7 rows selected.
SQL> select distinct SUBPARTITION_COUNT from DBA_IND_PARTITIONS where INDEX_OWNER='SH';
SUBPARTITION_COUNT
------------------
0
SQL> select INDEX_OWNER, INDEX_NAME, PARTITION_NAME from DBA_IND_PARTITIONS where
INDEX_OWNER='SH' and INDEX_NAME like 'SALES%'
order by INDEX_OWNER, INDEX_NAME, PARTITION_POSITION;
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SH SALES_CHANNEL_BIX SALES_1995
SH SALES_CHANNEL_BIX SALES_1996
SH SALES_CHANNEL_BIX SALES_H1_1997
SH SALES_CHANNEL_BIX SALES_H2_1997
...
SQL> alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_1995 tablespace example;
Index altered.
As long as you cannot move one whole schema (or even a single partitioned table or index) with just one SQL sentence, you can generate the SQL sentences needed to move objects this way:
SQL> select 'alter table '||OWNER||'.'||TABLE_NAME||' move tablespace example;'
from dba_tables where TABLESPACE_NAME='USERS' order by OWNER, TABLE_NAME;
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVETABLESPACEEXAMPLE;'
------------------------------------------------------------------------------------
alter table SCOTT.BONUS move tablespace example;
alter table SCOTT.DEPT move tablespace example;
alter table SCOTT.EMP move tablespace example;
alter table SCOTT.SALGRADE move tablespace example;
4 rows selected.
SQL> select 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild tablespace example;'
from dba_indexes where TABLESPACE_NAME='USERS' order by OWNER, INDEX_NAME;
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDTABLESPACEEXAMPLE;'
------------------------------------------------------------------------------------
alter index SCOTT.PK_DEPT rebuild tablespace example;
alter index SCOTT.PK_EMP rebuild tablespace example;
2 rows selected.
SQL> select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME||' move partition '||PARTITION_NAME||' tablespace example;'
from DBA_TAB_PARTITIONS where TABLE_OWNER='SH' and TABLE_NAME='SALES'
order by TABLE_OWNER, TABLE_NAME, PARTITION_POSITION;
'ALTERTABLE'||TABLE_OWNER||'.'||TABLE_NAME||'MOVEPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE;'
------------------------------------------------------------------------------------
alter table SH.SALES move partition SALES_1995 tablespace example;
alter table SH.SALES move partition SALES_1996 tablespace example;
alter table SH.SALES move partition SALES_H1_1997 tablespace example;
alter table SH.SALES move partition SALES_H2_1997 tablespace example;
...
SQL> select 'alter index '||INDEX_OWNER||'.'||INDEX_NAME||' rebuild partition '||PARTITION_NAME||' tablespace example;'
from DBA_IND_PARTITIONS where INDEX_OWNER='SH' and INDEX_NAME like 'SALES%'
order by INDEX_OWNER, INDEX_NAME, PARTITION_POSITION;
'ALTERINDEX'||INDEX_OWNER||'.'||INDEX_NAME||'REBUILDPARTITION'||PARTITION_NAME||'TABLESPACEEXAMPLE;'
-------------------------------------------------------------------------------------------------
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_1995 tablespace example;
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_1996 tablespace example;
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_H1_1997 tablespace example;
alter index SH.SALES_CHANNEL_BIX rebuild partition SALES_H2_1997 tablespace example;
...
And if you need to know information about tablespaces, indexes and datafiles you can get it like this:
SQL> select i.TABLE_OWNER, t.TABLE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and t.TABLESPACE_NAME in ('USERS')
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME;
TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME TABLESPACE_NAME
------------- ------------- -------------- ------------- ------------------
SCOTT DEPT SCOTT PK_DEPT USERS
SCOTT EMP SCOTT PK_EMP USERS
SQL> select i.TABLE_OWNER, t.TABLE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and t.TABLESPACE_NAME in ('USERS') and i.TABLESPACE_NAME not in ('USERS')
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME;
no rows selected
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, t.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.TABLESPACE_NAME in ('USERS')
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME;
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
SCOTT PK_DEPT SCOTT DEPT USERS
SCOTT PK_EMP SCOTT EMP USERS
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, t.TABLESPACE_NAME from dba_tables t, dba_indexes i
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.TABLESPACE_NAME in ('USERS') and t.TABLESPACE_NAME not in ('USERS')
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME;
no rows selected
SQL> select i.TABLE_OWNER, t.TABLE_NAME, pt.TABLESPACE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.PARTITION_NAME, pi.TABLESPACE_NAME
from dba_tables t, dba_indexes i, DBA_IND_PARTITIONS pi, (select distinct TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME from DBA_TAB_PARTITIONS) pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pt.TABLESPACE_NAME in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME, pi.PARTITION_POSITION;
TABLE_OWNER TABLE_NAME TABLESPACE_NAME INDEX_OWNER INDEX_NAME PARTITION_NAME TABLESPACE_NAME
------------ ------------- ----------------- ------------- -------------------- ----------------- -----------------
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_1995 EXAMPLE
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_1996 EXAMPLE
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_H1_1997 EXAMPLE
SH SALES EXAMPLE SH SALES_CHANNEL_BIX SALES_H2_1997 EXAMPLE
...
SQL> select i.TABLE_OWNER, t.TABLE_NAME, pt.TABLESPACE_NAME, i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.PARTITION_NAME, pi.TABLESPACE_NAME
from dba_tables t, dba_indexes i, DBA_IND_PARTITIONS pi, (select distinct TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME from DBA_TAB_PARTITIONS) pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pt.TABLESPACE_NAME in ('EXAMPLE') and pi.TABLESPACE_NAME not in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.TABLE_OWNER, t.TABLE_NAME, i.OWNER, i.INDEX_NAME, pi.PARTITION_POSITION;
no rows selected
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.TABLESPACE_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_NAME, pt.TABLESPACE_NAME
from dba_tables t, dba_indexes i, (select distinct INDEX_OWNER, INDEX_NAME, TABLESPACE_NAME from DBA_IND_PARTITIONS) pi, DBA_TAB_PARTITIONS pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pi.TABLESPACE_NAME in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_POSITION;
INDEX_OWNER INDEX_NAME TABLESPACE_NAME TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------ -------------------- ----------------- ------------- ------------- ----------------- -----------------
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_1995 EXAMPLE
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_1996 EXAMPLE
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_H1_1997 EXAMPLE
SH SALES_CHANNEL_BIX EXAMPLE SH SALES SALES_H2_1997 EXAMPLE
...
SQL> select i.OWNER as INDEX_OWNER, i.INDEX_NAME, pi.TABLESPACE_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_NAME, pt.TABLESPACE_NAME
from dba_tables t, dba_indexes i, (select distinct INDEX_OWNER, INDEX_NAME, TABLESPACE_NAME from DBA_IND_PARTITIONS) pi, DBA_TAB_PARTITIONS pt
where t.OWNER=i.TABLE_OWNER and t.TABLE_NAME=i.TABLE_NAME and i.OWNER=pi.INDEX_OWNER and i.INDEX_NAME=pi.INDEX_NAME and t.OWNER=pt.TABLE_OWNER and t.TABLE_NAME=pt.TABLE_NAME and pi.TABLESPACE_NAME in ('EXAMPLE') and pt.TABLESPACE_NAME not in ('EXAMPLE') and t.OWNER='SH' and t.TABLE_NAME='SALES'
order by i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, t.TABLE_NAME, pt.PARTITION_POSITION;
no rows selected
SQL> select d.TABLESPACE_NAME, d.TMBYTES, e.UMBYTES, round(e.UMBYTES/d.TMBYTES*100) as PERCENTAGE from
(select TABLESPACE_NAME, round(sum(BYTES)/1024/1024) as TMBYTES
from dba_data_files group by TABLESPACE_NAME) d, (select TABLESPACE_NAME, round(sum(BYTES)/1024/1024) as UMBYTES from dba_extents group by TABLESPACE_NAME) e
where d.TABLESPACE_NAME=e.TABLESPACE_NAME order by d.TABLESPACE_NAME;
TABLESPACE_NAME TMBYTES UMBYTES PERCENTAGE
------------------------------ ---------- ---------- ----------
EXAMPLE 100 68 68
SYSAUX 240 232 97
SYSTEM 480 473 99
UNDOTBS1 30 3 10
USERS 5 3 60
SQL> select OWNER, TABLESPACE_NAME, round(sum(BYTES)/1024/1024) as MBYTES from dba_segments group by OWNER, TABLESPACE_NAME order by OWNER, TABLESPACE_NAME;
OWNER TABLESPACE_NAME MBYTES
--------------- ------------------------------ ----------
CTXSYS SYSAUX 5
DBSNMP SYSAUX 2
DMSYS SYSAUX 0
EXFSYS SYSAUX 4
HR EXAMPLE 2
IX EXAMPLE 2
MDSYS SYSAUX 32
OE EXAMPLE 6
OE USERS 3
OLAPSYS SYSAUX 16
ORDSYS SYSAUX 1
OUTLN SYSTEM 1
PM EXAMPLE 3
SCOTT USERS 0
SH EXAMPLE 56
SYS SYSAUX 61
SYS SYSTEM 457
SYS UNDOTBS1 3
SYSMAN SYSAUX 50
SYSTEM SYSAUX 7
SYSTEM SYSTEM 15
TSMSYS SYSAUX 0
WMSYS SYSAUX 7
XDB SYSAUX 49
24 rows selected.
Tuesday, July 5, 2011
Getting a tablespace’s DDL
If you want to copy a tablespace structure and you don’t have a frog, then you could use the DBMS_METADATA package to get the DLL you need to recreate it:
As you might notice with the UNDO tablespace the original CREATE TABLESPACE statement comes first, and if there is any modification to the tablespaces that sentences come next.
More information:
How to extract DDL for all Tablespaces
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
SQL> set linesize 150
SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) from dba_tablespaces order by TABLESPACE_NAME;
CREATE TABLESPACE "MYTABLESPACE" DATAFILE
'/mydb/mytablespace.dbf' SIZE 5242880
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "PERFSTAT" DATAFILE
'/mydb/perfstat.dbf' SIZE 201326592
AUTOEXTEND ON NEXT 16777216 MAXSIZE 402653184
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "SYSAUX" DATAFILE
'/mydb/sysaux.dbf' SIZE 419430400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE "SYSTEM" DATAFILE
'/mydb/system.dbf' SIZE 419430400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT DICTIONARY DEFAULT NOCOMPRESS
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50);
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
'/mydb/temp.dbf' SIZE 10737418240
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE UNDO TABLESPACE "UNDO" DATAFILE
'/mydb/undo.dbf' SIZE 838860800
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
ALTER DATABASE DATAFILE
'/mydb/undo.dbf' RESIZE 5368709120;
CREATE TABLESPACE "USERS" DATAFILE
'/mydb/users.dbf' SIZE 536870912
AUTOEXTEND ON NEXT 1048576 MAXSIZE 2048M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
7 rows selected.
As you might notice with the UNDO tablespace the original CREATE TABLESPACE statement comes first, and if there is any modification to the tablespaces that sentences come next.
More information:
How to extract DDL for all Tablespaces
Monday, July 4, 2011
Checking maximum number of sessions
If you want to know if your Oracle instance can support some amount of concurrent users, then you have to check the sessions and processes parameters:
The sessions parameter is derived by default from the processes parameter, but if not it might be better to check and set both the processes and sessions parameters. If you have configured shared servers for your instance, you can have a lot of sessions with few processes.
But how many processes you can have depends of the operating system's parameters, therefore it would be good to check also that parameters:
For this example, we checked the maximum number of processes per user in AIX, how many processes where running at that time, and just to be sure the oracle user limits. If you have more than one instance per user, then you have to take into account all the other instance's maximum sessions parameters to be sure your operating system's limits will not affect your instances.
More information:
SESSIONS
PROCESSES
HPUX: Kernel Tunable parameters
Getting AIX basic system info
SQL> set linesize 150
SQL> select count(*) from v$session;
COUNT(*)
----------
87
SQL> column name format a30
SQL> column value format a30
SQL> select name, value from v$parameter where NAME in ('sessions','processes');
NAME VALUE
------------------------------ ------------------------------
processes 600
sessions 665
The sessions parameter is derived by default from the processes parameter, but if not it might be better to check and set both the processes and sessions parameters. If you have configured shared servers for your instance, you can have a lot of sessions with few processes.
But how many processes you can have depends of the operating system's parameters, therefore it would be good to check also that parameters:
myserver> lsattr -El sys0 -a maxuproc
maxuproc 2048 Maximum number of PROCESSES allowed per user True
myserver> id oracle
uid=214(oracle) gid=203(dba) groups=1(staff)
myserver> ps axl|grep " 214"|wc -l
409
myserver> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 5097151
nofiles(descriptors) unlimited
For this example, we checked the maximum number of processes per user in AIX, how many processes where running at that time, and just to be sure the oracle user limits. If you have more than one instance per user, then you have to take into account all the other instance's maximum sessions parameters to be sure your operating system's limits will not affect your instances.
More information:
SESSIONS
PROCESSES
HPUX: Kernel Tunable parameters
Getting AIX basic system info
Subscribe to:
Posts (Atom)