Tales From A Lazy Fat DBA

$ prashantdixit/dbs90@ace as sysdba

  • Likes

    • 110,996
  • Archives

  • Categories

  • Cause I Support!!

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Oracle Radio

  • Magic Of Oracle

  • Disclaimer!

    FatDBA or Oracle 'Ant' is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Misc

Finding SCN by date:

SQL> select timestamp_to_scn(to_date(’09/05/2013 12:20:00′,’mm/dd/yyyy hh24:mi:ss’)) from dual;
TIMESTAMP_TO_SCN(TO_DATE(’09/05/201312:20:00′,’MM/DD/YYYYHH24:MI:SS’))
————————————————————————————————————————————
2385658

SQL> select current_scn from v$database;

CURRENT_SCN
——————————–
2388097

SQL> select timestamp_to_scn(to_date(’09/01/2013 12:20:00′,’mm/dd/yyyy hh24:mi:ss’)) from dual;
TIMESTAMP_TO_SCN(TO_DATE(’09/01/201312:20:00′,’MM/DD/YYYYHH24:MI:SS’))
———————————————————————-
2230481

 

 

 

Use of parameters: Resizing, Autoextend, Maxsize, Next  …. Managing Tablspaces/Datafiles.

SQL> column FILE_NAME format a70

SQL> set linesize 300
SQL> set pagesize 400

SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY from dba_data_files;

FILE_NAME                                                              TABLESPACE_NAME                     BYTES AUT   MAXBYTES INCREMENT_BY
———————————————————————- —————————— ———- — ———- ————
/u01/app/oracle/oradata/tcs/system01.dbf                               SYSTEM                          817889280 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/sysaux01.dbf                               SYSAUX                          681574400 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/undotbs01.dbf                              UNDOTBS1                        597688320 YES 3.4360E+10            1
/u01/app/oracle/oradata/tcs/users01.dbf                                USERS                           104857600 YES 3.4360E+10          160
/u01/app/oracle/oradata/tcs/test.dbf                                   TEST                             31457280 NO           0            0
/u01/lord.dbf                                                          SHIVA                            26214400 YES   26214400            1
/u01/addd.dbf                                                          USERS                            41943040 NO           0            0

7 rows selected.

SQL> alter database datafile ‘/u01/app/oracle/oradata/tcs/test.dbf’ autoextend on next 512k maxsize 40M;

Database altered.

SQL>  select FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY from dba_data_files;

FILE_NAME                                                              TABLESPACE_NAME                     BYTES AUT   MAXBYTES INCREMENT_BY
———————————————————————- —————————— ———- — ———- ————
/u01/app/oracle/oradata/tcs/system01.dbf                               SYSTEM                          817889280 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/sysaux01.dbf                               SYSAUX                          681574400 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/undotbs01.dbf                              UNDOTBS1                        597688320 YES 3.4360E+10            1
/u01/app/oracle/oradata/tcs/users01.dbf                                USERS                           104857600 YES 3.4360E+10          160
/u01/app/oracle/oradata/tcs/test.dbf                                   TEST                             31457280 YES   41943040           64
/u01/lord.dbf                                                          SHIVA                            26214400 YES   26214400            1
/u01/addd.dbf                                                          USERS                            41943040 NO           0            0

7 rows selected.

SQL> alter database datafile ‘/u01/app/oracle/oradata/tcs/test.dbf’ resize 55M;

Database altered.

SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY from dba_data_files;

FILE_NAME                                                              TABLESPACE_NAME                     BYTES AUT   MAXBYTES INCREMENT_BY
———————————————————————- —————————— ———- — ———- ————
/u01/app/oracle/oradata/tcs/system01.dbf                               SYSTEM                          817889280 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/sysaux01.dbf                               SYSAUX                          681574400 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/undotbs01.dbf                              UNDOTBS1                        597688320 YES 3.4360E+10            1
/u01/app/oracle/oradata/tcs/users01.dbf                                USERS                           104857600 YES 3.4360E+10          160
/u01/app/oracle/oradata/tcs/test.dbf                                   TEST                             57671680 YES   41943040           64
/u01/lord.dbf                                                          SHIVA                            26214400 YES   26214400            1
/u01/addd.dbf                                                          USERS                            41943040 NO           0            0

7 rows selected.

SQL> alter database datafile ‘/u01/app/oracle/oradata/tcs/test.dbf’ maxsize 59M;
alter database datafile ‘/u01/app/oracle/oradata/tcs/test.dbf’ maxsize 59M
*
ERROR at line 1:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

SQL> alter database datafile ‘/u01/app/oracle/oradata/tcs/test.dbf’ autoextend on next 512k maxsize  59M;

Database altered.

SQL> select FILE_NAME, TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY from dba_data_files;

FILE_NAME                                                              TABLESPACE_NAME                     BYTES AUT   MAXBYTES INCREMENT_BY
———————————————————————- —————————— ———- — ———- ————
/u01/app/oracle/oradata/tcs/system01.dbf                               SYSTEM                          817889280 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/sysaux01.dbf                               SYSAUX                          681574400 YES 3.4360E+10         1280
/u01/app/oracle/oradata/tcs/undotbs01.dbf                              UNDOTBS1                        597688320 YES 3.4360E+10            1
/u01/app/oracle/oradata/tcs/users01.dbf                                USERS                           104857600 YES 3.4360E+10          160
/u01/app/oracle/oradata/tcs/test.dbf                                   TEST                             57671680 YES   61865984           64
/u01/lord.dbf                                                          SHIVA                            26214400 YES   26214400            1
/u01/addd.dbf                                                          USERS                            41943040 NO           0            0

7 rows selected.

SQL>

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

Check Database Size:

SQL> select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Size in GB” from dual;

Size in GB
———-
2.55041504

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: