Find out the Schema/User Size

Find out the Schema/User Size

If you are freaks who work around with Oracle as backend, you would face the sitution how to find the schema or user size.

This script finds out the Schema/User Size

 SELECT NVL(tablespace_name, 'UNKNOWN') TABLESPACE,
   SUM(BYTES)/1024/1024 AS "Total Size(MB)"
   FROM DBA_SEGMENTS
  WHERE owner = UPPER('&SCHEMA_NAME')
GROUP BY owner,
   ROLLUP (tablespace_name)

TABLESPACE	Total Size(MB)
USERS	2.875
EXAMPLE	6.25
UNKNOWN	9.125

This script finds out the tablespace's Size

 SELECT NVL(b.tablespace_name, NVL(a.tablespace_name,'UNKNOWN')) "Tablespace",
   kbytes_alloc "Allocated MB"                                               ,
   kbytes_alloc-NVL(kbytes_free,0) "Used MB"                                 ,
   NVL(kbytes_free,0) "Free MB"                                              ,  
   data_files "No. Data Files"
   FROM
   (
       SELECT SUM(bytes)/1024/1024 Kbytes_free,
         MAX(bytes)     /1024/1024 largest    ,
         tablespace_name
         FROM sys.dba_free_space
     GROUP BY tablespace_name
   ) a,
   (
       SELECT SUM(bytes)/1024/1024 Kbytes_alloc,
         tablespace_name                       ,
         COUNT(*) data_files
         FROM sys.dba_data_files
     GROUP BY tablespace_name
   ) b
  WHERE a.tablespace_name (+) = b.tablespace_name
ORDER BY 1

Tablespace	Allocated MB	Used MB	Free MB	No. Data Files
EXAMPLE	100	77.375	22.625	1
SYSAUX	370	350.1875	19.8125	1
SYSTEM	500	492.25	7.75	1
UNDOTBS1	100	11.4375	88.5625	1
USERS	6.25	5.6875	0.5625	1

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode

Find usage of tablespace(s) in Oracle

Find free and used temporary tablespace(s) in Oracle

If you are uncertain who work around with Oracle as backend, you would face the sitution where the oracle temporary space would be worn out.
The usage temporary tablespace can’t be found out exactly using DBA_FREE_SPACE. To find out the accurate value of temporary tablespace we may need to use V$TEMP_SPACE_HEADER data dictonary.

SELECT   tablespace_name,
         SUM (bytes_used) / (1024 * 1024) + SUM (bytes_free) / (1024 * 1024) "Size(MB)",
         SUM (bytes_used) / (1024 * 1024) "Used(MB)",
         SUM (bytes_free) / (1024 * 1024) "Free(MB)"
    FROM v$temp_space_header
GROUP BY tablespace_name; 

Find free and used tablespace(s) in Oracle

If you are looking out to find out the usage of other tablespace we need to use the following script.

