ORA-01653: unable to extend table in tablespace string tips

ORA-12096: error in materialized view log on ****.****
ORA-01653: unable to extend table  ****.**** by 1024 in tablespace ****

ORA-01653: unable to extend table in tablespace string tips


The Oracle docs note this on the ora-01653 error:

ORA-01653: unable to extend table string.string by string in tablespace string
    Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
    Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
Oracle MOSC Documents offer great resources on resolving ORA-01653,  namely the nature of the problem as it relates to space availability:

    Explanation:
    ------------
    This error does not necessarily indicate whether or not you have enough space
    in the tablespace, it merely indicates that Oracle could not find a large enough area of free
    contiguous space in which to fit the next extent.

SOURCE - http://www.dba-oracle.com/t_ora_01653_unable_to_extend_table_string_string_by_string_in_tablespace_string.htm

Number of Session Vs. Processes

Relationship between sessions on a database and the number of processes.

The parameters SESSIONS and PROCESSES determine the size of two arrays in the SGA.

If you try to create more sessions on an instance than specified by the SESSIONS parameter, you will get an ORA-00018: maximum number of sessions exceeded.

If you try to create more processes on an instances than specified by the PROCESSES prameter, you wil get an ORA-00020: maximum number of processes exceeded.

The number of sessions and processes on your instance can been seen using a COUNT(*) against V$PROCESS and V$SESSION. Your job is to determine resonable values, so your users can continue
to work without getting neither ORA-18 nor ORA-20 during normal conditions. (A run away job, that keeps starting new sessions is not a normal condition, and it should be stopped by ORA-18 or ORA-20
whichever happens first).

You can join the two V$-views using the columns V$SESSION.PADDR and V$PROCESS.ADDR.

It is quite normal to see a 1:1 relationship between processes and sessions. However one OCI-program can create multiple sessions belonging to one process. Users of Oracle Portal
will experience this behaviour.

The default value for SESSIONS is 1.1*PROCESSES + 5.

Sqlserver Full Text Index , PDF population empty

Error: Warning: No appropriate filter was found during full-text index population for table

Replicate the problem
create table fts_pdf (id int not null, doctype varchar(4), content varbinary(max));

alter table fts_pdf add constraint pk_id_fts_pdf primary key (id);

INSERT INTO fts_pdf(id, doctype, content)
   SELECT 2 as id, '.pdf' AS doctype,
       * FROM OPENROWSET(BULK N'D:\common\ebooks2\India-Year-Book-2009.pdf', SINGLE_BLOB) AS Document;
GO

select * from fts_pdf;
go

--create catalog
CREATE FULLTEXT CATALOG fts_pdf_content_catalog;
GO

--create index
create fulltext index on fts_pdf
(content type column doctype language 1033
)
key index pk_id_fts_pdf    ON fts_pdf_content_catalog
WITH CHANGE_TRACKING AUTO
go

select * from fts_pdf where contains (content, 'brought');
no result

Problem :- While population generation from any file(doc,pdf,txt) , sqlserver use filters to extract
the contents from the file. Filter can be checked by  the below query :

select * from sys.fulltext_document_types
Some of the filters are provided by the sqlserver and some of the filter are provided by windows.
".pdf" is one of the filter which not available while installation and even with windows.

Solution: =
We need to install ifilter provided by the adobe.

• Download and install the ifilter
• Set the environment variable to path of bin folder of ifilter
• i.e. "path = c:/program files/adobe/****/***/bin/"

• Now Open the Mssqlserver
• connect with database
• execute below commands:--

     EXEC sp_fulltext_service 'load_os_resources', 1
     EXEC sp_fulltext_service 'verify_signature', 0
     GO

• Restart the mssqlserver and fulltext index services

     EXEC sp_fulltext_database 'enable'

MySQL data files .MYD, .MYI, and .frm

A MySQL database creates a number of different data files in the mysql data directory (typically /var/lib/mysql). The following are the differences between the data files created:

File Extension	Purpose
.frm	table definition
.MYD	table data
.MYI	table indices

I believe these files are created for both MyISAM and InnoDB table types because the MySQL documentation references these types with respect to MyISAM and I definitely see them on my InnoDB tables.
I couldn’t find this information directly in the MySQL documentation and google wasn’t much help either so hopefully this post will save someone else some search effort.

In windows these files locate at:

"C:\ProgramData\MySQL\MySQL Server 5.*\data"

Create procedure, Cursor example , execute immediate

Create procedure in mysql,  Cursor example in mysql, execute immediate in mysql

cursor/procedure example

DELIMITER ##

CREATE PROCEDURE cursorexam()

BEGIN

    DECLARE vempno BIGINT; 

    DECLARE vsal BIGINT;

    DECLARE done INT DEFAULT 0;

    DECLARE cur CURSOR FOR SELECT empno ,sal FROM emp;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP

    FETCH cur INTO vempno,vsal;

        IF done THEN

            LEAVE read_loop;

        END IF;

        INSERT INTO empchart (empno, sal, pcat) VALUES (vempno, vsal, 1);

    END LOOP;

    CLOSE cur;

END;##

Execute immediate in mysql

DELIMITER $$

CREATE PROCEDURE Exec_imme_ex()
BEGIN

DECLARE tName TEXT;
DECLARE tSchema TEXT;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
    select table_name,table_Schema from information_schema.tables where table_schema='scott';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
FETCH cur INTO tName,tSchema;
    IF done THEN
        LEAVE read_loop;
    END IF;
    SET @s = CONCAT('insert into tab values (''',tName, ''',''', tSchema, ''')');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END LOOP;
CLOSE cur;
deallocate prepare stmt;
END
$$

DELIMITER ;



continue to post:

Dynamic Cursor in mysql

Unused index MySql

Unused index MySql
Find --


logic is here


SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME,
    s.SEQ_IN_INDEX,
    ( SELECT MAX(SEQ_IN_INDEX)
    FROM INFORMATION_SCHEMA.STATISTICS s2
    WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
    AND s.TABLE_NAME = s2.TABLE_NAME
    AND s.INDEX_NAME = s2.INDEX_NAME
    ) AS `COLS_IN_INDEX`,
    s.CARDINALITY AS `CARD`,
    t.TABLE_ROWS AS `ROWS`,
    ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %`
FROM INFORMATION_SCHEMA.STATISTICS s
    INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA != 'mysql'
    AND t.TABLE_ROWS > 10
    AND s.CARDINALITY IS NOT NULL
    AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY `SEL%`, TABLE_SCHEMA, TABLE_NAME;

Translate Equivalent in Sqlserver

translate function in Sqlserver

source - http://www.dbforums.com/microsoft-sql-server/1216565-oracle-translate-function-equivalent-sql-server.html

CREATE FUNCTION dbo.Thicky_Pants 
 (
 @Input AS VarChar(1000),
 @Find AS VarChar(100),
 @Replace AS VarChar(100)
 )
 RETURNS VarChar(1000)
AS
BEGIN
 
 DECLARE @i AS TinyInt
 
 SELECT @i = 1
 
 WHILE @i <= LEN(@Find) BEGIN
  
  SELECT @Input = REPLACE(@Input, SUBSTRING(@Find, @i, 1), SUBSTRING(@Replace, @i, 1))
  SELECT @i = @i + 1
 
 END
 RETURN @Input
END
GO
 
DECLARE @String AS VarChar(1000)
 
SELECT @String = 'pootle_flump'
SELECT @String = dbo.Thicky_Pants(@String, 'pt', 'xz')
 
PRINT  @String