ORACLE FORMATTING MANY PROBLEM SOLVED :)

-
FORMATTING OPTIONS AS FOLLOWS:

Line and Page display format.

SQL> SET LINES 150

SQL> SET PAGESIZE 500

TO FORMAT COLUMNS

SQL> column username format a18

====================================

SQL> SELECT MAX(LENGTH(USERNAME)), MAX(LENGTH(ACCOUNT_STATUS)),MAX(LENGTH(DEFAULT_TABLESPACE)),MAX(LENGTH( TEMPORARY_TA
LESPACE)) FROM DBA_USERS;

MAX(LENGTH(USERNAME)) MAX(LENGTH(ACCOUNT_STATUS)) MAX(LENGTH(DEFAULT_TABLESPACE))
--------------------- --------------------------- -------------------------------
MAX(LENGTH(TEMPORARY_TABLESPACE))
---------------------------------
                   18                          16                               6
                                4


SQL> COLUMN USERNAME FORMAT A18
SQL> COLUMN ACCOUNT_STATUS FORMAT A16
SQL> COLUMN DEFAULT_TABLESPACE FORMAT A6
SQL> COLUMN TEMPORARY_TABLESPACE FORMAT A4
SQL> SELECT USERNAME, ACCOUNT_STATUS,DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS;

USERNAME           ACCOUNT_STATUS   DEFAUL TEMP
------------------ ---------------- ------ ----
MGMT_VIEW          OPEN             SYSTEM TEMP
SYS                OPEN             SYSTEM TEMP
SYSTEM             OPEN             SYSTEM TEMP
DBSNMP             OPEN             SYSAUX TEMP
SYSMAN             OPEN             SYSAUX TEMP
NEWUSER            OPEN             USERS  TEMP
HELLO              OPEN             USERS  TEMP
SCOTT              OPEN             USERS  TEMP
XYZ                OPEN             USERS  TEMP
OUTLN              EXPIRED & LOCKED SYSTEM TEMP
MDSYS              EXPIRED & LOCKED SYSAUX TEMP
ORDSYS             EXPIRED & LOCKED SYSAUX TEMP
CTXSYS             EXPIRED & LOCKED SYSAUX TEMP
ANONYMOUS          EXPIRED & LOCKED SYSAUX TEMP
EXFSYS             EXPIRED & LOCKED SYSAUX TEMP
DMSYS              EXPIRED & LOCKED SYSAUX TEMP
WMSYS              EXPIRED & LOCKED SYSAUX TEMP
XDB                EXPIRED & LOCKED SYSAUX TEMP
ORDPLUGINS         EXPIRED & LOCKED SYSAUX TEMP
SI_INFORMTN_SCHEMA EXPIRED & LOCKED SYSAUX TEMP
OLAPSYS            EXPIRED & LOCKED SYSAUX TEMP
MDDATA             EXPIRED & LOCKED USERS  TEMP
DIP                EXPIRED & LOCKED USERS  TEMP
TSMSYS             EXPIRED & LOCKED USERS  TEMP

24 rows selected.

Comments

Post a Comment

Popular posts from this blog

Query for Tablespace usage with Autoextend

-
                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...
Read more

How to configure multiple Oracle listeners

-
How to configure multiple Oracle listeners It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment. 1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.    2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2 Modify listener.ora and tnsnames.ora as following: Here is a sample of   listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...
Read more

sheel script - automatic tablespace addition in oracle 11g

-
      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...
Read more