SELECT   ts.tablespace_name "Tablespace Name", "File Count", 
   TRUNC ("SIZE(MB)", 2) "Size(MB)",
         TRUNC (fr."FREE(MB)", 2) "Free(MB)",
         TRUNC ("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
         df."MAX_EXT" "Max Ext(MB)",
         ROUND((fr."FREE(MB)" / df."SIZE(MB)") * 100, 3) "% Free",
         RPAD ('#', TRUNC (CEIL ((fr."FREE(MB)" / df."SIZE(MB)") * 100) / 10),'#') "Graph" 
FROM     (SELECT   tablespace_name, SUM (BYTES) / (1024 * 1024) "FREE(MB)"
              FROM dba_free_space GROUP BY tablespace_name) fr,
         (SELECT   tablespace_name, SUM (BYTES) / (1024 * 1024) "SIZE(MB)",
                   COUNT (*) "File Count",
                   SUM (maxbytes) / (1024 * 1024) "MAX_EXT"
              FROM dba_data_files GROUP BY tablespace_name) df,
         (SELECT tablespace_name FROM dba_tablespaces) ts
   WHERE fr.tablespace_name = df.tablespace_name(+)
         AND fr.tablespace_name = ts.tablespace_name(+)
ORDER BY "Tablespace Name";

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode

How to wrap text inside cells of a JTable

Introduction

If you wrap the text in the JTable cell, which being wrapped into multiple line as well as row height also automatically to make the complete text area visible. This is class implemented a JTextArea renderer.

TextAreaRenderer.java

/** 
 * @(#)TextAreaRenderer.java 
 */ 
 
import javax.swing.JTable; 
import javax.swing.JTextArea; 
import javax.swing.table.DefaultTableCellRenderer; 
import javax.swing.table.TableCellRenderer; 
import javax.swing.table.TableColumn; 
import javax.swing.table.TableColumnModel; 
import java.awt.Component; 
import java.util.Enumeration; 
import java.util.HashMap; 
import java.util.Map; 
 
/** 
 * The standard class for rendering (displaying) individual cells in a JTable. 
 * This class inherits from JTextArea, a standard component class. 
 * However JTextArea is a multi-line area that displays plain text. 
 * 
 * This class implements TableCellRenderer , i.e. interface. 
 * This interface defines the method required by any object that 
 * would like to be a renderer for cells in a JTable. 
 * 
 * @author Manivel 
 * @see JTable 
 * @see JTextArea 
 */ 
 
public class TextAreaRenderer extends JTextArea implements TableCellRenderer { 
    private final DefaultTableCellRenderer renderer = new DefaultTableCellRenderer(); 
 
    // Column heights are placed in this Map 
    private final Map<JTable, Map<Object, Map<Object, Integer>>> tablecellSizes = new HashMap<JTable, Map<Object, Map<Object, Integer>>>(); 
 
    /** 
     * Creates a text area renderer. 
     */ 
    public TextAreaRenderer() { 
        setLineWrap(true); 
        setWrapStyleWord(true); 
    } 
 
    /** 
     * Returns the component used for drawing the cell.  This method is 
     * used to configure the renderer appropriately before drawing. 
     * 
     * @param table      - JTable object 
     * @param value      - the value of the cell to be rendered. 
     * @param isSelected - isSelected   true if the cell is to be rendered with the selection highlighted; 
     *                   otherwise false. 
     * @param hasFocus   - if true, render cell appropriately. 
     * @param row        - The row index of the cell being drawn. 
     * @param column     - The column index of the cell being drawn. 
     * @return - Returns the component used for drawing the cell. 
     */ 
    public Component getTableCellRendererComponent(JTable table, Object value, boolean isSelected, 
                                                   boolean hasFocus, int row, int column) { 
        // set the Font, Color, etc. 
        renderer.getTableCellRendererComponent(table, value, 
                isSelected, hasFocus, row, column); 
        setForeground(renderer.getForeground()); 
        setBackground(renderer.getBackground()); 
        setBorder(renderer.getBorder()); 
        setFont(renderer.getFont()); 
        setText(renderer.getText()); 
 
        TableColumnModel columnModel = table.getColumnModel(); 
        setSize(columnModel.getColumn(column).getWidth(), 0); 
        int height_wanted = (int) getPreferredSize().getHeight(); 
        addSize(table, row, column, height_wanted); 
        height_wanted = findTotalMaximumRowSize(table, row); 
        if (height_wanted != table.getRowHeight(row)) { 
            table.setRowHeight(row, height_wanted); 
        } 
        return this; 
    } 
 
    /** 
     * @param table  - JTable object 
     * @param row    - The row index of the cell being drawn. 
     * @param column - The column index of the cell being drawn. 
     * @param height - Row cell height as int value 
     *               This method will add size to cell based on row and column number 
     */ 
    private void addSize(JTable table, int row, int column, int height) { 
        Map<Object, Map<Object, Integer>> rowsMap = tablecellSizes.get(table); 
        if (rowsMap == null) { 
            tablecellSizes.put(table, rowsMap = new HashMap<Object, Map<Object, Integer>>()); 
        } 
        Map<Object, Integer> rowheightsMap = rowsMap.get(row); 
        if (rowheightsMap == null) { 
            rowsMap.put(row, rowheightsMap = new HashMap<Object, Integer>()); 
        } 
        rowheightsMap.put(column, height); 
    } 
 
    /** 
     * Look through all columns and get the renderer.  If it is 
     * also a TextAreaRenderer, we look at the maximum height in 
     * its hash table for this row. 
     * 
     * @param table -JTable object 
     * @param row   - The row index of the cell being drawn. 
     * @return row maximum height as integer value 
     */ 
    private int findTotalMaximumRowSize(JTable table, int row) { 
        int maximum_height = 0; 
        Enumeration<TableColumn> columns = table.getColumnModel().getColumns(); 
        while (columns.hasMoreElements()) { 
            TableColumn tc = columns.nextElement(); 
            TableCellRenderer cellRenderer = tc.getCellRenderer(); 
            if (cellRenderer instanceof TextAreaRenderer) { 
                TextAreaRenderer tar = (TextAreaRenderer) cellRenderer; 
                maximum_height = Math.max(maximum_height, 
                        tar.findMaximumRowSize(table, row)); 
            } 
        } 
        return maximum_height; 
    } 
 
    /** 
     * This will find the maximum row size 
     * 
     * @param table - JTable object 
     * @param row   - The row index of the cell being drawn. 
     * @return row maximum height as integer value 
     */ 
    private int findMaximumRowSize(JTable table, int row) { 
        Map<Object, Map<Object, Integer>> rows = tablecellSizes.get(table); 
        if (rows == null) return 0; 
        Map<Object, Integer> rowheights = rows.get(row); 
        if (rowheights == null) return 0; 
        int maximum_height = 0; 
        for (Map.Entry<Object, Integer> entry : rowheights.entrySet()) { 
            int cellHeight = entry.getValue(); 
            maximum_height = Math.max(maximum_height, cellHeight); 
        } 
        return maximum_height; 
    } 
}

I have tested with jdk1.4 to jdk1.5 in windows xp. Here is the tested code.

TextAreaRendererInTable.java

/** 
 * @(#)TextAreaRendererInTable.java 
 */ 
 
import javax.swing.*; 
import javax.swing.table.*; 
 
/** 
 * The standard class for testing the TextAreaRenderer class. 
 * This class inherits from JFrame, a standard component class. 
 * 
 * @author Manivel 
 * @see JFrame 
 */ 
public class TextAreaRendererInTable extends JFrame { 
 
    /** 
     * Creates a TextAreaRendererInTable object. 
     */ 
    public TextAreaRendererInTable() { 
    } 
 
    /** 
     * This method build the table. 
     */ 
    private void showTable() { 
        JTable table = new JTable(5, 3); 
 
        TableColumnModel cmodel = table.getColumnModel(); 
        TextAreaRenderer textAreaRenderer = new TextAreaRenderer(); 
 
        cmodel.getColumn(0).setCellRenderer(new DefaultTableCellRenderer()); 
        cmodel.getColumn(1).setCellRenderer(textAreaRenderer); 
        cmodel.getColumn(2).setCellRenderer(textAreaRenderer); 
 
        addRows(table); 
 
        getContentPane().add(new JScrollPane(table)); 
        setSize(500, 400); 
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); 
        setLocationRelativeTo(null); 
        setTitle("Text Area Renderer In Table"); 
        setVisible(true); 
 
    } 
 
    /** 
     * This method will add rows into table. 
     * 
     * @param table - JTable object 
     */ 
    private void addRows(JTable table) { 
        String props = "Joy is not in things but in us"; 
        for (int i = 0; i < 5; i++) { 
            table.setValueAt(props + "-" + props, i, 0); 
            table.setValueAt(props, i, 1); 
            table.setValueAt(props + "-" + props + "-" + props, i, 2); 
        } 
    } 
 
    /** 
     * Main method of TextAreaRendererInTable class 
     * 
     * @param args - Defualt main method string args 
     */ 
    public static void main(String[] args) { 
        new TextAreaRendererInTable().showTable(); 
    } 
} 

 

That's it!!

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode

Can you create folder in windows

Cannot create folder with following names

CON    - Keyboard and display
PRN   - System list device, usually a parallel port
AUX   - Auxiliary device, usually a serial port
CLOCK$  - System real-time clock
NUL   - Bit-bucket device
A: - Z:   - Drive letters
COM1  - First serial communications port
COM2  - Second serial communications port
COM3  - Third serial communications port
COM4  - Fourth serial communications port
LPT1  - First parallel printer port
LPT2  - Second parallel printer port
LPT3  - Third parallel printer port

But, you can create folder name as above, there is some tricks

Go to command prompt 
d:\>md \\.\d:\CON
d:\>md \\.\d:\PRN
----------
----------

That is it! Go to windows Explorer & verify it

Remove those folder’s like below:

Go to command prompt 
d:\>rd \\.\d:\CON
d:\>rd \\.\d:\PRN

There is nothing great in this...
There must be some function named as CON in folder creation library of windows. When we try to execute the command named as "CreateFolderName=CON" it get clashed with the config function. So it changes it to by default new folder name, i.e. New Folder.
DOS would recognize "NULL", "PRM", "CON", etc are not special files in windows to maintain the FAT table but are used for typical batch entry. That's not a bug, that's a feature
All this stuff has been documented going way back.

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) June 2008, manivelcode

