Oracle: To Display list of table spaces

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE

FROM sys.dba_free_space fs, sys.dba_data_files dd

WHERE dd.tablespace_name = fs.tablespace_name

AND dd.file_id = fs.file_id

GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024

ORDER BY dd.tablespace_name, dd.file_name;

Oracle SQL Thread: Convert String to Datetime

Found this link useful

 

https://forums.oracle.com/forums/thread.jspa?threadID=598694

 

select to_date('2007-01-01 12:00:01 AM','yyyy-mm-dd hh:mi:ss pm') as dt from dual;

 

ORA-22858: invalid alteration of datatype, ORA-22858: invalid alteration of datatype

When we try to modify a datatype from varchar2(400) to Clob in a Oracle table, we’ll get the following error:

 

The following steps is the work-around to solve this issue:

 

ALTER TABLE SCHEMANAME.TABLENAME ADD NEWCOLUMN CLOB;

 

UPDATE SCHEMANAME.TABLENAME SET NEWCOLUMN = OLDCOLUMN;

 

ALTER TABLE SCHEMANAME.TABLENAME drop COLUMN OLDCOLUMN;

 

ALTER TABLE SCHEMANAME.TABLENAME rename COLUMN NEWCOLUMN TO OLDCOLUMN;

 

 

To Search for a text in All the files in a folder in Windows 7

Hello,

 

In windows 7 - I want to search for all files containing "A word or phrase in the file". The standard XP search box, But I was not able to get the result until I did the following steps.

 

Hope it is helpful for people who is looking for the same feature.

 

http://answers.microsoft.com/en-us/windows/forum/windows_7-files/in-windows-7-i-want-to-search-for-all-files/aadfe1f1-4a33-406b-8e72-bb920efa4f30

 

---

 

ORA-01461: can bind a LONG value only for insert into a LONG column

Salam,

         

To this issue showed me stars!

 

We are using Devart dotconnect to connect to oracle database.

 

In the Dataset, in the Table adapter the max length is set to -1 by default. And in the Auto generated Abstract class the string datatype length is set to 1024 by default. Now to fix this if we change the max length column alone will not be sufficient. We need the rebuild the method again and then move the DataSet to production to make the changes come into effect!

 

Best Regards

 

List tables and their corresponding tablespace name and data file [Oracle]

SELECT T.TABLE_NAME,T.TABLESPACE_NAME,DF.FILE_NAME FROM DBA_TABLES T, DBA_DATA_FILES DF

 

SELECT T.TABLE_NAME,T.TABLESPACE_NAME,DF.FILE_NAME FROM DBA_TABLES T, DBA_DATA_FILES DF WHERE T.TABLESPACE_NAME = DF.TABLESPACE_NAME AND T.OWNER LIKE 'USER';

 

Grants for a User in Oracle Database

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv  
  FROM table_privileges 
 WHERE grantee = <theUser> 
 ORDER BY owner, table_name;