Rename a user in Oracle

Can one rename a database user (schema)?

No, this is listed as Enhancement Request 158508. Workaround:

    Step1. Do a user-level export of user A
    Step2. create new user B
    Step3. import system/manager fromuser=A touser=B
    Step4. drop user A 

Yes, we can rename database user, there is some trick

You can rename a user but it is not supported by Oracle. To properly rename the user, follow this guide: 1. login sqlplus using sys user as sysdba

D:\>SET ORACLE_SID=DEMO
D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jun 13 16:59:05 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL>conn sys/oracle as sysdba
Connected.

2. select the user id and which name from user$

SQL>select user#, name from user$;

     USER# NAME
---------- ------------------------------
         0 SYS
         1 PUBLIC
         2 CONNECT
         3 RESOURCE
         4 DBA
         5 SYSTEM
        ...........
        ...........
        64 DEMO
        ...........
        ...........

70 rows selected.

3. Change your old user name into new user name. Be careful when u update user$

---update user$ set name='<newname>'; where user#=<old user#>;

SQL>update user$ set name='SHOW' where user#=64;

1 row updated.

SQL>commit;

Commit complete.

4. Shutdown the database and start up, or restart database service.

SQL>shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL>

5. alter the renamed user password using system user or sys or dba user user.

SQL>alter user SHOW identified by SHOW;

User altered.

6. Now try to connect database with renamed user and its password

SQL>conn SHOW/SHOW
Connected.
SQL>exit

That's it!!!

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) June 2008, manivelcode

Move or Rename the Oracle datafile

Oracle database does not provide an easy user interface to rename a datafile of tablespace, nor database administrator can easily move or relocate the datafile to another location or directory that different from original location on creation of database. The rename or move place task has to be performed via Oracle SQLPlus command line interface. However, if the operation is performed when the tablespace which owns the datefile is online, error will occur. The error message may include the following:

ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file <string> - file is in use or recovery
ORA-01110: data file <string>: ‘datafile.dbf’

To properly move the datafile around or rename the datafile, follow this guide: 1. Login to SQLPlus. 2. Connect as SYS DBA with CONNECT / AS SYSDBA command. 3. Shutdown the database instance with SHUTDOWN command. 4. Rename or/and move the datafiles at operating system level. 5. Start Oracle database in mount state with STARTUP MOUNT command. 6. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:

ALTER DATABASE RENAME FILE ‘<fully qualified path to original data file name>’ TO 
     ‘<new or original fully qualified path to new or original data file name>’;

7. Open Oracle database instance completely with ALTER DATABASE OPEN command. If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline. 1. Login to SQLPlus. 2. Connect as SYS DBA with CONNECT / AS SYSDBA command. 3. Make offline the affected tablespace with ALTER TABLESPACE <tablespace name> OFFLINE; command. 4. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:

ALTER TABLESPACE <tablespace name> RENAME DATAFILE ‘<fully qualified path to original data file name>’
      TO ‘<new or original fully qualified path to new or original data file name>’;

5. Bring the tablespace online again with ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; command.

Copyright - GNU General Public License

(C) June 2008, manivelcode

Drop a Target Database using RMAN

Starting with Oracle10g Release 1, Oracle provides a method to completely drop a database with one simple statement. This method makes use of the DROP DATABASE command in RMAN.

NOTE: Although this should be obvious from the title of the article, the following will completely drop the target database including all datafiles, controlfiles, redo log files, SPFILE, and if you want all backups and archived redo log files associated with the database. Consider yourself warned.

The DROP DATABASE Command

NOTE: Before the target database can be dropped, it must be mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.

Before dropping the database, let's take a look at the complete syntax for the RMAN DROP DATABASE command:

DROP DATABASE;

DROP DATABASE NOPROMPT;

DROP DATABASE INCLUDING BACKUPS;

DROP DATABASE INCLUDING BACKUPS NOPROMPT;

When "NOPROMPT" is specified, RMAN will not prompt for confirmation before deleting the database. The default for RMAN is to prompt for confirmation. Restrictions and Usage Notes

• This command can only be run from RMAN.
• You must be connected to the target database from RMAN while the database is mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.
• When using the "DROP DATABASE" command, RMAN drops the target database including the following files at the operating system level:

• Datafiles
• Online Redo Log Files
• Controlfiles
• SPFILE (if it exists)

· When including the "INCLUDING BACKUPS" clause, RMAN will delete the files listed above as well as the following files at the operating system level:

• Archive Redo Logs
• Backup pieces generated by RMAN for the target database

· When using the "DROP DATABASE" command with RMAN connected to a recovery catalog, RMAN will unregister the target database.

• The "DROP DATABASE" command does not delete the following files:
• init.ora (text version of the Oracle initialization file)
• password file

· If the operating system is Windows NT/2000, then it does not delete the Windows NT service for the target database instance.

DROP DATABASE EXAMPLE In this article, I will be dropping a database named orcl. The orcl database is in archive log mode and has several backups included in the Flash Recovery Area. I want to drop the target database along with all backup files.

$ sqlplus "/ as sysdba"



SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.



SQL> startup mount exclusive

ORACLE instance started.



Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size             100664912 bytes

Database Buffers          180355072 bytes

Redo Buffers                2973696 bytes

Database mounted.



SQL> alter system enable restricted session;



System altered.



SQL> exit



$ rman target /



Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 7 17:33:25 2006



Copyright (c) 1982, 2005, Oracle.  All rights reserved.



connected to target database: ORCL (DBID=1123903688, not open)



RMAN> drop database including backups;



database name is "ORCL" and DBID is 1123903688



Do you really want to drop all backups and the database (enter YES or NO)? YES



using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK



List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

------- ------- --- --- ----------- ----------- ----------

51      31      1   1   AVAILABLE   DISK        /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T164635_2fh9of4s_.bkp

52      32      1   1   AVAILABLE   DISK        /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_nnndf_TAG20060807T164638_2fh9ogvf_.bkp

53      33      1   1   AVAILABLE   DISK        /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_ncsnf_TAG20060807T164638_2fh9vzo0_.bkp

54      34      1   1   AVAILABLE   DISK        /u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T165009_2fh9w2lm_.bkp

deleted backup piece

backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T164635_2fh9of4s_.bkp recid=51 stamp=597861997

deleted backup piece

backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_nnndf_TAG20060807T164638_2fh9ogvf_.bkp recid=52 stamp=597861998

deleted backup piece

backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_ncsnf_TAG20060807T164638_2fh9vzo0_.bkp recid=53 stamp=597862207

deleted backup piece

backup piece handle=/u02/flash_recovery_area/ORCL/backupset/2006_08_07/o1_mf_annnn_TAG20060807T165009_2fh9w2lm_.bkp recid=54 stamp=597862210

Deleted 4 objects


released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

specification does not match any archive log in the recovery catalog

database name is "ORCL" and DBID is 1123903688

database dropped

Copyright - GNU General Public License

(C) June 2008, manivelcode